When it comes to collecting information for a DB2® problem, the most important DB2 utility you need to run is db2support.
The db2support command 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.
To collect all necessary information without an error, run
the db2support utility as a user with SYSADM authority,
such as an instance owner. If you run the command without SYSADM authority,
SQL errors (for example, SQL1092N) might result when the utility runs
commands such as QUERY CLIENT or LIST
ACTIVE DATABASES.
To use the db2support command
to help collect information for IBM Software Support, run the db2support command
while the system is experiencing the problem. That way, the tool collects
timely information, such as operating system performance details.
If you cannot run the utility at the time of the problem, you can
still issue the db2support command after the problem
stops because some first occurrence data capture (FODC) diagnostic
files are produced automatically.
If an FODC package is stored in a directory
path that is different from the default diagnostic path or not in
a path specified by an FODCPATH setting, 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 typically
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:
db2support <output path> -d <database name>
The db2support tool collects most diagnostic data
specific to DB2 pureScale® components
by default. If you specify the -purescale, -cm, -cfs,
or -udapl parameter, the db2support command
collects additional diagnostic data that is space intensive or takes
a longer time to collect, but helps determining the source of problem
faster in DB2 pureScale environments.
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 the database manager
is 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 command not in
optimizer mode)
The
db2support utility
collects the following information in
DB2 pureScale environments:
- Diagnostic data for DB2 pureScale components,
such as cluster manager, cluster file system, and uDAPL
- Additional diagnostic data for cluster manager (with -cm option)
- Additional diagnostic data for cluster file system (with -cfs option)
- Additional diagnostic data for uDAPL (with -udapl option)
- Additional DB2 diagnostic
data (with -purescale option)
The
HTML report
db2support.html always includes 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 installation path information
- Contents of db2nodes.cfg
- Number of processors and disks and how much memory
- List of databases in the instance
- Registry information and environment, including PATH and LIBPATH
- Disk free space for current file system and inodes for UNIX
- Java™ SDK level
- Java JCC version
- Java JCC configuration
- Database manager configuration
- 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
- The result of the db2prereqcheck command
- List of all installed software
- DB2 license information
- DB2 compliance report
- Audit configuration information
- CLI configuration information
- Problem determination settings
- Status of the db2trc command
- Listing of log directory
The
db2support.html file
contains the following additional information if the DB2 database manager is started:
- Client connection state
- Database and database manager configuration (Database configuration
requires the -d option)
- Application snapshot
- 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 when 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
- Listing of the database recovery history file
- Optimizer database configuration
- Database configuration
- Nodegroup information
- Storage group information
- Number of string IDs
- List of tables
A db2support.html file
is included at the top level of the db2support package
to help you to quickly search for any diagnostic data that is collected
by the db2support command. This HTML file includes
links of the data collected in the db2support.html file
that point to its corresponding flat files in the subdirectory of
the db2support package. A plain text version
of the map file called db2support.map file is
also included in the db2support package.
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 three days
- DB2MISC/ - List of the sqllib directory
- DB2SNAP/ - Output of DB2 commands
(for example,db2set, LIST TABLES, LIST
INDOUBT TRANSACTIONS, and LIST APPLICATIONS)
- PURESCALE/-
Diagnostic information for DB2 pureScale components,
such as cluster manager, cluster file system and uDAPL
- db2supp_opt.zip - Diagnostic information for optimizer problems
- db2supp_system.zip - Operating system information
- db2support.html - Map to flat files
collected in each subdirectory of the db2support.zip file
listed in HTML format and 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
- db2support.map - Map to flat files
collected in each subdirectory of the db2support.zip file
listed in plain text format
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)
- OSSNAP/ - Operating system snapshots (for example, iostat, netstat, uptime, vmstat,
and ps_elf)
- 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 10.1 for Linux, UNIX, and Windows