db2diag Tool for Parsing the Diagnostic Log
Understanding the db2 diagnostic log takes a while when you’re new to DB2. Then you get better at spotting what is actually a problem. But there’s still so much there sometimes. How many messages for stmm adjustments or archived log files do I have to scroll through to find what I’m really looking for?
This week I had an instance with approximately 12 databases, and noticed some lock escalations in the diag log. So I wanted to get an idea of when and what database I was seeing the escalations on so I could then decide what to do about them.
db2diag -g message:=scalation -fmt '%ts %db'
and I get a list of the timestamps and databases where/when lock escalation was seen.
I haven’t been a heavy user of the db2diag tool. I do use it in my scripts for archiving the db2diagnostic log once a month, but since I have to manage other files like the notify log and dump files, it’s not much of a time saver there.
I used to work mostly on AIX, and when there, would use grep -p with some success on the db2diag.log, though that was a tool I’ve been using since before version 8.
So I’m not going to post in the whole syntax diagram here. It’s available at http://www.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.admin.cmd.doc/doc/r0011728.html
I thought I’d share a couple of the most useful examples.
To display a list of error and severe messages(eliminating info and event):
db2diag -level error,severe
To display a list of error and severe messages from the last 30 days:
db2diag -level error,severe -H 30d
Find all insurances of SQL0911 in the last 30 days (note this also returned some log file archives with the number 911 in them – interesting insight into how basic this tool is behind the scenes):
db2diag -e 911 -H 30d
From a script, Archive the diag log with a date stamp in the title (replace <DIAGPATH> with the value of DIAGPATH):
db2diag <DIAGPATH>/db2diag.log -readfile -A
I haven’t actually used it, but I’m intrigued by the -f option – I might use it sometime when I want to watch the diag log scrolling but want to be able to filter some things out.