How to monitor a deadlock in DB2

Question

I am following this link and try to simulate the deadlock issue:

http://www.dba-db2.com/2012/06/how-to-monitor-a-deadlock-in-db2.html

I can see my command run successful.

After that I go to simulate a deadlock error through DbVisualiser tool. However I didnt see any file being generated to the path.

Can someone point the mistake to me?

And also, I try to read back those old 0000000.evt file, it show me something as follow:

                            EVENT LOG HEADER
  Event Monitor name: DB2DETAILDEADLOCK
  Server Product ID: SQL10059
  Version of event monitor data: 12
  Byte order: BIG ENDIAN
  Number of nodes in db2 instance: 1
  Codepage of database: 1208
  Territory code of database: 1
  Server instance name: db2inst1
--------------------------------------------------------------------------

--------------------------------------------------------------------------
  Database Name: MYDB   
  Database Path: /db2home/db2inst1/NODE0000/SQL00003/MEMBER0000/
  First connection timestamp: 01/29/2018 10:00:17.694784
  Event Monitor Start time:   01/29/2018 10:00:18.951331
--------------------------------------------------------------------------

--------------------------------------------------------------------------
  Database Name: MYDB   
  Database Path: /db2home/db2inst1/NODE0000/SQL00003/MEMBER0000/
  First connection timestamp: 01/29/2018 10:12:54.382936
  Event Monitor Start time:   01/29/2018 10:12:54.697223
--------------------------------------------------------------------------

This means no deadlock?

Answer 1

Works correctly for me (linux, Db2 v11.1). Here are some command lines with annotations. You need to have suitable authorisation/privilege for each command. I was using the instance owner account.

Disable default db2detaildeadlock monitor first and then create your own:

$ db2 "set event monitor db2detaildeadlock state=0"      
DB20000I  The SQL command completed successfully.
$ 
$ db2 "create event monitor dlmon for deadlocks write to file '/tmp'"
DB20000I  The SQL command completed successfully.
$
$ db2 "set event monitor dlmon state=1"
DB20000I  The SQL command completed successfully.
$

Generate a deadlock, ensure you see this SQLCODE -911 with reason code 2. If you dont' see the reason code 2 then you don't have any deadlock but you might have a timeout and timeouts don't get recorded in the deadlock monitor.

Here I show the victim of the deadlock getting notified of rollback and you can see the correct reason code:

$ db2 +c "select * from db2inst1.dlk where a=4 with rr"
SQL0911N  The current transaction has been rolled back because of a deadlock 
or timeout.  Reason code "2".  SQLSTATE=40001

Investigate the monitor output with db2evmon and view resulting file

$ db2evmon -db mydb -evm dlmon > /tmp/db2evmon.dlmon.1

Reading /tmp/00000000.evt ...

$ view /tmp/db2evmon.dlmon.1
...<snip>
...
3) Deadlock Event ...
  Deadlock ID:   2
  Number of applications deadlocked: 2
  Deadlock detection time: 01/03/2018 09:06:39.019854
  Rolled back Appl participant no: 2
  Rolled back Appl Id: *LOCAL.db2inst1.180301090546
  Rolled back Appl seq number: 00001
  Rolled back Appl handle: 11872

...<snip>