Re: Given a list of objects, run SQL Queries on it -- MIDRANGE-L

This is what we ship our auditors

*-- category: IT Audits*
*-- description: DSPOBJAUT*
*--*
*-- Search for ROB and replace appropriately.*
*CL:* *CRTLIB* HQ20250814 TEXT('Audit 2025-08-14');
set *current schema* = 'HQ20250814';
*CL:* *CHGCURLIB* HQ20250814;
*CL:* *MD* '/home/ROB/HQ20250814';
*CL:* *CD* '/home/ROB/HQ20250814';

*CL:* *DSPUSRPRF* USRPRF(*ALL) TYPE(*BASIC)
OUTPUT(*OUTFILE) OUTFILE(DSPUSRPRF);
*CL:* *CPYTOIMPF* FROMFILE(DSPUSRPRF)
TOSTMF(DSPUSRPRF.csv)
MBROPT(*REPLACE)
STMFCCSID(*PCASCII)
RCDDLM(*CRLF)
ADDCOLNAM(*SYS);

*-- Use tools to convert spool file into pdf file**CL:* *OVRPRTF*
FILE(QSYSPRT)
TOSTMF('WRKSYSVAL.pdf')
DEVTYPE(*AFPDS)
WSCST(*PDF)
OVRSCOPE(*JOB);
*CL:* *WRKSYSVAL* OUTPUT(*PRINT);
*CL:* *DLTOVR* QSYSPRT LVL(*JOB);

*-- Use tools to convert spool file into pdf file**CL:* *ENDJOB*
JOB(ROB/QPRTJOB) OPTION(*IMMED) SPLFILE(*YES);
*CL:* *ANZDFTPWD* ACTION(*NONE);
*CL:* *CPYSPLF* FILE(QPSECPWD)
TOFILE(*TOSTMF)
JOB(ROB/QPRTJOB)
SPLNBR(*LAST)
TOSTMF('ANZDFTPWD.pdf')
WSCST(*PDF);

*-- Get list of objects users are authorized to:*
*-- https://www.ibm.com/developerworks/community/wikis/home?lang=en#!/wiki/IBM%20i%20Technology%20Updates/page/QSYS2.OBJECT_PRIVILEGES%20View
<https://www.ibm.com/developerworks/community/wikis/home?lang=en#!/wiki/IBM%20i%20Technology%20Updates/page/QSYS2.OBJECT_PRIVILEGES%20View>*
create table objectlist as (
select *
from QSYS2.OBJECT_PRIVILEGES
where system_object_schema in('ERPLXF', 'ERPLXUSRF', 'QSYS', 'QSYS2')
) with data;
*CL:* *CPYTOIMPF* FROMFILE(OBJECTLIST)
TOSTMF(OBJECTLIST.csv)
MBROPT(*REPLACE)
STMFCCSID(*PCASCII)
RCDDLM(*CRLF)
ADDCOLNAM(*SYS);

*-- Get list of libraries users are authorized to:*
*-- https://www.ibm.com/developerworks/community/wikis/home?lang=en#!/wiki/IBM%20i%20Technology%20Updates/page/QSYS2.OBJECT_PRIVILEGES%20View
<https://www.ibm.com/developerworks/community/wikis/home?lang=en#!/wiki/IBM%20i%20Technology%20Updates/page/QSYS2.OBJECT_PRIVILEGES%20View>*
create table schemalist as (
select *
from QSYS2.OBJECT_PRIVILEGES
where system_object_schema in('QSYS')
and system_object_name in('ERPLXF', 'ERPLXUSRF')
) with data;
*CL:* *CPYTOIMPF* FROMFILE(SCHEMALIST)
TOSTMF(SCHEMALIST.csv)
MBROPT(*REPLACE)
STMFCCSID(*PCASCII)
RCDDLM(*CRLF)
ADDCOLNAM(*SYS)

On Tue, Nov 25, 2025 at 10:50 AM Jim Oberholtzer <
midrangel@xxxxxxxxxxxxxxxxx> wrote:

It’s audit season, and auditors being auditors want quite a bit of almost
useless information from a long list of objects, IE: DSPOBJAUT on command
STRDFU. A) it’s already locked out from all users (where it even exists)
except a very small list of *ALLOBJ users, B) I removed it from the
production systems. But the auditors still want to see a null data set.

So, what I would like to build is an SQL based procedure that will run
all the various reports they want. The reports would be separate Excel
spreadsheets for each object.

Read list of objects
Run a series of SQL commands to get data (IE DSPOBJAUT, sql version not
the command)
Create the spreadsheet
Loop back to the list until EOF.

My limited SQL abilities stop me from doing this in SQL. I could do it in
CLLE but the auditors would reject it since they cannot spell IBM i without
being spotted the “I”. The can read SQL.

Suggestions?

--
Jim Oberholtzer
Agile Technology Architects

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.