db2diag and db2dart – 2 life saver utilities

Monitoring diagnostic log is very important task in a DBA`s daily life. When you compare this with onset of a chronic disease like heart attack or cancer you find that they are analogies. For these chronic diseases you will see symptoms like pain, weight loss and other related symptoms before the disease actually reaches serious stage. Similarly for a serious database issue you will have related records as a precursor in diaglog most of the time. So carefully reviewing your diagnostic logs will save you from unexpected dangers.

Most of us look at the diagnostic log when we notice a serious issue. Instead of doing that , if we proactively monitor the diaglog we will be able to prevent many serious issues.

To review diaglogs proactively there are four options:

1)PDLOGMSGS_LAST24HOURS administrative view

Example:

SELECT * FROM SYSIBMADM.PDLOGMSGS_LAST24HOURS
ORDER BY TIMESTAMP DESC

2)​PD_GET_LOG_MSGS table function

Example:

SELECT *
FROM TABLE ( PD_GET_LOG_MSGS( CURRENT_TIMESTAMP – 7 DAYS)) AS T
WHERE INSTANCENAME = ‘DB2′ AND DBNAME = ‘SAMPLE’
ORDER BY TIMESTAMP ASC

3)Perl program or shell script to parse diaglog

4)db2diag utility

 

Diaglog utility is the most flexible of all with wide options . Below are some of the common ones.

db2diag -A    –> To archive the diaglog

 

db2diag -time  –> To retrieve records for a particular time

 

db2diag -time 2011-09-09 -l severe,error  –> To retrieve records for a particular time. Only severe and error messages are returned here

 

db2diag -l severe –> To return only severe messages

 

db2diag -g db=SAMPLE,instance=aabrashk –> To return messages of SAMPLE database and instance aabrashk

 

db2diag -g -d SAMPLE –> To return messages related to SAMPLE database only

 

db2diag -g level=Severe,pid=952356 –> To return severe messages from process id 952356

 

db2diag -g ‘funcname=pdLog’ –> To return the messages related to the particular function name

 

db2diag -lastrecords 5  –> To return the last 5 records

 

system(“db2diag -readfile”); –> To read from perl script

 

db2diag -gi “level=severe” -H 3d –> To return the severe messages from last 3 days

 

db2diag -g msg:=0×87040055 –> to return records with a particular ZRC

 

db2diag -rc –>displays descriptions for db2 internal ZRC codes

 

db2diag -gi appid:=local –> To return the messages related to the appid local

 

Db2dart utility

——————-

Did you ever run into one of the worst scenarios wherein your database is corrupt. Some tables might have become corrupt because someone messed up with active logs. To recover from this scenario , you could try an export followed by import . But the export utility would fail as the table pages were corrupt. We recovered a huge database recently with the following procedure:

1)Check the file system using fsck utility

2)Check and see what backups and log files are present .. Do we have all the archive logs in one place ? Making sure that you have backups and archive logs before this kind of activity always helps.If you run into any major issue , you can always recover using​ the backup image and archive logs

3)Check and see what kind of dependencies the table has . Does it have any referential integrity constraints ? When you load the data into the table , you got to make sure the parent-child relationships are still valid after the loads.

4)Collect all table and indexes DDL and store in a file . Do not forget the permissions on objects

5)Evaluate the row count to see what is missing towards the end. Once the load from db2dart is done you have to double check the count to see if you have got all the data or not

6) db2dart dbname /DDEL . This will generate a DEL file containing all records that it could recover from corrupted table.

It will prompt for tablename tableid start page and end page

You can give the end page a very big number to cover all

7)Create a temp table similar to actual and load the dump to this temp table

8)Rename the actual to actual_copy and the temp table created in step 7 as actual. Before this step is performed it has to be verified that the table has no views , triggers , constraints on it . If there are any views on the table the rename will fail. You have to drop the views and recreate them after the rename is done

e.g RENAME SAMPLE.EMPLOYEE to SAMPLE.EMPLOYEE_BACKUP

RENAME SAMPLE.EMPLOYEE_COPY to SAMPLE.EMPLOYEE  (EMPLOYEE_COPY is the temp table generated using the data from db2dart)

9)If there are views, triggers as mentioned in step 8 , they have to be dropped and recreated once the rename is done

10) create indexes and grant appropriate permissions on table using the file generated in step 4. Indexes has to be created with new names as the old indexes are attached to  backup table

11) Runstats utility has to be run to gather statistics for optimizer usage