Isolating resources that cause suspensions

When lock suspensions are unacceptably long or timeouts occur, you can use the Db2 performance trace for locking and the IBM® OMEGAMON® for Db2 Performance Expert on z/OS® reports to isolate the resource that causes the suspensions.

To isolate the resources that cause a suspension:

1. Use the lockout report to identify the resources involved in the suspensions.
   The following figure shows a sample IBM OMEGAMON for Db2 Performance Expert on z/OS lockout report. The sample report shows that the PARALLEL plancontends with the DSNESPRR plan. It also shows that contention is occurring on partition 1 of the PARADABA.TAB1TS table space :

   PRIMAUTH           --- L O C K   R E S O U R C E ---                      --------------- A G E N T S --------------
    PLANNAME          TYPE      NAME                    TIMEOUTS DEADLOCKS   MEMBER    PLANNAME  CONNECT   CORRID         HOLDER WAITER
   ------------------ --------- ----------------------- -------- ---------   --------  --------- --------  ------------   ------ ------
   FPB
    PARALLEL          PARTITION DB  =PARADABA                  2         0   N/P       DSNESPRR  TSO       EOA                 2      0
                                OB  =TAB1TS
                                PART= 1
                      ** LOCKOUTS FOR PARALLEL   **            2         0

2. Use the lockout trace to identify the contending process (agent) that cause the timeout.
   The lockout trace contains information about contention from a single Db2 trace record.
   The following figure shows the IBM OMEGAMON for Db2 Performance Expert on z/OS lockout trace. For each contender, this report shows the database object, lock state (mode), and duration for each contention for a transaction lock.

   ⋮
   PRIMAUTH CORRNAME CONNTYPE
   ORIGAUTH CORRNMBR INSTANCE     EVENT TIMESTAMP            --- L O C K   R E S O U R C E ---
   PLANNAME CONNECT               RELATED TIMESTAMP EVENT    TYPE      NAME                    EVENT SPECIFIC DATA
   ------------------------------ ----------------- -------- --------- ----------------------- ----------------------------------------
   FPB      FPBPARAL TSO          15:25:27.23692350 TIMEOUT  PARTITION DB  =PARADABA           REQUEST =LOCK       UNCONDITIONAL
   FPB      'BLANK'  AB09C533F92E N/P                                  OB  =TAB1TS             STATE   =S          ZPARM INTERVAL=  300
   PARALLEL BATCH                                                      PART= 1                 DURATION=COMMIT     INTERV.COUNTER=    1
                                                                                               HASH    =X'000020E0'
                                                                                               ------------ HOLDERS/WAITERS -----------
                                                                                               HOLDER
                                                                                               LUW='BLANK'.IPSAQ421.AB09C51F32CB
                                                                                               MEMBER  =N/P        CONNECT =TSO
                                                                                               PLANNAME=DSNESPRR   CORRID=EOA
                                                                                               DURATION=COMMIT     PRIMAUTH=KARELLE
                                                                                               STATE   =X
    
   KARL     KARL     TSO          15:30:32.97267562 TIMEOUT  PARTITION DB  =PARADABA           REQUEST =LOCK       UNCONDITIONAL
   KARL     'BLANK'  AB09C65528E6 N/P                                  OB  =TAB1TS             STATE   =IS         ZPARM INTERVAL=  300
   PARALLEL TSO                                                        PART= 1                 DURATION=COMMIT     INTERV.COUNTER=    1
                                                                                               HASH    =X'000020E0'
                                                                                               ------------ HOLDERS/WAITERS -----------
                                                                                               HOLDER
                                                                                               LUW='BLANK'.IPSAQ421.AB09C51F32CB
                                                                                               MEMBER  =N/P        CONNECT =TSO
                                                                                               PLANNAME=DSNESPRR   CORRID=EOA
                                                                                               DURATION=COMMIT     PRIMAUTH=DAVE
                                                                                               STATE   =X
                                                                                               ENDUSER =DAVEUSER
                                                                                               WSNAME  =DAVEWS
                                                                                               TRANS   =DAVES TRANSACTION
   LOCKING TRACE COMPLETE

   At this point in the investigation, you know the following information:

   • The applications that contend for resources.
   • The page sets for which contention occurs.
   • The impact, frequency, and type of the contentions.
3. Examine the CLASS 3 LOCK/LATCH (DB2+IRLM) and #OCCURRENCES fields of theIBM OMEGAMON for Db2 Performance Expert on z/OS accounting report to find the suspension time and number of occurrences in the Accounting Report. The class 3 lock and latch time and number of occurrences in the accounting report are key statistics to analyze when you encounter locking problems If locking and latching are increasing the elapsed time of your transactions or batch work, investigate further.
   The accounting report - long shows the average elapsed times and the average number of suspensions per plan execution.

   • The class 1 average elapsed time is shown in the ELAPSED TIME field under APPL(CL.1). The class 2 times are shown in the ELAPSED TIME field under DB2(CL.2). that are spent in Db2. The remainder of the time is spent in the application.
   • Part of the class 2 elapsed time is spent waiting for lock or latch suspensions. This time is shown in the (LOCK/LATCH (DB2 + IRLM) field. Most of the suspension time is spent in IRLM LOCK+LATCH. In the Locking section, it shows that is shows that most of the suspensions are IRLM Lock Suspensions
   • The HIGHLIGHTS section of the report shows the number of transactions processed for the accounting interval in the #OCCURRENCES field. In this example DDF accounting rollup is used.

   
   The following figure shows a portion of the IBM OMEGAMON for Db2 Performance Expert on z/OS accounting report - long.

   AVERAGE       APPL(CL.1)  DB2 (CL.2)  IFI (CL.5)    CLASS 3 SUSPENSIONS   AVERAGE TIME  AV.EVENT    HIGHLIGHTS
   ------------  ----------  ----------  ----------    --------------------  ------------  --------    --------------------------
   ELAPSED TIME    0.136869    0.022632    0.000429    LOCK/LATCH(DB2+IRLM)      0.000192      0.14    #OCCURRENCES    :  1568491
    NONNESTED      0.124535    0.010763         N/A     IRLM LOCK+LATCH          0.000188      0.04    #ALLIEDS        :        0
    STORED PROC    0.012321    0.011859         N/A     DB2 LATCH                0.000004      0.11    #ALLIEDS DISTRIB:        0
    UDF            0.000002    0.000000         N/A    SYNCHRON. I/O             0.010347      6.38    #DBATS          :  1474051
    TRIGGER        0.000010    0.000010         N/A     DATABASE I/O             0.009948      6.18    #DBATS DISTRIB. :    94440
                                                        LOG WRITE I/O            0.000399      0.19    #NO PROGRAM DATA:        0
   CP CPU TIME     0.004499    0.004212    0.000333    OTHER READ I/O            0.003111      3.52    #NORMAL TERMINAT:       29
    AGENT          0.004499    0.004212         N/A    OTHER WRTE I/O            0.000002      0.00    #DDFRRSAF ROLLUP:    82834
     NONNESTED     0.002150    0.002102    0.000333    SER.TASK SWTCH            0.000291      0.05    #ABNORMAL TERMIN:        0
     STORED PRC    0.002345    0.002107         N/A     UPDATE COMMIT            0.000014      0.01    #CP/X PARALLEL. :        0
     UDF           0.000001    0.000000         N/A     OPEN/CLOSE               0.000120      0.01    #UTIL PARALLEL. :        0
     TRIGGER       0.000003    0.000003         N/A     SYSLGRNG REC             0.000004      0.00    #IO PARALLELISM :     1402
    PAR.TASKS      0.000000    0.000000         N/A     EXT/DEL/DEF              0.000015      0.00    #PCA RUP COUNT  :      N/A
                                                        OTHER SERVICE            0.000139      0.03    #RUP AUTONOM. PR:      N/A
   SE CPU TIME     0.003034    0.002970         N/A    ARC.LOG(QUIES)            0.000000      0.00    #AUTONOMOUS PR  :      N/A
    NONNESTED      0.003032    0.002968         N/A    LOG READ                  0.000000      0.00    #INCREMENT. BIND:       40
    STORED PROC    0.000002    0.000002         N/A    DRAIN LOCK                0.000001      0.00    #COMMITS        :  1650053
    UDF            0.000000    0.000000         N/A    CLAIM RELEASE             0.000000      0.00    #ROLLBACKS      :     1457
    TRIGGER        0.000000    0.000000         N/A    PAGE LATCH                0.000002      0.16    #SVPT REQUESTS  :        0
                                                       NOTIFY MSGS               0.000002      0.00    #SVPT RELEASE   :        0
    PAR.TASKS      0.000000    0.000000         N/A    GLOBAL CONTENTION         0.000191      0.11    #SVPT ROLLBACK  :        0
                                                       COMMIT PH1 WRITE I/O      0.000000      0.00    MAX SQL CASC LVL:        3
   SUSPEND TIME    0.000117    0.014248         N/A    ASYNCH CF REQUESTS        0.000038      0.52    UPDATE/COMMIT   :     1.54
    AGENT               N/A    0.014248         N/A    TCP/IP LOB XML            0.000071      0.05    SYNCH I/O AVG.  : 0.001623
    PAR.TASKS           N/A    0.000000         N/A    ACCELERATOR               0.000000      0.00
    STORED PROC    0.000116         N/A         N/A    AUTONOMOUS PROCEDURE           N/A       N/A
    UDF            0.000002         N/A         N/A    PQ SYNCHRONIZATION             N/A       N/A
                                                       TOTAL CLASS 3             0.014248     10.94
   NOT ACCOUNT.         N/A    0.001201         N/A
   DB2 ENT/EXIT         N/A        8.33         N/A
   EN/EX-STPROC         N/A       46.96         N/A
   EN/EX-UDF            N/A        0.00         N/A
   DCAPT.DESCR.         N/A         N/A    0.000000
   LOG EXTRACT.         N/A         N/A    0.000000
   

4. Use the IBM OMEGAMON for Db2 Performance Expert on z/OS locking timeout trace to obtain the information necessary to reduce overheads.

What to do next

Take action to improve concurrency for the resources involved.