This article will walk you thru the steps needed to create both a General Report and Detail Report using the Db2 Log Analysis Tool. The General Report shows each modified table row (update, inserted or deleted) using the time frame and filters provided. The Detail Report shows the actual table rows both before and after the the modification. To perform Audit Reporting, go to option 1 on the “Quick Start Menu” as shown and press enter.
The “Quick Start Report Activity” panel is displayed as shown below. Enterr the Db2 SSID you want to report on as well as the time period and reporting options. For this example, I want to report on Db2 SSID PB1A for the time period 5/18/2020 12:18 PM until 5/18/2020 12:20 PM. We enter an M for “Object filters” to enter an Object filter by name. I also specified Y for the “General Report” and the “Detail Report” and then pressed enter.
The “Object Name Filters” panel is displayed and I enter the object names I want to specifically report on. In this example, I want to report on type “T” for table and table name STD01.UNDTB1. Press enter to add the object filter and then PF3 to continue.
The following popup panel is displayed. Enter Y for “Enter/Submit JCL now” and then press enter.
You will be placed into edit for the job JCL Enter SUBMIT on the command line and press enter to submit the job.
Go to SDSF to review the job output. Once in SDSF, enter a ? next to the job submitted so that the output from each DD within each STEP can be reviewed.
Enter a S next to the GENRPT DDNAME and the DTLRPT DDNAME to view the output of the General Report and the Detail Report and press Enter.
Below you will see the information contained in a General Report. The report starts out by displaying the Log Range and Filters used to create the report. The report displays each Unit of Recovery ID (URID) that was reported on for that time period and those that match the filters entered. In my example, table STD01.UNDTB1 had 5 rows entered according to the Action/Count column heading.
********************************* TOP OF DATA **********************************
DB2 LOG ANALYSIS- GENERAL REPORT: PB1A
**************************************
LOG RANGE
---------
START DATE : 2020/05/18
START TIME : 12:18:00
END DATE : 2020/05/18
END TIME : 12:20:00
FILTERS
-------
SHOW UPDATES : Y
SHOW INSERTS : Y
SHOW DELETES : Y
SHOW ROLLBACKS : N
CATALOG DATA : Y
INCLUDE-TABLE...... STD01.UNDTB1
===============================================================================
URID DATE TIME AUTHID PLAN CONNTYPE CONNID
-------------------- ---------- -------- -------- -------- -------- --------
00000000000B65BA1AA9 2020-05-18 12:18:28 PDBOB DSNTEP2 BATCH BATCH
MEMBER ID LUWID= NETID/LUNAME/UNIQUE/COMMIT LRSN CORRID
--------- ----------------------------------- -------------------- ------------
00000 ROCKNET1/PB1ADB2 /D7EFECC215E9/0001 00D7EFECC2180B000000 AUDLAB1B
-------------------------------------------------------------------------------
DBNAME DBID TSNAME PSID TABLE OWNER TABLE NAME OBID ACTION/COUNT
-------- ----- -------- ----- ----------- ------------------ ----- ------------
UNDDB 00341 UNDTS1 00002 STD01 UNDTB1 00005 I/1
|
Below you will see the information contained in the Detail Report. The Detail Report also shows transactions by URID. Here you see the Action was an INSERT and it will also show you the before and after row images of the transaction. In this example, since it was an insert, the before row image was NULLs and the after image is the result of the insert.
********************************* TOP OF DATA **********************************
DB2 LOG ANALYSIS - DETAILS REPORT: PB1A
***************************************
_______________________________________________________________________________
RECORD IDENTIFIER: 3
ACTION DATE TIME TABLE OWNER TABLE NAME
------ ---------- -------- ------------ -------------------------
INSERT 2020-05-18 12.18.28 STD01 UNDTB1
URID DATABASE TABLESPACE DBID PSID OBID AUTHID
-------------------- -------- ---------- ----- ----- ----- --------
00000000000B65BA1AA9 UNDDB UNDTS1 00341 00002 00005 PDBOB
PLAN CONNTYPE LRSN MEMID CORRID CONNID
-------- -------- -------------------- ----- ------------ --------
DSNTEP2 BATCH 00D7EFECC2180B000000 00000 AUDLAB1B BATCH
LUW=NETID/LUNAME/UNIQUE/COMMIT PAGE/RID PARTITION
----------------------------------- ----------- ---------
ROCKNET1/PB1ADB2 /D7EFECC215E9/0001 00000002/05
ROW STATUS COL1 COL2 COL3 COL4
----------- ---------- ---------- ---------- ----------
POST-CHANGE E1 E2 E3 E4
PRE-CHANGE - - - -
|
#db2tools
#Db2Toolsforz/OS