IBM Support

Collecting data: DB2 lock timeouts

Question & Answer


Question

Collecting data for troubleshooting DB2 lock timeout 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 lock holder caused lock timeout and its information.


Things to think about

    - Has the application ever run without problems?
    - Have the lock timeouts started recently?
    - What level of DB2 are you running on?
    - Does the problem only occur at certain times?
    - What has changed on the system? For example, number of users, number of applications,
    amount of data in the tables, fix pack application, etc.
    - What if anything has changed in the application? Isolation level, amount of concurrent
    executions, volume of data, etc?


Tools to enable
    As of v8 fp 16 / v9 fp 4 / v95
    The DB2 Lock Timeout Reporting tool, new for this level of code, is the best tool for collecting information on lock timeout problems. The functionality is enabled through a dynamic registry variable, DB2_CAPTURE_LOCKTIMEOUT. When a lock timeout (SQLCODE -911 reason code 68) is detected, a report is generated that provides details about the lock requester and one representative lock holder, including the statements they were running and other locks they are holding at the same time.
    Full details about lock timeout reporting are available in the documentation at the following link:
    Lock timeout reporting

    As of v97 / v98 / v101 or v105
    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 lock timeout event(SQLCODE -911 RC 68), it is required to set the collection environment, rerun the workload which causes timeout 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 timeout 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 LOCK TIMEOUT DATA WITH HISTORY
     d. UPDATE db cfg for <dbname> using mon_locktimeout 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 LOCKTIMEOUT
            (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 68)
    - Formatted data guided in "Tools to enable"
    - EXPLAIN output (via db2exfmt) for the statements involved in the lock timeout.
    - DDL (i.e., db2look output) for the tables involved in the lock timeout.
    - 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

swg21322231

Page Feedback