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

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.

DROP EVENT MONITOR <monitor_name>

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 permissionsdb2 "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 SET EVENT MONITOR dlmon STATE = 0#5. Translate event monitor into readable stuffdb2evmon -path /tmp > /tmp/filtered.out#6. Read the eventsvi /tmp/filtered.out#7. db2 DROP EVENT MONITOR dlmon 

 

 Read More

SQL0911N Reason Code 68 The current transaction has been rolled back because of a deadlock or timeout

DB2 - Drop the DB2DETAILDEADLOCK event monitor

SQL0911N Reason Code 68 The current transaction has been rolled back because of a deadlock or timeout

Question : I am seeing this SQL0911N Reason Code 68 message in my DB2 diaglog file:

command : db2 backup db MYDB online use TSM include logs
SQL0911N  The current transaction has been rolled back because of a deadlock
or timeout.  Reason code "68".  SQLSTATE=40001

 

How do I fix this SQL0911N error?

 

Answer: The DB2  command line error utility  shows this detail for  the SQL0911N Reason Code 68 error., indicating that the SQL0911N error is caused by a deadlock or locktimeout.

A DB2  ONLINE backup attempts to obtain a IN lock on tables in SMS table spaces. DB2 tries to upgrade BLOB fileds to an S LOCK during an ONLINE backup.

 If an application is holding a lock incompatible with the ONLINE backup lock attempt, The ONLINE BACKUP waits for the period of the LOCKTIMEOUT ,  where the ONLINE backup fails.

Possible solutions

1)       Analyse scheduled backup times and move to a quieter time frame

2)       Change table space to DMS from SMS. DMS doesn’t require the SHARE lock for BLOBs

3)       Force the application off.

4)       Increase LOCKTIMEOUT

Read More

How to monitor a deadlock in DB2 - DBA DB2

DB2 - Drop the DB2DETAILDEADLOCK event monitor