DB2 LUW DBA HowTo

Ever since version 8 DB2 has shipped with a default event monitor, active at startup, called DB2DETAILDEADLOCK. I sometimes drop the event monitor when installing a database, because I would rather create an event monitor when I have reason to believe deadlocks are occurring due to application timeouts (SQLCODE -911) (assuming the the database is configured with finite locking timeout value (DB CFG LOCKTIMEOUT > 0). My next step would be to check the database snapshot Deadlocks Detected monitor element. If Deadlocks Detected was being incremented, then I would create and activate a deadlock event monitor for more detailed investigation.

I believe this CREATE statement will generate an monitor identical in behavior to the DB2 system DB2DETAILDEADLOCK:

create event monitor DB2DETAILDEADLOCK for deadlocks with details
write to file 'db2detaildeadlock'
maxfiles 20 MAXFILESIZE 512 BUFFERSIZE  17
blocked append
autostart
on dbpartitionnum 0 global;

This, like the default system DB2DETAILDEADLOCK event monitor, will place event files data in directory

<Database path>/db2event/db2detaildeadlock

(all CREATE EVENT MONITOR statements which write to file and which specify a relative path, append to the root directory

<Database path>/db2event

)

Because deadlocks are an issue in the database I am currently managing, I did want a deadlock event monitor active at all times; but I don’t want event files written to the database path filesystem, and wanted a larger buffersize. This is the CREATE statement, presented as an example of what you can do in your own database:

create event monitor MYDETAILDEADLOCK for deadlocks with details
write to file '/db2/support/evmon/mydetaildeadlock'
maxfiles none MAXFILESIZE 512 BUFFERSIZE  300
blocked append
autostart
on dbpartitionnum 0 global;