Daily Archives: January 6, 2010

DB2 Snippets

Today a DBA at one of our customers reported deadlocks and wanted recommendations.  I usually start with the SQLs involved in the deadlock for which I need the deadlock event monitor output.  Fortunately, all modern versions of DB2 have the db2detaildeadlock event monitor installed and one just needs to run db2evmon to get it to talk.  This DBA wasn’t clear on how to provide this so I copied and pasted some simple instructions from my file of “common DB2 snippets”:

Getting deadlock event output – Windows

Open a DB2 Command Window (db2cmd)
cd to the “Default database path”.  If not the default instance path, you can determine this by running: db2 get dbm cfg | find “DFTDBPATH”
Run: dir db2detaildeadlock /s
Look for paths with recent timestamps
For each of these paths, cd to that path and run the following.  Change the output filename each time; for example: dl1.txt, dl2.txt, dl3.txt…
db2evmon -path . > dl1.txt
Send each of the dl*.txt files

I can then browse and grep the dl*.txt files for SQLs and other information.

I thought I’d paste in some others, if for no other reason than ease of reference (“send it off in a letter to yourself”).  That is, we have many of these in our products’ scripts and utilities, but often a DBA just wants easy access to a quick script.  Some of the other commonly-sent snippets are:

Gather snapshots remotely – Windows

: Check if DBM-level snapshot monitors are on:db2 get dbm cfg | find “DFT”
: Turn on those that are off, restart DB2 afterward:
db2 update dbm cfg using dft_mon_bufpool on dft_mon_table on dft_mon_uow on
db2 update dbm cfg using dft_mon_sort on dft_mon_stmt on dft_mon_lock on
db2stop & db2start
: Collect snapshots:
echo Current snapshots for %date% – %time%. >>%SnapshotFilename%
db2 get snapshot for db manager >>%SnapshotFilename%

db2 get snapshot for all on %DbName% >>%SnapshotFilename%

Collect DDL and mimic statistics (to do explains remotely):

db2look -d %DbName% -a -o db2look-%DbName%.ddl -e -l -m -f -x 2>&1

Get explain output (for remote analysis):

db2expln -g -i -d %DbName% -o explain1.txt -q “%SqlText%”

Get more detailed explain output (for remote analysis):

db2 connect to %DbName%
db2 set schema %DbSchema%
db2 “explain all with snapshot set querytag = ‘DWEXPLN’ for %SqlText%”
db2exfmt -d %DbName% -n % -s % -# 0 -w -1 -o explain1.txt