Managing DB2 LUW Diagnostic Logs

Minor problems stack up , eventually causing the DB2 server to crash.  Monitoring DB2 and responding to messages in the DB2 diagnostic log files is a critical part of the DB2 DBA job role.

DB2 DBAs review the logs after a crash – searching for  clues in root cause analysis. This is OK , but proactive monitoring can save downtime.

You need a system, an effective method to extract the important information from the DB2 diagnostic logs. These approaches will give you some ideas.

My preferred method is to execute a  script daily, extract severe and critical messages using db2diag ( see examples below) , mail these messages and archive the  diaglog.

The amount and type of information is determined by the DIAGLEVEL

1) Shell script to parse dbdiag files . I’ve even seen examples of Powershell  and Shell

2)    PDLOGMSGS_LAST24HOURS administrative view 

SELECT timestamp,instancename ,MSGSEVERITY FROM SYSIBMADM.PDLOGMSGS_LAST24HOURS WHERE MSGSEVERITY = ‘E’

3)     PD_GET_LOG_MSGS table function 

Similar to PDLOGMSGS_LAST24HOURS , but allows returning data older than 24 hrs 

SELECT TIMESTAMP, APPL_ID, DBPARTITIONNUM, MSG 
   FROM TABLE ( PD_GET_LOG_MSGS( CURRENT_TIMESTAMP - 7 DAYS)) AS T 
   WHERE INSTANCENAME = 'MYINST'  
   ORDER BY TIMESTAMP ASC

2)    db2diag utility

Some examples

db2diag -A    –> Archive the diaglog
db2diag –g db=MYDB -> Filter by db name
db2diag -time  –> To retrieve records for a particular time
db2diag -time 2012-02-02 -l severe,error,critical –> To retrieve records for a particular time & filter only server,error,critical
db2diag -l severe –> To return only severe messages
db2diag –merge db2diag.1.log  db2diag.2.log  -level  severe    Merge 2 dlog files filtering on severe 

Read More on Diagnostic Logs and DB2 Tuning

 DIAGLEVEL db2 - DBA DB2

DB2 Tuning Toolkit – db2exfmt - DBA DB2