DB2: How to find out the root cause for dead lock and time out abends

When you receive a deadlock or time out time abend, go to JESMSGLG of the Db2 master job and the job name will be in the form of XXXXMSTR , where XXXX is the DB2 subsystem id. Locate your job name or CICS region name in the JESMSGLG and make sure the time stamp in the located line matches with the time at which the abend has occurred.

The below messages indicate that the job J9234CF5 deadlocked with another job J9234CF3 and job J9234CF5 received “resource unavailable” error. Here CORRELATION-ID indicates the job name and CONNECTION-ID = BATCH indicates that it is a batch job.

01.10.14 S0034400  DSNT375I  #DB1T PLAN=X5810DXB WITH  316            

    316                     CORRELATION-ID=J9234CF5                    

    316                     CONNECTION-ID=BATCH                        

    316                     LUW-ID=ABCGDV.ABC02B1T.C8B48D19A95B=109801 

    316                     THREAD-INFO=X968701:*:*:*                  

    316                     IS DEADLOCKED WITH PLAN=X6418DXB WITH      

    316                     CORRELATION-ID=J9234CF3                    

    316                     CONNECTION-ID=BATCH                        

    316                     LUW-ID=ABCGDV.ABC02B1T.C8B48D19AD32=109804 

    316                     THREAD-INFO=X568701:*:*:*                  

    316                     ON MEMBER DBT2                             

 01.10.14 S0034400  DSNT501I  #DB1T DSNILMCL RESOURCE UNAVAILABLE  317 

    317                        CORRELATION-ID=J9234CF5                 

    317                        CONNECTION-ID=BATCH                     

    317                        LUW-ID=ABCGDV.UHG02B1T.C8B48D19A95B=10980

    317                        REASON 00C90088                         

    317                        TYPE 00000D01                           

    317                        NAME 00000402.00002466                  

To find out the name of the resource causing the issue, first we need to find out the type of that resource. In the above DSNT501I message, we can see that resource TYPE is “00000D01”. If do “QW DSNT501I” (QuickRef), you can find what TYPE=00000D01 mean. Once you know the type of the resource, you can use any one of the below suitable queries to find out the name of that resource. In “NAME 00000402.00002466” message, 402 is the DBID (database id) and 2466 is the OBID (object id).

SELECT NAME FROM SYSIBM.SYSTABLES

WHERE DBID = XXX AND OBID = YYY

SELECT NAME FROM SYSIBM.SYSINDEXES

WHERE DBID = XXX AND OBID = YYY

SELECT NAME FROM SYSIBM.SYSTABLESPACE

WHERE DBID = XXX AND OBID = YYY

The below messages indicates, transaction “IPRO” timed out due to transaction “IMPP”. In this case CONNECTION-ID has the CICS region name and 5^th to 8^th character of CORRELATION-ID has the transactin id.

02.41.37 S0034400  DSNT376I  #DBT2 PLAN=X5940DTX WITH  183            

   183                     CORRELATION-ID=POOLIPRO0002                

   183                     CONNECTION-ID=CICTXCB1                      

   183                     LUW-ID=UHGGDV.ABC02B1T.C8B49F9B21D0=138198 

   183                     THREAD-INFO= CICTXCB1:*:*:*                 

   183                     IS TIMED OUT. ONE HOLDER OF THE RESOURCE IS

   183             WITH                                               

   183                     CORRELATION-ID=POOLIMPP0001                

   183                     CONNECTION-ID= CICTXCB1                     

   183                     LUW-ID=ABCGDV.ABC02B1T.C8B4A05E77F8=139198 

   183                     THREAD-INFO= CICTXCB1:*:*:*                 

   183                     ON MEMBER DBT2                             

02.41.37 S0034400  DSNT501I  #DB1T DSNILMCL RESOURCE UNAVAILABLE  185 

   185                        CORRELATION-ID=POOLIPRO0002             

   185                        CONNECTION-ID= CICTXCB1                  

   185                        LUW-ID=ABCGDV.ABC02B1T.C8B49F9B21D0=13819

   185                        REASON 00C9008E                          

   185                        TYPE 00000302                           

   185                        NAME X5490DB1.TS5230CI.X'0004D9'        

Here NAME of the resource shows a table space and  X'0004D9' is the page id causing the contention.