DB2 Z/os: How to find the thread causing deadlock
Abhishek Verma
Mainframe Modernization Go-to-market Leader Professional Services EMEA | Enterprise Transformation & Cloud Consulting
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 5th to 8th 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.