Hi Art,
Sorry for the delay with the answer, I was out for a couple of days. So here are my findings:
- I built a sample AUDIT5 IQL request (provided below in the JCL sample) that produces data in LOADable format. I used the default request idb2hlq.REQUESTS(AUDCLAS5) to identify what information to capture and request idb2hlq.REQUESTS(ARCIACD) as a archive IQL sample (https://docops.ca.com/display/CAIDB219/APPLICATION_DETAIL+Table)
- After I built my IQL request, I used the LOAD Statement Generator utility (https://docops.ca.com/display/CAIDB219/Utilities) to produce DDL and LOAD statements for that IQL request. Later I had to tweak them a bit. Here is the DDL for my AUDIT_IFCID144 table:
CREATE TABLE AUDIT_IFCID144
(DATE_TIME TIMESTAMP,
SYSTEM_ID CHAR(4),
SUBSYSTEM CHAR(4),
RELEASE CHAR(4),
YEAR CHAR(4),
MONTH CHAR(2),
DAY CHAR(2),
HOUR CHAR(2),
DAY_OF_WEEK CHAR(3),
WEEK_NUM DEC(15, 0),
AUTH_ID VARCHAR(128),
CORR_ID CHAR(12),
PLAN_NAME CHAR(8),
CONNECTION CHAR(8),
ENDUSER_USERID VARCHAR(128),
ENDUSER_TRANSACTION VARCHAR(128),
ENDUSER_WORKSTATION VARCHAR(128),
DATABASE CHAR(8),
PAGESET CHAR(8),
DBID SMALLINT,
OBID SMALLINT,
TABLE_NAME CHAR(128)
);
//AUDIT5J JOB (nnnnnnnn),'SYSVDB2 BATCH R19',REGION=0M
//*-------------------------------------------------------------------
//* RUNS CA SYSVIEW FOR DB2 BATCH REPORT WRITER TO CREATE OUTFILE
//*-------------------------------------------------------------------
//DATA EXEC PGM=NSIGHTRW,REGION=4096K
//STEPLIB DD DISP=SHR,DSN=idb2hlq.CDBALOAD
//DBGPRINT DD SYSOUT=*
//SYSPARMS DD DISP=SHR,DSN=idb2hlq.SOURCE(SYSPARMS)
//DB2DDN DD DISP=SHR,DSN=your.SMF.IFCID144
//OBIDTBL DD DISP=SHR,DSN=idb2hlq.D11A.OBID
//DBGIN DD *
AUDIT5: TRACE AUDIT-READ
EVENT-TIME (OF=DB2)
MVS-SMF-ID
SUBSYS
DB2-RLSE-NUM
EVENT-TIME (OF='YYYY')
EVENT-TIME (OF='MM')
EVENT-TIME (OF='DD')
EVENT-TIME (OF='HH')
EVENT-TIME (OF='DDD')
WEEK-NUM (OF=P15.0)
*
* COMMON FIELDS
AUTH-ID-LEN
AUTH-ID-LONG (OF=A128)
CORR-ID
PLAN-NAME
CONNECTION
ENDUSER-USERID-LEN
ENDUSER-USERID-LONG (OF=A128)
ENDUSER-TRANSACTION-LEN
ENDUSER-TRANSACTION-LONG (OF=A128)
ENDUSER-WORKSTATION-LEN
ENDUSER-WORKSTATION-LONG (OF=A128)
*
* IFCID 144 FIELDS
DB-NAME
PAGESET-NAME
DBID
OBID
OBIDNAME(DBID,OBID) (OF=A128)
*
* DESTINATION
OUTFILE (AUDIT5)
;
//AUDIT5 DD DISP=(NEW,CATLG,DELETE),SPACE=(CYL,(100,10),RLSE),
// DSN=temp.AUDIT5.OUTPUT
//*-------------------------------------------------------------------
//* EXECUTES DB2 LOAD UTILITY TO LOAD OUTFILES
//*-------------------------------------------------------------------
//LOAD EXEC PGM=DSNUTILB,REGION=4096K,
// PARM='D11A,USERID,',COND=(4,LT)
//STEPLIB DD DISP=SHR,DSN=D11A.PRIVATE.SDSNEXIT
// DD DISP=SHR,DSN=DB2.DB2B10.SDSNLOAD
//SYSPRINT DD SYSOUT=*
//UTPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SORTWK01 DD UNIT=SYSDA,SPACE=(16384,(20,20),,,ROUND)
//SORTWK02 DD UNIT=SYSDA,SPACE=(16384,(20,20),,,ROUND)
//SORTWK03 DD UNIT=SYSDA,SPACE=(16384,(20,20),,,ROUND)
//SORTWK04 DD UNIT=SYSDA,SPACE=(16384,(20,20),,,ROUND)
//SYSUT1 DD UNIT=SYSDA,SPACE=(16384,(20,20),,,ROUND)
//SORTOUT DD UNIT=SYSDA,SPACE=(16384,(20,20),,,ROUND)
//AUDIT5 DD DISP=(SHR,DELETE,DELETE),DSN=temp.AUDIT5.OUTPUT
//SYSIN DD *
LOAD DATA INDDN AUDIT5 RESUME YES
LOG YES
INTO TABLE AUDIT_IFCID144
WHEN (1:8)='AUDIT5 '
(
DATE_TIME POSITION(77) TIMESTAMP EXTERNAL,
SYSTEM_ID CHAR(4),
SUBSYSTEM CHAR(4),
RELEASE CHAR(4),
YEAR CHAR(4),
MONTH CHAR(2),
DAY CHAR(2),
HOUR CHAR(2),
DAY_OF_WEEK CHAR(3),
WEEK_NUM DECIMAL PACKED,
AUTH_ID VARCHAR,
CORR_ID POSITION(266) CHAR(12),
PLAN_NAME CHAR(8),
CONNECTION CHAR(8),
ENDUSER_USERID VARCHAR,
ENDUSER_TRANSACTION POSITION(424) VARCHAR,
ENDUSER_WORKSTATION POSITION(554) VARCHAR,
DATABASE POSITION(684) CHAR(8),
PAGESET CHAR(8),
DBID SMALLINT,
OBID SMALLINT,
TABLE_NAME CHAR(128)
)
//
- A few notes to the JCL above:
- I changed the data set names in the sample since these will be different in each installation
- Input IFCIDs are assumed to be in the SMF data set your.SMF.IFCID144
- In the batch report writer step I used the OBID file (pointed by the ddname OBIDTBL) so that OBID/DBID pair is translated to the actual table name. Refer to the OBID Translation Utility documentation in the SYSVIEW for DB2 Utilities section - https://docops.ca.com/display/CAIDB219/Utilities (login with your support.ca.com credentials to access it). If the file is not provided, the table name will be written as dbid.obid. Alternately, when you query the AUDIT_IFCID144 table, you can use SQL JOIN with SYSIBM.SYSTABLES using this pair to get the table name.
- The output temp.AUDIT5.OUTPUT data set is deleted in the second step
- I tested the sample on our DB2 11 subsystem named "D11A"
- You might want to expand the JCL with the COPY step in order to create an image copy before the LOAD. I suggest you have a look at the archiving JCL IDB2ARC delivered with the product (https://docops.ca.com/display/CAIDB219/Archival+Process)
Please try this and let me know if this helps.
Thank you,
Denis Tronin
CA SYSVIEW for DB2 Product Owner
denis.tronin@ca.com