Collecting environment information with the db2support command

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.
Note: If text search is configured for a given Db2 instance and enabled for a database, corresponding diagnostic details are collected.

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.
    Note: The -udapl parameter is deprecated, and the -purescale parameter is recommended instead.

    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)
  • Workload manager (WLM) related information for optimizer mode (with -wlm option)
  • Specified system user information (with -su option)
  • Specified system group information (with -sg option)
  • Text search server (db2tss) configuration and logging information as well as catalog views contents (under SYSIBMTS schema)
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 RDMA
  • 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)
    Note: The -udapl parameter is deprecated.
  • 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 RDMA
  • 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
  • DB2TSS/ - Text Search Server Configuration and Logging information and SYSIBMTS catalog views contents
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. If db2support prompts messages, for example, DBT7116E, you might find more information within this log file.
  • 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