DB2 crash with S04F (DSNJ113E)

This happened a couple of days ago. One of our CICS transactions inserted more than 18 million rows (probably loop) in a DB2 table before someone noticed and cancelled it. DB2 started rolling back the data but was slow, so the DBAs tried to issue the CANCEL THREAD command with NOBACKOUT option (this was in a TEST region) but that didn't work. So they just let it roll back. When there was less than 2 million rows to be rolled back DB2 crashed with the following message (S04F):

DSNJ113E +DB2T DSNJR003 RBA 'C35EF8BC2000' NOT IN
ANY ACTIVE OR ARCHIVE LOG DATA SET. CONNECTION-ID=DB2T,
CORRELATION-ID=003.RCRSC 02, MEMBER-ID=0

The operators restarted DB2, but it went down again after some time. DB2 manual suggested that the log record might be missing from the active/archive log. DBAs ran DSNJU004 utility and found that this RBA was available in a archive log data set which was not in the BSDS. DB2 manual suggested to add the dataset to the BSDS using DSNJU003 utility. But before doing that, the DBAs stopped DB2 and ran DSNJU003 using the parameters

CRESTART CREATE,FORWARD=YES,BACKOUT=NO

to reset checkpoint. Then they stopped DB2 with MODE(FORCE) since it didn't stop after issuing a regular stop DB2 command. But that failed. So they issued the following command to stop IRLM:

F DB2TIRLM,ABEND,NODUMP

After stopping DB2, they ran the DSNJU003 utility again and then started DB2.

Most of these are possible because it was a TEST system. Not sure what would we have done if this had happened in a PROD system.

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.