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 Tivoli OMEGAMON XE for DB2 Performance Expert on z/OS statistics trace

DATA SHARING LOCKING            QUANTITY  /SECOND  /THREAD  /COMMIT
---------------------------     --------  -------  -------  -------
GLOBAL CONTENTION RATE (%)          0.19  A 
FALSE CONTENTION RATE (%)           0.04  B 
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    C   1478.8K   616.16    12.77     2.48
SYNCH.XES - CHANGE REQUESTS  D  45450.00    18.94     0.39     0.08
SYNCH.XES - UNLOCK REQUESTS  E    810.0K   337.48     7.00     1.36
BACKGROUND.XES -CHILD LOCKS  F      0.00     0.00     0.00     0.00
ASYNCH.XES -CONVERTED LOCKS  G    108.00     0.04     0.00     0.00

SUSPENDS - IRLM GLOBAL CONT  H   3236.00     1.35     0.03     0.01
SUSPENDS - XES GLOBAL CONT.  I    334.00     0.14     0.00     0.00
SUSPENDS - FALSE CONT. MBR   J    863.00     0.36     0.01     0.00
SUSPENDS - FALSE CONT. LPAR  J       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

Attention: In DB2 9 and DB2 10, if the LPAR contains more than one DB2 subsystem and the fixes for APARs OA12164 and PK85159 are not applied, the percentages in this report are not accurate.

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

Table 1. Explanation of the fields in a Tivoli OMEGAMON XE for DB2 Performance Expert on z/OS statistics trace
Field	Explanation
A	The global contention rate.
B	The false contention rate.
C , D , E	These counters indicate the total number of lock, change, and unlock requests (including L-locks and P-locks) that were propagated to XES synchronously.
F	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 requestor's execution unit has been suspended.
G	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.
H	The number of real contentions, as detected by IRLM.
I	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.
J	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.