Remind myself to look into this IBM technote for Recommended AIX Virtual Memory Manager settings for DB2. It is always a question of who is going to dig into each others internals, but it really can’t hurt to build some AIX muscle.

 

deadlock detective

From time to time we encounter the dreaded deadlock situation in production, and of course the developers scratch their head and wonder why this never turned up in their tests, and wonder what magic the dba can wield to untie the knots, or at least find out what the *¤% is going on.
Although one might wonder sometimes where IBM is going with their overly pessimistic locking model, I can to some degree appreciate that it is better to be safe than sorry, and with good and consistent coding you will avoid getting entangled in deadlocks.
Anyway, IBM has become very generous in supplying interfaces to the overwhelming amount of internal metrics of db2, and I will most probably return to variations on that subject many times in the future. This time, it is all about the deadlock event monitor.

connect to db mydldb;
create event monitor dlmon1 for deadlocks with details history
write to file dlmon1 maxfiles 20 maxfilesize 4000 buffersize 1000
nonblocked replace manualstart;
set event monitor dlmon1 state 1;

The above creates a deadlock monitor for the database and activates it. The binary trace data is written in case of a deadlock to the directory [DBPATH]/db2evmon/dlmon1/, where [DBPATH] is the database directory path, which you can deduce by reading the output from list active databases. The trace data produced can be parsed into neatly shaped reports simply by running the command

db2evmon -db mydldb -evm dlmon1

Now you can impress your developers with details, not only on the connections involved in the deadlock situation, but also on the table over which the connections are fighting. You are also presented with a list of active locks held by the transaction, and in case of dynamic sql, the actual sql statement text of both transactions. But the real treat comes from the history keyword. This produces in clear text the entire statement history of each of the involved transactions. Line the sequences of statements up beside each other, and you will hopefully get a pretty clear picture of what is going on. Still confused? Then you can even get the actual values from the involved statements and drill down to the actual rows involved in the lock by adding the keyword values to the create event monitor statement.

Now for a simple real life example: