Analyzing db2diag log files using db2diag tool

DB2 Version 9.5 for Linux, UNIX, and Windows

---

Analyzing db2diag log files using db2diag tool

http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/index.jsp?topic=/com.ibm.db2.luw.admin.trb.doc/doc/c0020701.html

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 DB2® 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
• OPSTATS which returns records related to optimizer statistics

To read messages from the MAIN facility:

db2diag -facility MAIN

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

db2diag -fac OPSTATS -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 OPSTATS facility having a level of Error and then outputting the Timestamp and PID field in a specific format:

db2diag -fac opstats -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.

Related concepts

Interpreting diagnostic log file entries

Related reference

db2diag - db2diag logs analysis tool command

Concept topic

---

( Feedback )

Last updated: 2009-12-14

http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/topic/com.ibm.db2.luw.admin.trb.doc/doc/c0020701.html