DB2PD – Diagnosing a lockwait
In RDBMS database, to validate data integrity, it has concept called as lockwait. Lockwait goes like wait on lock to be released by another application. The time which is need to get hold of perticular resource by holding lock is measured in time.
DB2 has tool which provides a way to diagnose lockwait condition. Following is example of this.
Locks: Address TranHdl Lockname Type Mode Sts Owner Dur HldCnt Att ReleaseFlg 0x07800000202E5238 3 00020002000000040000000052 Row ..X G 3 1 0 0x0000 0x40000000 0x07800000202E4668 2 00020002000000040000000052 Row ..X W* 2 1 0 0x0000 0x40000000
For the database that you specified using the -db database name option, the first results show the locks for that database. The results show that TranHdl 2 is waiting on a lock held by TranHdl 3.
Transactions: Address AppHandl [nod-index] TranHdl Locks State Tflag Tflag2 Firstlsn Lastlsn LogSpace SpaceReserved TID AxRegCnt GXID 0x0780000020251B80 11 [000-00011] 2 4 READ 0x00000000 0x00000000 0x000000000000 0x000000000000 0 0 0x0000000000B7 1 0 0x0780000020252900 12 [000-00012] 3 4 WRITE 0x00000000 0x00000000 0x000000FA000C 0x000000FA000C 113 154 0x0000000000B8 1 0
We can see that TranHdl 2 is associated with AppHandl 11 and TranHdl 3 is associated with AppHandl 12.
Applications: Address AppHandl [nod-index] NumAgents CoorPid Status C-AnchID C-StmtUID L-AnchID L-StmtUID Appid 0x07800000006879E0 12 [000-00012] 1 1073336 UOW-Waiting 0 0 17 1 *LOCAL.burford.060303225602 0x0780000000685E80 11 [000-00011] 1 1040570 UOW-Executing 17 1 94 1 *LOCAL.burford.060303225601
We can see that AppHandl 12 last ran dynamic statement 17, 1. ApplHandl 11 is currently running dynamic statement 17, 1 and last ran statement 94, 1.
Dynamic SQL Statements: Address AnchID StmtUID NumEnv NumVar NumRef NumExe Text 0x07800000209FD800 17 1 1 1 2 2 update pdtest set c1 = 5 0x07800000209FCCC0 94 1 1 1 2 2 set lock mode to wait 1
We can see that the text column shows the SQL statements that are associated with the lock timeout.
