Question & Answer
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.
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
- - 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.
What to do next
- Once you have collected the preceding information, you can begin analyzing the data, or simply submit the diagnostic information to IBM support.
Submitting diagnostic information to IBM Technical Support for problem determination
16 June 2018