Monitoring locks by using statistics and accounting traces
You can use statistics and accounting traces to monitor locking activity.
About this task
Tivoli® OMEGAMON® XE for Db2 Performance Expert on z/OS® provides
one way to view the trace results. The Tivoli OMEGAMON XE for Db2 Performance Expert on z/OS reports
for the accounting and statistics traces each corresponds to a single Db2 trace
record.
Procedure
To monitor locking activity by using statistic and accounting reports:
- Use the statistic trace to monitor locking activity on
the subsystem. The following figure shows a portion of the statistics trace. It shows how many suspensions, timeouts, deadlocks, and lock escalations occur in the trace record. You can also use the statistics trace to monitor lock escalation.
Figure 1. Locking activity blocks from statistics trace LOCKING,ACTIVITY QUANTITY /SECOND /THREAD /COMMIT --------------------------- -------- ------- ------- ------- SUSPENSIONS (ALL) 107.4K 30.33 0.17 0.07 SUSPENSIONS (LOCK ONLY) 1126.00 0.32 0.00 0.00 SUSPENSIONS (IRLM LATCH) 70701.00 19.97 0.11 0.05 SUSPENSIONS (OTHER) 35524.00 10.04 0.06 0.02 TIMEOUTS 0.00 0.00 0.00 0.00 DEADLOCKS 0.00 0.00 0.00 0.00 LOCK REQUESTS 36558.4K 10.3K 57.12 25.30 UNLOCK REQUESTS 9007.3K 2544.45 14.07 6.23 QUERY REQUESTS 34751.00 9.82 0.05 0.02 CHANGE REQUESTS 853.4K 241.08 1.33 0.59 OTHER REQUESTS 0.00 0.00 0.00 0.00 LOCK ESCALATION (SHARED) 2.00 0.00 0.00 0.00 LOCK ESCALATION (EXCLUSIVE) 1.00 0.00 0.00 0.00 DRAIN REQUESTS 6131.00 1.73 0.01 0.00 DRAIN REQUESTS FAILED 58.00 0.02 0.00 0.00 CLAIM REQUESTS 21397.8K 6044.59 33.43 14.81 - Try to keep the number of unlock requests per commit to
fewer than five. You can expect a greater number of unlock requests per commit for applications that use the ISOLATION(CS) and CURRENTDATA(YES) bind options.
- Use accounting trace data to investigate specific applications
that have high numbers of lock and unlock requests. The following figure shows a portion of the accounting trace. It shows the suspensions, timeouts, deadlocks, lock escalations for a particular application. It also shows the maximum number of concurrent page locks that are held and acquired during the trace. If this value is large, you can try to reduce it by reducing the number of locks that are acquired, or by committing more frequently. This value is also the basis for setting the values of the NUMLKUS and NUMLKTS subsystem parameters.
The LOCK/LATCH(DB2_IRLM) time is broken down into specific values for the IRLM locks and latches and Db2 latch activity. The accounting report also breaks down the total suspensions in lock, lock, latch, and other suspensions. You can use this information to isolate the problem.
Figure 2. Locking activity blocks from the accounting trace report CLASS 3 SUSPENSIONS ELAPSED TIME EVENTS -------------------- ------------ -------- LOCK/LATCH(DB2+IRLM) 0.362856 1947 IRLM LOCK+LATCH 0.274047 692 DB2 LATCH 0.088809 1455 ............................................ LOCKING TOTAL ------------------- -------- TIMEOUTS 0 DEADLOCKS 0 ESCAL.(SHAR) 0 ESCAL.(EXCL) 0 MAX PG/ROW LCK HELD 13 LOCK REQUEST 407685 UNLOCK REQST 59302 QUERY REQST 0 CHANGE REQST 10 OTHER REQST 0 TOTAL SUSPENSIONS 692 LOCK SUSPENS 60 IRLM LATCH SUSPENS 632 OTHER SUSPENS 0