RE: Running OS commands from SQL

Works great! My audit script runs really fast. Then I pull that down to my PC, zip it up (along with the sql script from ACS) and send that to the audit. Reports done so darn quick.
I change the date from the "insert from examples" below:

-- category: IT Audits
-- description: DSPOBJAUT
--
-- Search for ROB and replace appropriately.

CL: CRTLIB M120180927 TEXT('Audit 2018-09-27');
set current schema = 'M120180927';
CL: CHGCURLIB M120180927;
CL: MD '/home/ROB/M120180927';
CL: CD '/home/ROB/M120180927';

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
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
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);


Rob Berendt