Who updated that DB2 table? (how to read DB2 log?)

There are situations when people drop and re-create a DB2 object, such as a Stored Procedure (usually in the development or test environment) using a common shared user-id. It may be necessary to find out who made the change, but may not be possible, because the person used a CURRENT SQLID = 'shared id' or something like that.

Or, sometimes, someone might have updated (or deleted or inserted) a table without logging it anywhere (or that's what they thought). There is no straight forward way to find who updated this table. But, I didn't say that it is not possible.

To find this out, you may need to read the DB2 log. Here are the steps to do just that:

1. Run a batch job which executes DSNJU004 with the correct Boot Strap dataset (BSDS). This program reads the BSDS and displays information from DB2*MSTR, including the active and archive logs.
2. Get the archive log dataset name that was created around the time that the DB2 object got changed. If the log is still active for time that you are looking for, archive it manually (using DB2 ARCHIVE command)
3. Run a batch job which executes DSN1LOGP with SUMMARY(ONLY) option. This program reads the log, formats it and prints it in the output  dataset.
4. In this job output, search the database in which the change was made. Convert the STARTLRSN time to readable timestamp value (I've a REXX script to do it, if you need it, please let me know). If this matches with the time when the DB2 object was changed, then get other details such as CONNID, AUTHID etc.

Note: If you need sample JCLs for the steps mentioned in this blog entry, you can contact me @ ajjuba@yahoo.com.