Monitoring locks by using statistics and accounting traces

You can use statistics and accounting traces to monitor locking activity.

About this task

Begin program-specific programming interface information. 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