Analyzing timeouts and deadlocks

To analyze timeouts and deadlocks

1. On the EZDLOCK menu, hyperlink from Lockout Events by Time (for one DB2).A list of timeouts and deadlocks is displayed.

   10MAR2011  17:20:11 --------MainView WINDOW INTERFACE (V6.0.00) ---------------
   COMMAND  ===>                                                 SCROLL ===> CSR
   CURR WIN ===> 1        ALT WIN ===>
   >W1=LKEVENT===========DB2HC====*========10MAR2011==17:20:10====MVDB2====D====5
   Date / Time        Lockout   Victim   Victim   Victim   Victim Victim       Nr
    ----------------- Type      Plan     AuthID   Connect  CType  Corr ID      Re
    17SEP-16:58:56.41 DEADLOCK  AUDIT    BBLLAT5  TSO      TSO    BBLLAT5
    17SEP-16:58:45.18 TIMEOUT   PAYROLL  DMRDLK3  BATCH    TSO    DMRDLK3
    17SEP-16:42:34.18 TIMEOUT   PAYROLL  DMRTMO3  BATCH    TSO    DMRTMO3
    17SEP-16:37:33.20 DEADLOCK  AUDIT    BBLLAT5  TSO      TSO    BBLLAT5

   This view shows a chronological list of lockout events that have occurred since DB2 startup, sorted with the most current at the top of the screen. The most recent events are retrieved from an online buffer that holds a default of 100 events.

2. Type SORT on the COMMAND line and press the Tab key to move to the Victim Plan column. In the Victim Plan column, sort these lockout events by plan name to identify the applications for which SQL requests were terminated.
3. Hyperlink on the Date/Time field for an event (preferably a deadlock that involves multiple resources).The first view shows the resources involved and the holder (blocker) and waiter plans.
4. Scroll right to see additional details for both resources at once, or hyperlink on the Res Seq field to see all of the available details for this conflict.
5. Press F3 until you return to EZDLOCK.

6. Hyperlink on Resource Cont. Summary.A list of resources involved in timeouts or deadlocks is displayed.

   10MAR2011  17:21:09 --------MainView WINDOW INTERFACE (V6.0.00) ---------------
   COMMAND  ===>                                                 SCROLL ===> CSR
   CURR WIN ===> 1        ALT WIN ===>
   >W1 =LKRESZ============DB2HC====*========10MAR2011==17:21:09====MVDB2====D====2
    --Resource Name--  Total           % Total                         Global
    Database Object    Conflicts       0...50..100   PAGE   ROW INDEX  Conflicts
    DSN8D51A DSN8S51E          5  71.4 ********         5     0     0          2
    DSN8D51A DSN8S51P          2  28.6 ***              0     2     0          2

   The first view, LKRESZ, summarizes all conflicts by resource name, usually database and table space. A deadlock with three resources and participants will result in three conflicts, not one, for the purposes of this analysis. With this view, you can easily identify those table spaces involved in the most contention.

7. Hyperlink on a resource name that shows one or more conflicts to see a breakdown of these conflicts by specific resource, down to a page or row level (LKRESNRZ).With this view, hot spots in your tables are immediately visible.

   10MAR2011  17:22:35 --------MainView WINDOW INTERFACE (V6.0.00) ---------------
   COMMAND  ===>                                                 SCROLL ===> CSR
   CURR WIN ===> 1        ALT WIN ===>
   >W1 =LKRESZ===LKRESNRZ=DB2HC====*========10MAR2011==17:22:35====MVDB2====D====1
    --Resource Name-- Resource   Resource  Total           % Total     Global
    Database Object   Number     Type      Conflicts       0...50..100 Conflicts
    DSN8D51A DSN8S51E 0000001200 DATAPAGE          5  71.4 ********            2

8. Hyperlink on either the resource name or the resource number to view a list of each lockout event that involved this resource (LKRESD).The events are initially sorted in descending sequence by time, but you can use the SORT command to sort by any column. (To view the online Help, type HELP SORT on the COMMAND line.) Controlling the sort order allows you to identify quickly the applications (blocker planname, waiter planname) and users (blocker/waiter Corr ID, Connection, Victim Auth ID) who are involved.

   10MAR2011  17:24:34 --------MainView WINDOW INTERFACE (V6.0.00) ---------------
   COMMAND  ===>                                                 SCROLL ===> CSR
   CURR WIN ===> 1        ALT WIN ===>
   >W1 =LKRESZ===LKRESD===DB2HC====*========10MAR2011==17:24:34====MVDB2====D====5
    --Resource Name-- Resource   Resource   Time   Lockout  Blocker  Waiter   Gbl
    Database Object   Number     Type     -------- Type     PlanName PlanName Con
    DSN8D51A DSN8S51E 0000001200 DATAPAGE 16:58:56 DEADLOCK PAYROLL  AUDIT    Yes
    DSN8D51A DSN8S51E 0000001200 DATAPAGE 16:58:45 TIMEOUT  AUDIT    PAYROLL
    DSN8D51A DSN8S51E 0000001200 DATAPAGE 16:42:34 TIMEOUT  AUDIT    PAYROLL
    DSN8D51A DSN8S51E 0000001200 DATAPAGE 16:37:33 DEADLOCK PAYROLL  AUDIT    Yes
    DSN8D51A DSN8S51E 0000001200 DATAPAGE 16:36:53 TIMEOUT  RXDB2    RXDB2

   Although the solution to locking problems might involve application or table redesign, at least you now know where the problems lie.

9. Press F3 until you return to EZDLOCK.

10. Hyperlink on Conntype Cont. Summary if you suspect that the problem might lie in application scheduling, where applications with incompatible lock usage are running concurrently.

    10MAR2011  17:38:42 --------MainView WINDOW INTERFACE (V6.0.00) ---------------
    COMMAND  ===>                                                 SCROLL ===> CSR
    CURR WIN ===> 1        ALT WIN ===>
     W1 =LKCONZ============DB2HC====*========10MAR2011==17:38:41====MVDB2====D====1
     DB2      Victim    Victim   Blocker   Timeouts Deadlocks        % Lockouts
     Target   ConnType  Connect  Connect                             0....50..100
     DB2HC    TSO       TSO      BATCH            0         2   40.0 *****
     DB2HC    BATCH     BATCH    TSO              2         0   40.0 *****
     DB2HC    CAF       DB2CALL  CAF              1         0   20.0 ***

    The view LKCONZ summarizes the conflict data to identify, for example, whether batch jobs or utilities are blocking critical CICS or IMS transactions. As in the resource summaries, hyperlinks lead to lists of the exact events with time stamps, so that the critical time periods can be seen at a glance.

11. Press F3 until you return to EZDLOCK.

12. Hyperlink on Blocker/Waiter Summary to identify incompatible applications that frequently cause blocking.

    10MAR2011  17:40:24 --------MainView WINDOW INTERFACE (V6.0.00) ---------------
    COMMAND  ===>                                                 SCROLL ===> CSR
    CURR WIN ===> 1        ALT WIN ===>
    >W1 =LKBWZ=============DB2HC====*========10MAR2011==17:40:24====MVDB2====D====1
     Blocker  Waiter    Timeout Deadlock        % Conflicts    Global  Participant
     PlanName PlanName  Involv.  Invovl.   .... 0....50...100  Confl.  -Only Count
     AUDIT    PAYROLL         2        0   40.0 *****               0            0
     PAYROLL  AUDIT           0        2   40.0 *****               0            0
     RXDB2    RXDB2           1        0   20.0 ***                 0            0

    The view LKBWZ summarizes the conflict data by blocker and waiter plans, so that you can quickly see which applications are causing the most conflicts. The hyperlinks again show all of the lockout events where the selected plan was involved.

13. Press F3 until you return to EZDB2.

    Tip

    If you want to print a lockout report, use one of the views designed for this purpose. The views LKPRINT, LKPR133, and LKPR133L combine the data from LKEVENT with the resource information from LKEVRES. For more information about the lock views, see Application-analysis-views.

    For information about printing views, see Printing-views-and-reports.