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.

db2diag syntax

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.

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *