Lock monitoring with the Db2 statistics trace

The Db2 statistics trace provides counters that track the amount of global locking activity and contention that each member in the data sharing group is encountering.

This trace runs with low overhead. Keep the Db2 statistics trace turned on to allow continuous monitoring of each subsystem.

The following figure shows the type of information that is provided by a statistics trace.

Figure 1. Data sharing locking block of IBM® OMEGAMON® for Db2 Performance Expert on z/OS® statistics trace

DATA SHARING LOCKING            QUANTITY  /SECOND  /THREAD  /COMMIT
---------------------------     --------  -------  -------  -------
GLOBAL CONTENTION RATE (%)          0.19
FALSE CONTENTION RATE (%)           0.04
P/L-LOCKS XES RATE (%)             36.47

LOCK REQUESTS (P-LOCKS)           107.4K    44.74     0.93     0.18
UNLOCK REQUESTS (P-LOCKS)         105.2K    43.82     0.91     0.18
CHANGE REQUESTS (P-LOCKS)        1453.00     0.61     0.01     0.00

SYNCH.XES - LOCK REQUESTS        1478.8K   616.16    12.77     2.48
SYNCH.XES - CHANGE REQUESTS     45450.00    18.94     0.39     0.08
SYNCH.XES - UNLOCK REQUESTS       810.0K   337.48     7.00     1.36
BACKGROUND.XES -CHILD LOCKS         0.00     0.00     0.00     0.00
ASYNCH.XES -CONVERTED LOCKS       108.00     0.04     0.00     0.00

SUSPENDS - IRLM GLOBAL CONT      3236.00     1.35     0.03     0.01
SUSPENDS - XES GLOBAL CONT.       334.00     0.14     0.00     0.00
SUSPENDS - FALSE CONT. MBR        863.00     0.36     0.01     0.00
SUSPENDS - FALSE CONT. LPAR          N/A      N/A      N/A      N/A
REJECTED - XES                  31897.00    13.29     0.28     0.05
INCOMPATIBLE RETAINED LOCK          0.00     0.00     0.00     0.00
NOTIFY MESSAGES SENT              344.00     0.14     0.00     0.00
NOTIFY MESSAGES RECEIVED          106.00     0.04     0.00     0.00
P-LOCK/NOTIFY EXITS ENGINES       500.00      N/A      N/A      N/A
P-LCK/NFY EX.ENGINE UNAVAIL         0.00     0.00     0.00     0.00

PSET/PART P-LCK NEGOTIATION        38.00     0.02     0.00     0.00
PAGE P-LOCK NEGOTIATION          3026.00     1.26     0.03     0.01
OTHER P-LOCK NEGOTIATION         1520.00     0.63     0.01     0.00
P-LOCK CHANGE DURING NEG.        3072.00     1.28     0.03     0.01

The following table contains an explanation of the fields that are shown in the previous figure.

Table 1. Explanation of the fields in a IBM OMEGAMON for Db2 Performance Expert on z/OS statistics trace
Field	Explanation
`GLOBAL CONTENTION RATE (%)`	The global contention rate.
`FALSE CONTENTION RATE (%)`	The false contention rate.
`SYNCH.XES - LOCK REQUESTS, CHANGE REQUESTS, UNLOCK REQUESTS`	These counters indicate the total number of lock, change, and unlock requests (including L-locks and P-locks) that were propagated to XES synchronously.
`BACKGROUND.XES -CHILD LOCKS`	The number of resources (including L-locks and P-locks) that were propagated by IRLM to XES asynchronously to the original request. This situation occurs when new inter-Db2 interest occurs on a parent resource, or when a request completes after the requester's execution unit has been suspended.
`ASYNCH.XES -CONVERTED LOCKS`	The number of synchronous-to-asynchronous heuristic conversions for lock requests in XES. This conversion occurs when XES determines that it is more efficient to send the request asynchronously to the coupling facility.
`SUSPENDS - IRLM GLOBAL CONT`	The number of real contentions, as detected by IRLM.
`SUSPENDS - FALSE CONT. MBR`	The number of real contentions, as detected by XES, that were not IRLM-level contentions. IRLM has knowledge of more lock types than XES. Thus, IRLM often resolves contention that XES cannot. The most common example of XES-level contention is usually the intent locks (IS and IX) on the parent L-locks. IS and IX are compatible to IRLM but not to XES. Another common example is the U and S page L-locks; U and S are compatible to IRLM, but not to XES.
`SUSPENDS - FALSE CONT. LPAR`	The total number of false contentions for LOCK and UNLOCK requests at the subsystem (member) level or the LPAR level. This information is helpful when the LPAR contains more than one Db2 member. The QTGSFCON flag indicates whether the false contention is reported at the subsystem level or the LPAR level.