RE: Running OS commands from SQL

[Rob Berendt <rob@xxxxxxxxx>]

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