Question & Answer
Question
Collecting data for DB2 locking issues such as database deadlocks, timeouts, and slow queries due to lockwait. Gathering this information before calling IBM support will help reduce the time needed to diagnose your problem, and is a useful template for performing self-diagnosis of locking issues.
Cause
Database locking issues (deadlock, lock timeout, query performance issues due to lockwait) can occur When an application concurrently accesses the same data in multiple transactions, during retrieval, insertion, update, and deletion operations.
This can occur more frequently under heavy load (due to the greater chance of contention by two queries for the same resource) or in a particular scenario. The degree of concurrency contorol (isolation level) used by the application, may also come into play.
An application which receives a deadlock or lock timeout will return an SQLCODE of -911 (SQL0911N), with reason code 2 (deadlock) or 68 (lock timeout).
For further information on Locking issues and their diagnosis, please refer first to the following articles
Diagnose and resolve lock problems with DB2 for Linux, UNIX, and Windows
As well, consider the following when investigating application-related database locking issues with DB2:
1) Perform regular database maintenance, such as updating the table and index statistics (reorg, runstats).
2) Upgrading software (DB2, Application Server) to the latest service level to take advantage of the most up-to-date performance enhancements and fixes.
3) Conducting a performance tuning assessment. Your product vendor may have specific documentation for benchmarking system performance.
The DB2 Information Center contains a section on application benchmarking. Please see the section 'Benchmark Testing'.
This can occur more frequently under heavy load (due to the greater chance of contention by two queries for the same resource) or in a particular scenario. The degree of concurrency contorol (isolation level) used by the application, may also come into play.
An application which receives a deadlock or lock timeout will return an SQLCODE of -911 (SQL0911N), with reason code 2 (deadlock) or 68 (lock timeout).
For further information on Locking issues and their diagnosis, please refer first to the following articles
Diagnose and resolve lock problems with DB2 for Linux, UNIX, and Windows
As well, consider the following when investigating application-related database locking issues with DB2:
1) Perform regular database maintenance, such as updating the table and index statistics (reorg, runstats).
2) Upgrading software (DB2, Application Server) to the latest service level to take advantage of the most up-to-date performance enhancements and fixes.
3) Conducting a performance tuning assessment. Your product vendor may have specific documentation for benchmarking system performance.
The DB2 Information Center contains a section on application benchmarking. Please see the section 'Benchmark Testing'.
Answer
The following instructions will describe how to prepare your environment for troubleshooting, collect the necessary data, and gather the required information for IBM support.
Part A: Configure environment for data collection
Follow the instructions below to set up the problematic system for capturing the required information:
1. Enable event monitors
Deadlock monitoring
Enabling event monitors to capture information about deadlock( SQL0911N Reason code 2 ), as well as statements and transactions (for lock timeout, SQL0911N reason code 68) is extremely useful in narrowing down the source of locking issues, if the problem is recreateable or occurs intermittently.
If deadlocks are being experience, create a monitor for deadlocks to capture the problem queries. The impact on performance by enabling this monitor is negligible, so it is recommended that you enable these monitors and leave them enabled until the problem reoccurs.
Complete the following steps to enable an event monitor for deadlocks:
1. Create a temporary directory where the event monitor files will be created
mkdir tempfolder/deadlock
2. Connect to the database
db2 connect to <database-name>
3. Enable timestamp monitor switch to collect start/end and execution times:
db2 update monitor switches using timestamp on
4. Create the event monitor using the following command:
db2 "create event monitor mon_dl for deadlocks with details write to file '<tempfolder>/deadlock'"
5. Enable the event monitor:
db2 "set event monitor mon_dl STATE=1"
Lock timeout monitoring
If lock timeouts are being experienced, use a statement event monitor to capture the query or queries that are being affected. A statement event monitor will log all the statements being run on the database. As there will be performance degradation, you should only enable this monitor on a production system while the database is experiencing timeout problems or when requested by the support analyst. You should disable the event monitor as soon as the data concerning the problem has been collected.
Also note that as all the statements are logged, the size of the file will grow rapidly. Depending on the number of statements, on a typical production database, the file can grow at a pace of one gigabyte every ten minutes. Ensure that there is enough space on the file system to contain the event monitor. Alternatively, the File Options of the CREATE EVENT MONITOR Statement can be used to specify a maximum file size.
Complete the following steps to enable an event monitor for statements:
1. Create a temporary directory where the event monitor files will be created:
mkdir tempfolder/timeout
2. Connect to the database
db2 connect to <database-name>
3. Enable timestamp monitor switch to collect start/end and execution times:
db2 update monitor switches using timestamp on
4. Create the event monitor using the following command:
db2 "create event monitor mon_timeout for statements write to file '<tempfolder>/timeout'"
5. Enable the event monitor using the following command:
db2 "set event monitor mon_timeout STATE=1"
For more information on creating an event monitor, consult the following page of the DB2 Information Center:
CREATE EVENT MONITOR Statement:
https://www.ibm.com/support/knowledgecenter/en/SSEPGG_10.5.0/com.ibm.db2.luw.sql.ref.doc/doc/r0000915.html
2. Using db2pd to enable db2cos data collection on SQLCODE -911
DB2 ships a db2 callout script (db2cos) which will generate a report when a specified error code is encountered.
The command used to enable db2cos collection is:
db2pdcfg -catch [-911 | deadlock | timeout]
For details on usage, see:
Monitoring and troubleshooting using db2pd
Part B: Reproduce the problem
The environment is now configured to capture the necessary data when the problem occurs.
While the database is experiencing problems, take a series of snapshots by entering the following commands:
1. Connect to the database
db2 connect to dbname
2. Enable monitor switches:
db2 update monitor switches using lock on timestamp on statement on bufferpool on uow on sort on table on
3. Reset monitor switches
db2 reset monitor all
4. Wait 3 minutes while the database collects the data
5. Obtain a snapshot:
db2 get snapshot for all on dbname > db2snap.yyyymmddhhmm.txt
(where 'yyyymmddhhmm' is the current date and time)
6. Reset the monitor switches to reinitialize the counters once again:
db2 reset monitor all
7. Wait 3 minutes
8. Repeat steps 5-7 three times.
9. When done, disable the monitor switches
db2 update monitor switches using lock off statement off bufferpool off uow off sort off table off
Always ensure to wait at least 3 minutes from the time you reset the counters to the time you get the snapshots.
After capturing the error, the event monitor needs to be disabled and the output formatted to a file:
1. Disable the event monitor using the following command:
db2 "set event monitor wctimeout STATE=0"
2. Format the event monitor logs using the following command:
db2evmon -db dbname -evm wctimeout > tempfolder/evtimeout.txt
3. Drop the event monitor:
db2 drop event monitor wctimeout
Part C: Collect all necessary data
After capturing the error, the deadlock event monitor needs to be disabled and the output formatted to a file:
1. Connect to the database
db2 connect to <database-name>
2. Disable the event monitor(s) using the following command:
db2 "set event monitor mon_dl STATE=0"
db2 "set event monitor mon_timeout STATE=0"
3. Format the event monitor logs using the following command:
db2evmon -db <database-name> -evm mon_dl > <tempfolder>/mon_dl.txt
db2evmon -db <database-name> -evm mon_timeout > <tempfolder>/mon_timeout.txt
4. Disable the db2pd catch option
db2pdcfg -catch clear all
5. Drop the event monitors (optional)
db2 drop event monitor mon_dl
db2 drop event monitor mon_timeout
Connect to the database and issue the following commands:
1. db2 -z reorgchk.txt "reorgchk current statistics on table all"
2. db2 -z tblspaces.txt list tablespaces show detail
3. db2support <output_directory> -d database_name -cl 0
The last command generates db2support.zip, which collects the DB2 diagnostic files, including the db2cos report file(s), system information, and information on the database.
Part D: Submit files to IBM Support
Include the following information in the package you provide to IBM:
1. db2support output
db2support.zip
2. DB2 Snapshot Files
db2snap.yyyymmddhhmm.txt
3. DB2 Event Monitor Files
mon_dl.txt
mon_timeout.txt
4. Application Server Logs / Mustgather
See your product documentation regarding the appropriate docs to collect for a database deadlock/timeout situation. In some cases, traces may be recommended. If this is the case, be sure to enable the traces before problem recreation.
5. Submit data
Once IBM has been contacted to open a Case, submit files to IBM per the guidelines outlined at the URL:
https://www.ibm.com/support/pages/enhanced-customer-data-repository-ecurep-send-data-ftp
Related Information
[{"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Component":"Performance - Locks","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF010","label":"HP-UX"},{"code":"PF016","label":"Linux"},{"code":"PF027","label":"Solaris"},{"code":"PF033","label":"Windows"}],"Version":"9.7;9.5;9.1;10.1;10.5","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]
Was this topic helpful?
Document Information
Modified date:
10 November 2020
UID
swg21329152