Analyzing db2diag log files using db2diag tool

If there are several databases in the instance, and you want to only see those messages which pertain to the database "SAMPLE", you can filter the db2diag log files as follows:

db2diag -g db=SAMPLE

Thus you would only see db2diag log file records that contained "DB: SAMPLE", such as:

2006-02-15-19.31.36.114000-300 E21432H406         LEVEL: Error
PID     : 940                  TID  : 660         PROC : db2syscs.exe
INSTANCE: DB2                  NODE : 000         DB   : SAMPLE
APPHDL  : 0-1056               APPID: *LOCAL.DB2.060216003103
FUNCTION: Db2, base sys utilities, sqleDatabaseQuiesce, probe:2
MESSAGE : ADM7507W  Database quiesce request has completed successfully.

The following command can be used to display all severe error messages produced by processes running on partitions 0,1,2, or 3 with the process ID (PID) 2200:

db2diag -g level=Severe,pid=2200 -n 0,1,2,3

Note that this command could have been written a couple of different ways, including db2diag -l severe -pid 2200 -n 0,1,2,3. It should also be noted that the -g option specifies case-sensitive search, so here "Severe" will work but will fail if "severe" is used. These commands would successfully retrieve db2diag log file records which meet these requirements, such as:

2006-02-13-14.34.36.027000-300 I18366H421         LEVEL: Severe
PID     : 2200                 TID  : 660         PROC : db2syscs.exe
INSTANCE: DB2                  NODE : 000         DB   : SAMPLE
APPHDL  : 0-1433               APPID: *LOCAL.DB2.060213193043
FUNCTION: Db2, data management, sqldPoolCreate, probe:273
RETCODE : ZRC=0x8002003C=-2147352516=SQLB_BAD_CONTAINER_PATH
          "Bad container path"

The following command filters all records occurring after January 1, 2006 containing non-severe and severe errors logged on partitions 0,1 or 2. It outputs the matched records such that the time stamp, partition number and level appear on the first line, pid, tid and instance name on the second line, and the error message follows thereafter:

db2diag -time 2006-01-01 -node "0,1,2" -level "Severe, Error" | db2diag -fmt 
"Time: %{ts}
Partition: %node Message Level: %{level} \nPid: %{pid}  Tid: %{tid} 
Instance: %{instance}\nMessage: @{msg}\n"

An example of the output produced is as follows:

Time: 2006-02-15-19.31.36.099000 Partition: 000 Message Level: Error
Pid: 940 Tid:940 Instance: DB2
Message: ADM7506W  Database quiesce has been requested.

For more information, issue the following commands:

• db2diag -help provides a short description of all available options
• db2diag -h brief provides descriptions for all options without examples
• db2diag -h notes provides usage notes and restrictions
• db2diag -h examples provides a small set of examples to get started
• db2diag -h tutorial provides examples for all available options
• db2diag -h all provides the most complete list of options

The following examples show how to only see messages from a specific facility (or from all of them) from within the database manager. The supported facilities are:

• ALL which returns records from all facilities
• MAIN which returns records from Db2® general diagnostic logs such as the db2diag log files and the administration notification log
• OPTSTATS which returns records related to optimizer statistics

To read messages from the MAIN facility:

db2diag -facility MAIN

To display messages from the OPTSTATS facility and filter out records having a level of Severe:

db2diag -fac OPTSTATS -level Severe

To display messages from all facilities available and filter out records having instance=harmistr and level=Error:

db2diag -fac all -g instance=harmistr,level=Error

To display all messages from the OPTSTATS facility having a level of Error and then outputting the Timestamp and PID field in a specific format:

db2diag -fac optstats -level Error -fmt " Time :%{ts} Pid :%{pid}"

The two db2diag log files to merge are the following ones:

• db2diag.0.log; contains records of Level:Error with the following timestamps:
  • 2009-02-26-05.28.49.822637
  • 2009-02-26-05.28.49.835733
  • 2009-02-26-05.28.50.258887
  • 2009-02-26-05.28.50.259685
• db2diag.1.log; contains records of Level:Error with the following timestamps:
  • 2009-02-26-05.28.11.480542
  • 2009-02-26-05.28.49.764762
  • 2009-02-26-05.29.11.872184
  • 2009-02-26-05.29.11.872968

To merge the two diagnostic log files and sort the records according to timestamps, execute the following command:

db2diag -merge db2diag.0.log db2diag.1.log -fmt %{ts} -level error

The result of the merge and sort of the records is as follows:

• 2009-02-26-05.28.11.480542
• 2009-02-26-05.28.49.764762
• 2009-02-26-05.28.49.822637
• 2009-02-26-05.28.49.835733
• 2009-02-26-05.28.50.258887
• 2009-02-26-05.28.50.259685
• 2009-02-26-05.29.11.872184
• 2009-02-26-05.29.11.872968

where the timestamps are merged and sorted chronologically.

By default, each member and CF log to a different db2diag log file. The following is a list of the three db2diag log files to merge:

• ~/sqllib/db2dump/DIAG0000/db2diag.log
• ~/sqllib/db2dump/DIAG0001/db2diag.log
• ~/sqllib/db2dump/DIAG0002/db2diag.log

To merge the three diagnostic log files and sort the records according to timestamps, execute the following command:

db2diag -merge

This example shows how to obtain an output of all the records from all the diagnostic logs and merge the diagnostic log files from three database partitions on each of two hosts, bower and horton. The following list shows the six db2diag log files:

• ~/sqllib/db2dump/HOST_bower/DIAG0000/db2diag.log
• ~/sqllib/db2dump/HOST_bower/DIAG0001/db2diag.log
• ~/sqllib/db2dump/HOST_bower/DIAG0002/db2diag.log
• ~/sqllib/db2dump/HOST_horton/DIAG0003/db2diag.log
• ~/sqllib/db2dump/HOST_horton/DIAG0004/db2diag.log
• ~/sqllib/db2dump/HOST_horton/DIAG0005/db2diag.log

To output the records from all six db2diag log files, run the following command:

db2diag -global

To merge all six db2diag log files in the diagnostic data directory path from all three database partitions on each of the hosts bower and horton and format the output based on the timestamp, execute the following command:

db2diag -global -merge -sdir /temp/keon -fmt %{ts}

where /temp/keon is a shared directory, shared by the hosts bower and horton, to store temporary merged files from each host during processing.

In this example, db2diag log file records are filtered to display only a specific number of recent entries. To display the last 5 formatted records for each of the 3 partitions in a partitioned database environment, merged and formatted by timestamp, enter:

db2diag -lastrecords 5 -global -merge -sdir /home/vbmithun -fmt %{ts}

2010-10-08-04.46.02.092192
2010-10-08-04.46.02.092821
2010-10-08-04.46.02.093497
2010-10-08-04.46.02.094431
2010-10-08-04.46.02.095317
2010-10-08-04.46.05.068648
2010-10-08-04.46.05.069212
2010-10-08-04.46.05.069900
2010-10-08-04.46.05.071008
2010-10-08-04.46.05.071831
2010-10-08-04.46.07.302051
2010-10-08-04.46.07.302727
2010-10-08-04.46.07.303544
2010-10-08-04.46.07.304647
2010-10-08-04.46.07.305391

You can also filter recent diagnostic log records further to return only messages of a specific level. For example, to return only those records in the last 10 records that have a severe message level, enter:

$ db2diag db2diag.log -lastrecords 10 -level Severe -fmt %{ts}

2010-08-11-04.11.33.733807
2010-08-11-04.11.33.735398

You can use the db2diag -archive (or -A) option, which is available with IBM® Data Server Driver Package and IBM Data Server for ODBC and CLI, to archive the diagnostic log file on an instance-less client. For example:

$ db2diag -A
db2diag: Moving "/home/usr1/clidriver/db2dump/db2diag.log"
         to     "/home/usr1/clidriver/db2dump/db2diag.log_2010-09-14-01.16.26"

If you specify options other than -archive or -A, an error message is returned. For example:

$ db2diag -x
db2diag: Unrecognized option: -x

$ db2diag -pid 1234
db2diag: Unrecognized option: -pid