Performance PD techniques - sniffing into the database

Sometimes performance problem determination can be assisted by tracing what is going on in the process engine database.
Usually database managers offer very helpful facilities to assist here in a least intrusive way.

In case you don't have your DBA available (or don't have one - or have enough access rights yourself) you can e.g. start monitoring SQL statements or deadlocks.

An SQL statement event monitor can be started (here with DB2 on AIX) with the following sequence of commands.

db2 connect to your-bpedb-name-here
db2 "create event monitor statmnts for statements write to file '/tmp/DB2_smon'"
---- (note: this path (directory) should exist)
db2 set event monitor statmnts state 1
db2 flush event monitor statmnts
---- now perform whatever scenario you want to do the tracing for
db2 set event monitor statmnts state 0
db2 flush event monitor statmnts
db2evmon -path '/tmp/DB2_smon' > '/tmp/DB2_smon/sqltrace.txt'
db2 connect reset


Similarly an event monitor for deadlocks can be set up:
db2 connect to your-bpedb-name-here
db2 "create event monitor dedlk for deadlocks with details history values write to file '/tmp/DB2_dlock'"
---- (note: this path (directory) should exist)
db2 set event monitor dedlk state 1
db2 flush event monitor dedlk
db2 set event monitor dedlk state 0
db2 flush event monitor dedlk
db2evmon -path '/tmp/DB2_dlock' > '/tmp/DB2_dlock/sqldeadlocks.txt'
db2 connect reset