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.

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.

• Author Details

Ember Crooks

https://datageek.blog/

ember.crooks@gmail.com

Ember is always curious and thrives on change. She has built internationally recognized expertise in IBM Db2, and is now pivoting to focus on learning MySQL. Ember shares both posts about her core skill set and her journey learning MySQL. Ember lives in Denver and work from home