db2diag Tool for Parsing the Diagnostic Log

Posted by

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.

voila!:

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.

Lead Db2 Database Engineer and Service Delivery Manager , XTIVIA
Ember is always curious and thrives on change. Working in IT provides a lot of that change, but after 17 years developing a top-level expertise on Db2 for mid-range servers and more than 7 years blogging about it, Ember is hungry for new challenges and looks to expand her skill set to the Data Engineering role for Data Science. With in-depth SQL and RDBMS knowledge, Ember shares both posts about her core skill set and her journey into Data Science. Ember lives in Denver and work from home for XTIVIA, leading a team of Db2 DBAs.

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.