Identifying Locked DB2 Resources
Last week I explained how to find the cause of a deadlock or timeout. This week, I'll show you how to identify the name and type of DB2 resource being locked. We'll start with the SQLCODE -911 message:
THE CURRENT UNIT OF WORK HAS BEEN ROLLED BACK DUE TO DEADLOCK OR TIMEOUT.
REASON reason-code, TYPE OF RESOURCE resource-type, AND RESOURCE NAME resource-name
The reason code will be either 00C90088 (deadlock) or 00C9008E (timeout). The resource-type can be found in the DB2 for z/OS CODES manual or at the IBM Information Center.
The common resource types related to objects being locked are:
TYPE Code Type of Resource Name,Content,Format
00000100 Database DB
00000200 Table space DB.SP
00000201 Index space DB.SP
00000210 Partition DB.SP.PT
00000D00 DBID/OBID RD.DI.OI
00000D01 DBID/OBID DI.OI
What you will see in the error message are codes that have to be translated. For instance, here's a sample message for a locked database where the name is DSN8D11P:
DSNT408I SQLCODE = -911, ERROR: THE CURRENT UNIT OF WORK HAS BEEN ROLLED BACK DUE TO DEADLOCK ORTIMEOUT. REASON 00C9008E, TYPE OF RESOURCE 00000100,AND RESOURCE NAME DSN8D11P
And here's a sample message for a locked table where the name is 403.43:
DSNT408I SQLCODE = -911, ERROR: THE CURRENT UNIT OF WORK HAS BEEN ROLLED BACK DUE TO DEADLOCK ORTIMEOUT. REASON 00C9008E, TYPE OF RESOURCE 00000D01,AND RESOURCE NAME 00000403.00000043
As noted in the DB2 for z/OS Messages Guide and explained here, when the resource type is D01, you need to look for a table name. To do so, use this query:
SELECT CREATOR, NAME
FROM SYSIBM.SYSTABLES
WHERE DBID = 403 AND OBID = 43;
If the resource-type is X'D00' or X'D01', use this SQL statement to determine the table name:
SELECT CREATOR, NAME
FROM SYSIBM.SYSTABLES
WHERE DBID = dbid AND OBID = obid;
For more information related to this topic, see my previous posts about DB2 locking and application design for concurrency:
* Batch Tuning: Checkpoint Restart
* Batch Tuning:Continuous Availability
Connect With Us: