Example of thread monitoring

This example demonstrates how to identify a possible lock suspension problem using the Online Monitor thread activity function.

Display the Thread Summary panel by selecting option 1 (Display Thread Activity) from the Online Monitor Main Menu.
Figure 1. Thread Summary panel
 DGOMTPLS 13:49               Thread Summary                   ROW 1 TO 5 OF 5

 PMO1DLOC           DSN1 V10  GROUP001 MEMBER01

 To display a thread, place any character next to it, then press Enter.

                         Program      Connection         ------- Elapsed -------
   Primauth   Planname   name         ID         Status     Class 1      Class 2
 _ SYSADM     KO2PLAN    SYSADM       DB2CALL    APPL    37:30.3691    1.2797744
 _ USERT001   KO2PLAN    USERT001     DB2CALL    APPL    19:06.9127    0.6458763
 _ USERT001   DSNESPRR   USERT001     TSO        APPL    30:10.7428    0.9713783
 _ USERT002   KO2PLAN    USERT002     DB2CALL    DB2     46:25.5780    1.5100349
 _ USERT003   DSNESPCS   USERT003     TSO        DB2     31.4012740    0.9831031
 -- End of Thread list --






 Command ===> _________________________________________________________________
  F1=Help      F2=Split     F3=Exit      F5=Auto      F6=History   F7=Up
  F8=Down      F9=Swap     F10=Qualify  F11=Sort     F12=Cancel   F16=Look
 F17=Collect  F19=Left     F20=Right    F22=Purge
Note: To view Class 3 times in the Thread Summary panel, you need to scroll the display to the right (F20).
Type SORT on the command line and press Enter to display the DB2® Thread Sort Specification window, where you can sort threads by class 3 lock/latch times in descending sequence so that threads with the most time spent waiting because of lock and latch suspensions are sorted to the top.
Figure 2. Db2 Thread Sort Specification window
 DGOMTWSS              DB2 Thread Sort Specification

 Specify field order by number in range 1 to 15. Enter 1 for Ascending or
 2 for Descending sequence.

   Column                         Order              Sequence
                                                              More:   - +
   Prim Auth  . . . . . . . . . . . __                     _
   Plan Name  . . . . . . . . . . . __                     _

   Package/DBRM
     Collection ID  . . . . . . . . __                     _
     Program Name . . . . . . . . . __                     _

   Correlation  . . . . . . . . . . __                     _
   Connection ID  . . . . . . . . . __                     _
   Connection type  . . . . . . . . __                     _
   Requesting location  . . . . . . __                     _
   Thread status  . . . . . . . . . __                     _
   Request count  . . . . . . . . . __                     _

   Class 1 times
     Elapsed  . . . . . . . . . . . __                     _
     CPU  . . . . . . . . . . . . . __                     _

   Class 2 times
     Elapsed  . . . . . . . . . . . __                     _
     CPU  . . . . . . . . . . . . . __                     _

   Class 3 time . . . . . . . . .   1_                     2

 Command ===> ___________________________________________________________
  F1=Help     F2=Split    F3=Exit     F7=Up       F8=Down     F9=Swap
 F12=Cancel  F16=Look    F17=Collect

You can also sort threads by class 3 lock/latch times in descending sequence by typing the command SORT C3E and pressing Enter on the command line.

For information about the SORT command see SORT command.

Press F3 (Exit) to exit the Db2 Thread Sort Specification window and return to the Thread Summary panel.
Figure 3. Thread Summary panel
 DGOMTPLS 13:54               Thread Summary   SORT             ROW 1 TO 5 OF 5

 PMO1DLOC           DSN1 V10   GROUP001 MEMBER01

 To display a thread, place any character next to it, then press Enter.

                         Program      Connection         ------- Elapsed -------
   Primauth   Planname   name         ID         Status     Class 1      Class 2
 / USERT003   DSNESPCS   USERT003     TSO        LOCK    2:43.66156   1:11.85677
 _ USERT001   KO2PLAN    USERT001     DB2CALL    APPL    19:06.9123    0.6458763
 _ USERT001   DSNESPRR   USERT001     TSO        APPL    30:10.7424    0.9713783
 _ USERT002   KO2PLAN    USERT002     DB2CALL    DB2     46:25.5787    1.5100349
 _ SYSADM     KO2PLAN    SYSADM       DB2CALL    APPL     37:30.369    1.2797744
 -- End of Thread list --






 Command ===> _________________________________________________________________
  F1=Help      F2=Split     F3=Exit      F5=Auto      F6=History   F7=Up
  F8=Down      F9=Swap     F10=Qualify  F11=Sort     F12=Cancel   F16=Look
 F17=Collect  F19=Left     F20=Right    F22=Purge

At this point, all threads have been sorted in descending sequence by class 3 lock/latch time, although this field is not displayed. You can press the F20 (Right) key to view class 3 times. The word SORT is displayed in the header of the Thread Summary panel.

For example, to view detailed information about the class 3 lock and latch times of a particular thread, type any character next to that thread and press Enter. The Thread Detail panel is displayed.
Note: The Thread Detail panel that you see might have been tailored by your installation and consequently might not necessarily match the panel examples shown in this information.
Figure 4. Thread Detail panel
 DGOMTPSM 13:58                   Thread Detail             PMO1DLOC DSN1 V10
                                    GROUP001 MEMBER01
 For details, place any character next to heading, then press Enter.
                                                                    More:   - +
 _ Thread Identification
   Primauth . . . . . : USERT003         Correlation name  . . . : USERT003
   Planname . . . . . : DSNESPCS         Connection type . . . . : TSO
   Connection ID  . . : TSO              Type  . . . . . . . . . : ALLIED
   Requesting Location: DSNAPC6          Status  . . . . . . . . : LOCK
 _ Current Package      . . . . . . . . . . . : DSNESM68
 / Times                                              Elapsed            CPU
   Class 1  . . . . . . . . . . . . . . . . . :   2:44.363291       0.088226
   Class 2  . . . . . . . . . . . . . . . . . :   1:11.856771       0.041806
   Class 3  . . . . . . . . . . . . . . . . . :   1:04.192452            N/A
   Class 7  . . . . . . . . . . . . . . . . . :           N/P            N/P
   Class 8  . . . . . . . . . . . . . . . . . :           N/P            N/A
 _ Locking Activity
   Timeouts . . . . . . . . . . . . . . . . . :             0
   Deadlocks  . . . . . . . . . . . . . . . . :             0
   Suspensions  . . . . . . . . . . . . . . . :             2
   Lock escalations . . . . . . . . . . . . . :             0
   Maximum page locks held  . . . . . . . . . :             6
 _ Locked Resources
 _ RID List Processing
   Unsuccessful - any reason  . . . . . . . . :             0

 Command ===> _________________________________________________________________
  F1=Help      F2=Split     F3=Exit      F5=Auto      F6=History   F7=Up
  F8=Down      F9=Swap     F12=Cancel   F16=Look     F17=Collect
 F22=Purge
The Thread Detail panel shows that the thread is in LOCK status. Select the 1 field to display the Thread Times window, where you can examine the suspension time shown in the Locks and latches field.
Figure 5. Thread Times window
 DGOMTWTI                        Thread Times

                                                                 More:   - +
                                  Class 1       Class 2
                                  In Appl        In DB2          Outside DB2
  Elapsed time . . . . . . :  2:44.363291   1:11.856771          1:32.106573
  CPU time . . . . . . . . :     0.088226      0.041806             0.046421
    TCB  . . . . . . . . . :     0.088226      0.041806             0.046421
    TCB - Stored Proc  . . :     0.000000      0.000000
    Parallel tasks . . . . :     0.000000      0.000000
  Waiting time . . . . . . :          N/A   1:11.814965
  Suspension time  . . . . :          N/A   1:04.192450
    TCB  . . . . . . . . . :          N/A   1:04.192450
    Parallel tasks . . . . :          N/A      0.000000
  Not accounted  . . . . . :          N/A      7.622515
                                                   Time                Event
  Suspensions (Class 3). . . . . . . . . . : 1:04.19245                    8
     Locks and latches . . . . . . . . . . : 1:04.16683                    3
     Synchronous I/O . . . . . . . . . . . :   0.000000                    0
     Other read I/O  . . . . . . . . . . . :   0.000000                    0
     Other write I/O . . . . . . . . . . . :   0.000000                    0
     Services task switch  . . . . . . . . :   0.025619                    5
     Archive log (quiesce) . . . . . . . . :   0.000000                    0
  Command ===> ___________________________________________________________
   F1=Help     F2=Split    F3=Exit     F5=Auto     F6=History  F7=Up
   F8=Down     F9=Swap    F12=Cancel  F16=Look    F17=Collect F22=Purge

As you can see, most of the total class 3 suspension time is because of locks and latches.

To view the locked resources associated with this thread, press F3 (Exit) to return to the Thread Detail panel and select the Locked Resources field.

The Locked Resources window is displayed.
Figure 6. Locked Resources window
 DGOMTWLD                  Locked Resources                 ROW 1 TO 5 OF 5


 For a list of all the threads that have a lock on a resource, place any
 character next to the resource, then press Enter.

     Database           Type      Status      Duration        Attribute
     Object             Count     Suspended   Function
 /   MACBP2DB           PAGESET   N/A         COMMIT          L-LOCK
     MACBP2TB           2         YES         LOCK
 _   MACBP2DB           PAGESET   IS          COMMIT          L-LOCK
     MACBP2TS           1         NO          N/A
 _   MACBP2DB           DATABASE  S           COMMIT          L-LOCK
     N/A                1         NO          N/A
 _   DSNDB06            SKPT      S           COMMIT          L-LOCK
     SYSUSER            1         NO          N/A
 _   N/A                SKCT      S           PLAN            L-LOCK
     DSNESPCS           1         NO          N/A
 ***************************** BOTTOM OF DATA *****************************

 Command ===> _____________________________________________________________
  F1=Help     F2=Split    F3=Exit     F5=Auto     F6=History   F7=Up
  F8=Down     F9=Swap    F12=Cancel  F16=Look    F17=Collect  F22=Purge

All locks held by the selected thread USERT003 are displayed.

You can see that a suspension has occurred for object MACBP2TB. To view all users contending for the same object, type any character next to that object and press Enter. The Threads Holding Resource window is displayed.
Figure 7. Threads Holding Resource window (Planname)
  DGOMTWRL                 Threads Holding Resource         ROW 1 TO 2 OF 2


  To display a new thread, place any character next to it, then press Enter.
  The Thread Detail will be displayed.

  Database . . . . . . . . : MACBP2DB  Object . . . : MACBP2TB
  Resource type  . . . . . : PAGESET   Resource . . : N/A

      Primauth      Corrname            Status              Subsystem
      Planname      Connection          Suspended           Duration
  _   USERT001      USERT001            S                   N/P
      DSNESPRR      TSO                 NO                  COMMIT
  _   USERT003      USERT003            N/A                 N/P
      DSNESPCS      TSO                 YES                 COMMIT
  ***************************** BOTTOM OF DATA *****************************

  Command ===> ____________________________________________________________
   F1=Help     F2=Split    F3=Exit     F5=Auto     F6=History   F7=Up
   F8=Down     F9=Swap    F12=Cancel  F16=Look    F17=Collect  F22=Purge

All threads involved in the locking of this resource are displayed. From this window, it can be seen that USERT001 is causing USERT003 to be suspended. This problem can be resolved if USERT001 uses the SPUFI cursor stability plan (DSNESPCS), as does USERT003, instead of the SPUFI repeatable read plan (DSNESPRR). You should decide whether it is necessary for USERT001 to use DSNESPRR, or if DSNESPCS can be used instead.