When it comes to collecting information for a DB2® problem, the most important DB2 utility you need to run is db2support.
The db2support utility automatically collects all DB2 and system diagnostic information
available. It also has an optional interactive "Question and Answer"
session, which poses questions about the circumstances of your problem.
About this task
Using the
db2support utility avoids possible
user errors, as you do not need to manually type commands such as
GET
DATABASE CONFIGURATION FOR database-name or
LIST
TABLESPACES SHOW DETAIL. Also, you do not require instructions
on which commands to run or files to collect, therefore it takes less
time to collect the data.
Procedure
- Execute the command db2support -h to
display the complete list of command options.
- Collect data using the appropriate db2support command.
The db2support utility should be run by
a user with SYSADM authority, such as an instance owner, so that the
utility can collect all of the necessary information without an error.
If a user without SYSADM authority runs db2support,
SQL errors (for example, SQL1092N) might result when the utility runs
commands such as QUERY CLIENT or LIST
ACTIVE DATABASES.
If you're using the db2support utility
to help convey information to IBM Software Support, run the db2support command
while the system is experiencing the problem. That way the tool will
collect timely information, such as operating system performance details.
If you are unable to run the utility at the time of the problem, you
can still issue the db2support command after the
problem has stopped since some first occurrence data capture (FODC)
diagnostic files are produced automatically.
If an FODC package is stored in a directory
path that is different than the default diagnostic path or not in
a path specified by an FODCPATH setting, you need to indicate the
FODC path to the db2support command with the -fodcpath parameter,
so that the FODC package can be included in the db2support.zip file.
The following basic invocation is usually sufficient
for collecting most of the information required to debug a problem,
unless you need to include the path to an FODC package with the -fodcpath parameter
(note, that if the -c option is used, the utility
will establish a connection to the database):
db2support <output path> -d <database name> -c
The output is conveniently collected and stored in
a compressed ZIP archive, db2support.zip, so
that it can be transferred and extracted easily on any system.
Results
The type of information that db2support captures
depends on the way the command is invoked, whether or not the database
manager has been started, and whether it is possible to connect to
the database.
The
db2support utility collects
the following information under all conditions:
- db2diag log files
- All trap files
- Locklist files
- Dump files
- Various system related files
- Output from various system commands
- db2cli.ini
- db2dsdriver.cfg
Depending on the circumstances, the
db2support utility
might also collect:
- Active log files
- Buffer pool and table space (SQLSPCS.1 and SQLSPCS.2)
control files (with -d option)
- Contents of the db2dump directory
- Extended system information (with -s option)
- Database configuration settings (with -d option)
- Database manager configuration settings files
- First occurrence data capture (FODC) information
(with the -fodc and -fodcpath options)
- Log File Header file (with -d option)
- Recovery History File (with -d option)
- Formatted
data for SYSIBM.SYSTABLES, SYSIBM.SYSINDEXES and SYSIBM.SYSDATAPARTITIONS
system catalog tables (with -d option, the database
not activated and the db2support tool not in optimizer
mode)
- Diagnostic
data for all hosts in the cluster (with cluster manager installed)
The
HTML report
db2support.html will always include
the following information:
- Problem record (PMR) number (if -n was specified)
- Operating system and level (for example, AIX® 5.1)
- DB2 release information
- An indication of whether it is a 32- or 64-bit environment
- DB2 install path information
- Contents of db2nodes.cfg
- Number of CPUs and disks and how much memory
- List of databases in the instance
- Registry information and environment, including PATH and LIBPATH
- Disk freespace for current filesystem and inodes for UNIX
- Java™ SDK level
- Java JCC version
- Java JCC configuration
- Database Manager Configuration
- Listing of the database recovery history file
- ls -lR output (or Windows equivalent) of the sqllib directory
- The result of the LIST NODE DIRECTORY command
- The result of the LIST ADMIN NODE DIRECTORY command
- The result of the LIST DCS DIRECTORY command
- The result of the LIST DCS APPLICATIONS EXTENDED command
- List of all installed software
The following information appears in the
db2support.html file
when the
-s option is specified:
- Detailed disk information (partition layout, type, LVM information,
and so on)
- Detailed network information
- Kernel statistics
- Firmware versions
- Other operating system-specific commands
The
db2support.html file contains
the following additional information if DB2 has
been started:
- Client connection state
- Database and Database Manager Configuration (Database Configuration
requires the -d option)
- CLI configuration
- Memory pool info (size and consumed). Complete data is collected
if the -d option is used
- The result of the LIST ACTIVE DATABASES command
- The result of the LIST DCS APPLICATIONS command
The
db2support.html file contains
the following information if the
-c option has
been specified and a connection to the database was successfully established:
- Number of user tables
- Approximate size of database data
- Database snapshot
- Application snapshot
- Buffer pool information
- The result of the LIST APPLICATIONS command
- The result of the LIST COMMAND OPTIONS command
- The result of the LIST DATABASE DIRECTORY command
- The result of the LIST INDOUBT TRANSACTIONS command
- The result of the LIST DATABASE PARTITION GROUPS command
- The result of the LIST DBPARTITIONNUMS command
- The result of the LIST ODBC DATA SOURCES command
- The result of the LIST PACKAGES/TABLES command
- The result of the LIST TABLESPACE CONTAINERS command
- The result of the LIST TABLESPACES command
- The result of the LIST DRDA IN DOUBT TRANSACTIONS command
- DB2 workload
manager information
Example
contents of db2support.zip file
You
can use the db2support command with the -unzip parameter
to extract the contents of the db2support.zip file
locally, optionally specifying the directory path where you want the
contents extracted to. You can also use the -unzip option
to extract the contents of archived diagnostic data without the need
for additional software. If you want to know only what files are included
in a db2support.zip file, without extracting
the actual content, you can instead use the -unzip list parameters
with the db2support command.
For an example
of the contents of a
db2support.zip file, the
following command was executed:
db2support . -d sample -c -f -st "select * from staff"
Extracting
the
db2support.zip file, the following files
and directories were collected:
- DB2CONFIG/ - Configuration information (for example,
database, database manager, BP, CLI, and Java developer
kit, among others)
- DB2DUMP/ - db2diag.log file
contents for the past 3 days
- DB2MISC/ - List of the sqllib directory
- DB2SNAP/ - Output of DB2 commands
(for example,db2set, LIST TABLES, LIST
INDOUBT TRANSACTIONS, and LIST APPLICATIONS,
among others)
- db2supp_opt.zip - Diagnostic information for optimizer problems
- db2supp_system.zip - Operating system information
- db2support.html - Diagnostic information formatted into HTML sections
- db2support.log - Diagnostic log information for db2support collection
- db2support_options.in - Command line options
used to start the db2support collection
Information about Optimizer can be found in the
db2supp_opt.zip file.
Extraction of this file finds the following directories:
- OPTIMIZER/ - Diagnostic information for optimizer problems
- OPTIMIZER/optimizer.log - File contains a log of all activities
- OPTIMIZER/CATALOGS - All the catalogs with
LOBs in the following subdirectories (generated only if the LOB column
in the catalog table is not empty):
- FUNCTIONS
- INDEXES
- NODEGROUPS
- ROUTINES
- SEQUENCES
- TABLES
- VIEWS
- OPTIMIZER/DB2DUMP - db2serv output (serv.* and serv2.* output
files)
System information can be found in the
db2supp_system.zip file.
Extraction of this file finds the following file and directories:
- DB2CONFIG/ - db2cli.ini (files from ~/sqllib/cfg)
- DB2MISC/ - DB2SYSTM file (binary), among others
- OSCONFIG/ - Different operating system information files (for
example, netstat, services, vfs, ulimit, and hosts, among others)
- OSSNAP/ - Operating system snapshots (for example, iostat, netstat,
uptime, vmstat, and ps_elf, among others)
- SQLDBDIR/ - Important buffer pool meta files (~/sqllib/sqldbdir)
- SQLGWDIR/ - DCS directory (files from ~/sqllib/sqlgwdir)
- SQLNODIR/ - Node directory (files from ~/sqllib/sqlnodir)
- SPMLOG/ - Files from ~/sqllib/spmlog
- report.log - Log of all collection activities
DB2 Version 9.7 for Linux, UNIX, and Windows