Tags

, , , , , , , , , , , , , , , ,

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:

A “Medium” Post on how this blog started
Working on Mainframes – Is Change to a different technology necessary
Important SQL CODES and ABEND CODES
SORT JOIN – TO JOIN TWO FILES BASED ON A KEY
KNOW YOUR MAINFRAME
REXX – INITIAL SETUP
HOW TO SUBMIT A BATCH JOB FROM THE CICS PROGRAM
CICS – EXEC interface block – EIBRESP Values
CICS – EXEC interface block Fields
Flow of control between COBOL programs, run units, and CICS
CICS INTERVIEW QUESTIONS
CICS TIPS
COBOL – COPY and INCLUDE statements
COBOL – PERFORMANCE IMPROVEMENT
COBOL – SIGN STORED IN COMP, COMP-3 AND DISPLAY FORMATS
SHORTEST COBOL PROGRAM
RESTART LOGIC IN COBOL DB2 Program
GOBACK – EXIT PROGRAM – STOP RUN
Continuation lines in COBOL
Computational items – COMP, COMP 1 , COMP 2, COMP 3
COBOL program format
cobol indicator area column-7 and area a and area b
COBOL INTERVIEW QUESTIONS
COBOL TIPS
COBOL COMPILER OPTIONS
The IDENTIFICATION DIVISION