I have recently faced an issue where a process was updating one of the columns in the composite primary key of one of our DB2 tables which leaded to bad data in subsequent process. And the DB2 table did not have the last mod user for us to determine the issue process directly (There is also a chance that the issue program is not updating the mod user), so we did not have a way of finding the issue program that was updating the primary key column. But by researching we were able to identify the issue data (Primary Key values) that got changed and the time that this happened.
Using these details, below DB2 DBA job that does “DB2 LOG ANALYZER LOG REPORTING”, helped us in determining the process. This job is a DB2 LOG Analyzer (Batch version of Platinum tool option), and to this we need to supply the below details.
- New Dataset Name (for DD LADREPT) to store the output of the job
- START and END Timestamp between which you want to get the DB2 logs
- Table Name
- Key Columns Names
- Statement Type that you are looking for in the DB2 LOG (In our case, UPDATE)
Output of the job will have before/after state for all the rows in the table provided between the START and END time provided. Also, the column name will start with an asterisk (*) if that column is changed in the UPDATE statement. Also, before each modified row, it has the time that the update has happened also the process name that did the update. This helped us in finding the issue process which was updating the Primary Key column.
If your shop has DB2 Platinum Tool (CA DATABASE MANAGEMENT SOLUTIONS FOR DB2 FOR Z/OS), below JCL can be used.
In United States, If you would like to Earn Free Stocks, Credit card Points and Bank account Bonuses, Please visit My Finance Blog
You may also like to look at: