Lock monitoring with the Db2 accounting trace
Use the Db2 accounting trace to determine which users or plans are experiencing global lock contention.
The accounting trace provides a summary of thread resource usage within Db2. Db2 threads experiencing global lock contention are shown in accounting trace class 1, as shown in the figure below. The accumulated elapsed time of the suspensions are shown in accounting trace class 3.
LOCKING AVERAGE TOTAL DATA SHARING AVERAGE TOTAL
-------------- -------- -------- ------------------- -------- --------
TIMEOUTS 0.00 3 GLOBAL CONT RATE(%) 0.54 N/A B
DEADLOCKS 0.00 0 FALSE CONT RATE(%) 0.00 N/A C
ESCAL.(SHARED) 0.00 0 P/L-LOCKS XES (%) 17.97 N/A D
ESCAL.(EXCLUS) 0.00 2 LOCK REQ - PLOCKS 2.92 8317127 E
MAX PG/ROW LOCKS HELD 0.92 5053 UNLOCK REQ - PLOCKS 2.76 7849772
LOCK REQUEST 92.74 264098K A CHANGE REQ - PLOCKS 0.01 20661
UNLOCK REQUEST 7.78 22155610 LOCK REQ - XES 17.19 48941369 F
QUERY REQUEST 0.00 0 UNLOCK REQ - XES 8.03 22863352 G
CHANGE REQUEST 0.42 1196297 CHANGE REQ - XES 0.26 754626 H
OTHER REQUEST 0.00 0 SUSPENDS - IRLM 0.14 396784 I
TOTAL SUSPENSIONS 16.78 47784696 SUSPENDS - XES 0.00 0 J
LOCK SUSPENSIONS 0.00 10897 CONVERSIONS- XES 0.05 128351 K
IRLM LATCH SUSPENS. 16.78 47773728 FALSE CONTENTIONS 0.00 0 L
OTHER SUSPENS. 0.00 71 INCOMPATIBLE LOCKS 0.00 0
NOTIFY MSGS SENT 0.00 73
The following formulas are used to calculate the global contention rate, false contention rate, and percentage of P/L-lock requests that were synchronously propagated to XES:
GLOBAL CONT RATE(%) = (I+J+L) / (F+G+H+I+J+K+L)
FALSE CONT RATE(%) = (K+L) / (F+G+H+I+J+K+L)
P/L-LOCKS XES(%) = F / (A+E)
FALSE CONT RATE(%) = (K+L) / (F+G+H+I+J+K+L)
P/L-LOCKS XES(%) = F / (A+E)