Lock suspension report

You can start Db2 performance class 6 to GTF to prepare a lock suspension report.

About this task

Begin program-specific programming interface information. Because that class 6 traces only suspensions, it does not significantly reduce performance. The following figure shows the IBM® OMEGAMON® for Db2 Performance Expert on z/OS® lock suspension report.

Figure 1. Portion of the IBM OMEGAMON for Db2 Performance Expert on z/OS Lock suspension report

⋮
                                                              --SUSPEND REASONS-- ---------- R E S U M E   R E A S O N S ------
PRIMAUTH      --- L O C K   R E S O U R C E --- TOTAL    LOCAL  GLOB.  S.NFY ---- NORMAL ----  TIMEOUT/CANCEL  --- DEADLOCK --
 PLANNAME     TYPE      NAME                    SUSPENDS LATCH  IRLMQ  OTHER NMBR         AET NMBR         AET NMBR        AET
------------- --------- ----------------------- -------- -----  -----  ----- ---- ----------- ---- ----------- ---- ----------
FPB
 PARALLEL     PARTITION DB  =PARADABA                  2     2      0      0    0         N/C    2  303.277805    0        N/C
                        OB  =TAB1TS                          0      0      0
                        PART= 1
LOCKING REPORT COMPLETE

The lock suspension report shows:

Which plans are suspended, by plan name within primary authorization ID. For statements bound to a package, see the information about the plan that executes the package.
What IRLM requests and which lock types are causing suspensions.
Whether suspensions are normally resumed or end in timeouts or deadlocks.
What the average elapsed time (AET) per suspension is.

The report also shows the reason for the suspensions, as described in Table 1.

Table 1. Reasons for suspensions
Reason	Includes
LOCAL	Contention for a local resource
LATCH	Contention for latches within IRLM (with brief suspension)
GLOB.	Contention for a global resource
IRLMQ	An IRLM queued request
S.NFY	Intersystem message sending
OTHER	Page latch or drain suspensions, suspensions because of incompatible retained locks in data sharing, or a value for service use

The preceding list shows only the first reason for a suspension. When the original reason is resolved, the request could remain suspended for a second reason.

Each suspension results in either a normal resume, a timeout, or a deadlock.

The report shows that the suspension causing the delay involves access to partition 1 of table space PARADABA.TAB1TS by plan PARALLEL. Two LOCAL suspensions time out after an average of 5 minutes, 3.278 seconds (303.278 seconds). End program-specific programming interface information.