DB2 Version 9.7 for Linux, UNIX, and Windows

Analyzing db2diag log files using db2diag tool

The primary log file intended for use by database and system administrators is the administration notification log. The db2diag log files are intended for use by IBM Software Support for troubleshooting purposes.

Administration notification log messages are also logged to the db2diag log files using a standardized message format.

The db2diag tool serves to filter and format the volume of information available in the db2diag log files. Filtering db2diag log file records can reduce the time required to locate the records needed when troubleshooting problems.

Example 1: Filtering the db2diag log files by database name

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 UDB, base sys utilities, sqleDatabaseQuiesce, probe:2
MESSAGE : ADM7507W  Database quiesce request has completed successfully.

Example 2: Filtering the db2diag log files by process ID

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 UDB, data management, sqldPoolCreate, probe:273
RETCODE : ZRC=0x8002003C=-2147352516=SQLB_BAD_CONTAINER_PATH
          "Bad container path"

Example 3: Formatting the db2diag tool output

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

Example 4: Filtering messages from different facilities

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}"

Example 5: Merging files and sorting records according to timestamps

This example shows how to merge two or more db2diag log files and sort the records according to timestamps.

The two db2diag log files to merge are the following:
  • 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 the following:
  • 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.

Example 6: Merging split diagnostic directory path files from a single host and sorting records by timestamps

This example shows how to merge files from three database partitions on the current host. To obtain the split diagnostic directory paths, the diagpath database manager configuration parameter was set in the following way:
db2 update dbm cfg using diagpath '"$n"'
The following is a list of the three db2diag log files to merge:
  • ~/sqllib/db2dump/NODE0000/db2diag.log
  • ~/sqllib/db2dump/NODE0001/db2diag.log
  • ~/sqllib/db2dump/NODE0002/db2diag.log
To merge the three diagnostic log files and sort the records according to timestamps, execute the following command:
db2diag -merge

Example 7: Merging split diagnostic directory path files from multiple hosts and database partitions

In this example, the default diagnostic data directory path was split according to physical host and database partition by setting the diagpath database manager configuration parameter using the following command:
db2 update dbm cfg using diagpath '"$h$n"'
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 is a list of the six db2diag log files:
  • ~/sqllib/db2dump/HOST_bower/NODE0000/db2diag.log
  • ~/sqllib/db2dump/HOST_bower/NODE0001/db2diag.log
  • ~/sqllib/db2dump/HOST_bower/NODE0002/db2diag.log
  • ~/sqllib/db2dump/HOST_horton/NODE0003/db2diag.log
  • ~/sqllib/db2dump/HOST_horton/NODE0004/db2diag.log
  • ~/sqllib/db2dump/HOST_horton/NODE0005/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.

Example 8: Filtering and merging only recent diagnostic log entries

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

Example 9: Archiving the db2diag log files on an instance-less client

Starting with Version 9.7, Fixpack 4, the db2diag -archive (or -A) option is available with IBM® Data Server Driver Package and IBM Data Server for ODBC and CLI. This option enables you 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
Parent topic: Tools for troubleshooting
Related concepts:
Interpretation of diagnostic log file entries
Related tasks:
Splitting a diagnostic data directory path by database partition server, database partition, or both
Related reference:
db2diag - db2diag logs analysis tool command