IBM Support

Collecting data: DB2 Deadlocks

Question & Answer


Question

Collecting data for troubleshooting DB2 deadlock problems. Collecting this information before calling IBM support will help you understand the problem and save time analyzing the data.

Answer

This document lists things to think about before getting the data, available tools to enable and data which should be collected for analyzing. Answering questions and analyzing the data reveals which applications are under deadlock situation and their information.


Things to think about

    - Have the deadlocks recently started occurring?
    - Did this application run before without problems?
    - What changes have been made to the system? For example, more users, more data,
    new applications, fix pack application, etc.

Tools to enable
    Prior to version 9.7, the tool for deadlock issue was a deadlock event monitor, which must be defined and enabled before the problem occurs. For default deadlock event monitor, please see the following knowledge center topic:
    Deprecated lock monitoring functionality

    From version v97 to v105, the new approaches are introduced by CREATE EVENT MONITOR FOR LOCKING. This event monitor will collect the locking-related such as lock wait, lock timeout and deadlocks. To acquire information of deadlock event(SQLCODE -911 RC 2), it is required to set the collection environment, rerun the workload which causes deadlock and then format the data to readable form.

    1. set collection environment
     - After finishing the following steps, data will be piled up in the event monitor table. The workload in which the deadlock occurs should be known before this setting.
     a. CREATE EVENT MONITOR <evmonname> FOR LOCKING WRITE TO UNFORMATTED EVENT TABLE
     b. SET EVENT MONITOR <evmonname> STATE 1
     c. ALTER WORKLOAD <workloadname> COLLECT DEADLOCK DATA WITH HISTORY
     d. update db cfg for <dbname> using mon_deadlock hist_and_values

    2. rerun the timeout workload which causes timeout

    3. format data to readable form
     - To format data, one of the following three tools can be used.
     * db2evmonfmt tool for reading event monitor data
        Shortcut guide is available like as follows
        a. locate to $HOME/sqllib/samples/java/jdbc
        b. compile the source code : $HOME/sqllib/java/jdk64/bin/javac db2evmonfmt.java
        c. run the tool : $HOME/sqllib/java/jdk64/bin/java db2evmonfmt -d <dbname> 
    -ue <unformatted evmon table name> -ftext -hours 1 -type DEADLOCK
            (For db2jcct error, export LIBPATH=$HOME/sqllib/lib64:$LIBPATH) 

    * EVMON_FORMAT_UE_TO_TABLES procedure
    * EVMON_FORMAT_UE_TO_XML table function
       
    For more detail and restriction in each step, please refer to the following link(for v105)
    Collecting lock event data and generating reports
Data to collect
    - Error message result (SQLCODE -911 RC 2)
    - Formatted data guided in "Tools to enable"
    - EXPLAIN output (via db2exfmt) for the statements involved in the deadlock.
    - DDL (i.e., db2look output) for the tables involved in the deadlock.
    - db2support


What to do next

[{"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Component":"Performance - Lock-Latch","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF010","label":"HP-UX"},{"code":"PF016","label":"Linux"},{"code":"PF027","label":"Solaris"},{"code":"PF033","label":"Windows"}],"Version":"9.8;9.7;10.1;10.5","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
16 June 2018

UID

swg21321787