How to troubleshoot DB2 lock waits

Monitoring lock waits is a great way of measuring query performance slowdown. Lock waits are a normal part query processing but when the lock waits start taking longer than normal , it’s a sign of trouble. According to the DB2 LUW documentation a lock wait is defined as “A lock wait occurs when one transaction (composed of one or more SQL statements) tries to acquire a lock whose mode conflicts with a lock held by another transaction” Common symptoms of lock waits taking longer than normal are: Applications are not completing tasks SQL query performance slowdown Lock escalations. A small... Read more →

How to read db2detaileventlock event monitor trace file

Question: When I try to view the *.evt file in $DBHOME/db2event/db2detaildeadlock it looks like binary. I’d like to be able to read the details in human readable format. Is there a DB2 utility to format the event monitor trace file data? Answer: Yes, there is a DB2 utility called db2evmon. This utility formats the even monitor file. Here is a an example of how to configure and execute the db2evmon event monitor utility.In this example I'm viewing the file using the vi utility. I normally use vi to read text files on Linux. If reading the output file on Windows... Read more →

DB2 - Drop the DB2DETAILDEADLOCK event monitor

DB2DETAILDEADLOCK is created and activated by default when a database is created. When an activity activates the database the monitor starts up. When a deadlock occurs the diagnostic information is collected. There are circumstances where you need to either deactivate or DROP the monitor. For example , there may be resource constraints Deactivate the the event monitor db2 "set event monitor DB2DETAILDEADLOCK state 0" Drop the event monitor db2 "drop event monitor DB2DETAILDEADLOCK" Related Posts SQL0911N Reason Code 68 The current transaction has been rolled back because of a deadlock or timeout How to monitor a deadlock in DB2 Read more →

How to fix DIA8052C the Event Monitor has reached its file capacity

Question: This DB2 error message appears in the DB2Diag.LOG file ZRC=0x800D002B=-2146631637=SQLM_RC_EVFULL "monitor full of data" DIA8052C The Event Monitor "" has reached its file capacity. Delete the files in the target directory "" or move them to another directory. How can I fix it? Answer: There’s probably a limit on the event monitor size. To check the current size – connect to the database and issue the statement: SELECT * FROM SYSCAT.EVENTMONITORS Find the event monitor name under the EVMONNAME column. The main two options are to 1) Delete or move the event files in the target directory 2) Recreate... Read more →

How to monitor a deadlock in DB2

Question: How can I trace and capture DB2 database deadlock details? I then want to view the deadlock details. Answer: Capturing DB2 deadlock details is straightforward. Use a similar process to below. Basically, you create an Event Monitor , capture some activity into a file, drop the event monitor. #connect to db #1-Create event monitor- make sure you've got write permissions db2 "CREATE EVENT MONITOR dlmon FOR DEADLOCKS WRITE TO FILE '/tmp'" #2 Turn on event monitordb2 SET EVENT MONITOR dlmon STATE = 1 #3. (Do stuff to the database that you want to monitor) #4. Turn off event monitordb2... Read more →