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 DBA role 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 dialog.

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