Version information: DB2 64-bit instance Version 9 Fixpack 4 running on Solaris 10.
Summary: DB2 System Monitor elements LOCK_TIMEOUTS and DEADLOCKS both are incremented for SQLCODE -911 (sql0911n) but LOCK_TIMEOUTS is incremented for Reason Code 68 whereas DEADLOCKS is incremented for Reason Code 2. SQLCODE -911 actually covers 3 different timeout scenarios (the third related to DB2 Data Links Manager, Reason Code 72). I don’t know why IBM wouldn’t have given us three separate SQLSTATE’s, it would have been less confusing.
The Problem: On three separate days August 26-28 2008 my production application experienced a large number of lock timeouts (SQLCODE: -911, SQLSTATE: 40001 ) as evidenced by application logs (each incident period recorded over forty separate -911 events, over 100 lock timeouts in 3 days). I did not have access to Reason Code information. The System Monitor element LOCK_TIMEOUTS counter was not being incremented. I capture and store snapshot data every five minutes both via text (db2 get snapshot for all on dbname) and via inserts from Snapshot Administrative View sysibmadm.snapdb.
The Solution: According to IBM (private communication November 2008), monitor element LOCK_TIMEOUTS only gets incremented for SQLCODE -911 Reason Code 68. SQLCODE -911 which occurs due to deadlock results in a different Reason Code of 2. SQLCODE -911 Reason Code 2 will increment System Monitor element DEADLOCKS but not LOCK_TIMEOUTS. The behavior of the two counter elements is mutually exclusive. If you only have access to SQLCODE/SQLSTATE, but not Reason Code, you are left to guess what kind of timeout the -911 was. You can infer it by looking at LOCK_TIMEOUTS and DEADLOCKS elements.
Other APARS about Misleading Snapshot Element Values
