Here's how to use the new registry variable:

db2set DB2_CAPTURE_LOCKTIMEOUT=ON
then RESTART your instance.

Every time a lock timeout occurs, detailed information about the time out is written to a file in your diagpath. The file names have format of db2locktimeout.par.AGENTID.yyyy-mm-dd-hh-mm-ss.

It's a good idea to enable this variable and to check your diagpath regularly for lock timeouts on your system.

Note: Lock timeouts will never occur if your DB CFG parameter of LOCKTIMEOUT is set to -1. DB2 will simply wait forever for the lock to be freed. It's a good idea to set LOCKTIMEOUT to a value such as 30 seconds so your application doesn't hang forever.

LOCK TIMEOUT REPORT

Date: 10/01/2008
Time: 17:08:59
Instance: DB2
Database: SAMPLE
Database Partition: 0

Lock Information:

Lock Name: 02000F00270000060000000052
Lock Type: Row
Lock Specifics: Tablespace ID=2, Table ID=15, Row ID=x2700000600000000

Lock Requestor:
System Auth ID: ROB
Application Handle: [0-7]
Application ID: *LOCAL.DB2.080110220733
Application Name: db2bp.exe
Requesting Agent ID: 5920
Coordinator Agent ID: 5920
Coordinator Partition: 0
Lock timeout Value: 1000 milliseconds
Lock mode requested: .NS
Application Status: (SQLM_UOWEXEC)
Current Operation: (SQLM_FETCH)
Lock Escalation: No

Context of Lock Request:
Identification: UOW ID (1); Activity ID (1)
Activity Information:
Package Schema: (NULLID )
Package Name: (SQLC2G13NULLID )
Package Version: ()
Section Entry Number: 201
SQL Type: Dynamic
Statement Type: DML, Select (blockable)
Effective Isolation: Cursor Stability
Statement Unicode Flag: No
Statement: SELECT * FROM STAFF

Lock Owner (Representative):
System Auth ID: ROB
Application Handle: [0-12]
Application ID: *LOCAL.DB2.080110220755
Application Name: db2bp.exe
Requesting Agent ID: 2164
Coordinator Agent ID: 2164
Coordinator Partition: 0
Lock mode held: ..X

List of Active SQL Statements:

Entry: #1
Identification: UOW ID (2); Activity ID (1)
Package Schema: (NULLID )
Package Name: (SQLC2G13NULLID )
Package Version: ()
Section Entry Number: 1
SQL Type: Dynamic
Statement Type: DML, Select (blockable)
Effective Isolation: Cursor Stability
Statement Unicode Flag: No
Statement: select * from staff

List of Inactive SQL Statements from current UOW:
Entry #1
Identification: UOW ID (2); Activity ID (3)
Package Schema: (NULLID )
Package Name: (SQLC2G13NULLID)
Package Version: ()
Section Entry Number: 203
SQL Type: Dynamic
Statement Type: DML, Insert/Update/Delete
Effective Isolation: Cursor Stability
Statement Unicode Flag: No
Statement: INSERT INTO staff(id,name,dept,job,years,salary,comm) VALUES(11,'test',20,'Mgr',7,1.00,NULL)

Rob Williams and Martin Hubel
www.mhubel.com