db2support - Problem analysis and environment collection tool command

db2support

db2support - Problem analysis and environment collection toolcommand

Collects environment data about either a client or server machine and places the files containing system data into a compressed file archive.

This tool can also collect basic data about the nature of a problem through an interactive question and answer process with the user.
Authorization

For the most complete output, this utility should be invoked by the instance owner. Users with more limited privileges on the system can run this tool, however some of the data collection actions will result in reduced reporting and reduced output.
Required connection

None
Command syntax


Read syntax diagram

Skip visual syntax diagram
>>-db2support--+-| Archive Mode |----+-------------------------><
'-| Collection Mode |-'

Archive Mode

|-- -A--archive path--+--------------+--------------------------|
'- -C--+-----+-'
+-tar-+
'-tgz-'

Collection Mode

|--output path--+-----+--+----------------------+--+-------+---->
'- -B-''- -cd--current degree-''- -cfg-'

>--+---------------------+--+------+---------------------------->
'- -cl--collect level-''- -co-'

>--+----------------------+------------------------------------->
'- -cs--current schema-'

>--+----------------------------------------------------------------------------+-->
| .- -c--+--------------------------------+-. |
| | '- -u--userid--+---------------+-' | |
| | '- -p--password-' | |
'- -d--database name--+-------+--+-----------------------------------------+-'
'- -nco-'

>--+------------------+--+-----+-------------------------------->
'- -extenddb2batch-''- -f-'

>--+--------------------------------------+--+-----+------------>
'- -fodc--+--------------------------+-''- -F-'
'-list of FODC directories-'

>--+---------------------+--+-----+----------------------------->
'- -fp--function path-''- -g-'

>--+-------------------------------------------------------------------------------+-->
'- -global-- -sdir--shared directory path--+----------------------------------+-'
'- -dbp--database partition number-'

>--+-----+--+---------------------+----------------------------->
'- -h-''- -H--history period-'

>--+-----------------------+--+-----+--+-----+--+-----+--------->
'- -il--isolation level-''- -l-''- -m-''- -n-'

>--+------+--+------+--+------------------+--------------------->
'- -nc-''- -nl-'| .-,------. |
| V | |
'- -ol----levels-+-'

>--+----------------------------+------------------------------->
'- -op--optimization profile-'

>--+---------------------------+--+-----+----------------------->
'- -ot--optimization table s-''- -q-'

>--+-------------------+--+------+--+-----+--------------------->
'- -ra--refresh age-''- -ro-''- -s-'

>--+-------------------------+--+----------------+-------------->
'- -se--embedded SQL file-''- -sf--SQL file-'

>--+---------------------+--+--------------------+-------------->
'- -st--SQL statement-''- -t--time interval-'

>--+---------------------------------------+--+-----+----------->
'- -td--termination character delimiter-''- -v-'

>--+-----+------------------------------------------------------|
'- -x-'


Note:

1. The db2support tool collects bad query-related information only if -st, -sf, or -se options are specified. In case there is an error or trap during optimization, -cl 0 (collect level zero) should be used to collect all catalog tables and db2look table definitions without trying to explain a bad query. One of the four options mentioned here must be specified to work with optimizer problems.
2. In case special registers have been set to values other than the default during the statement execution, it is very important for correct problem analysis that the same values should be passed as parameters to the db2support tool.

Command parameters

output path
Specifies the path where the archived library is to be created. This is the directory where user created files must be placed for inclusion in the archive.
-A archive_path | -Archive archive_path
Starting with Fix Pack 1, this option archives all the data from the directory specified in the DIAGPATH configuration parameter into the specified archive path. A new directory will be created in the specified archive path with the name “DB2 DUMP” with the system hostname and timestamp appended, for example "DB2DUMP_systemhostname_2009-01-12-12.01.01".
-B | -basic
Restricts the collection to only optimizer information. No other information will be collected except information for the db2supp_opt.zip file. The -basic parameter must be used with the -st, -sf, or -se parameters or a syntax error will be returned.
-cd | -curdegree
Specifies the value of the current degree special register to use. The default is the value of the dft_degree database configuration parameter.
-cfg
Collect configuration information and exclude all other support-related data. This option can only be combined with the following options: -f, -flow, -c, -connect, -d, -database, -m, -html, -n, -number, -o, -output, -p, -password, -u, -user, -v, -verbose.
Note: This command parameter is available in DB2® Version 9.7 Fix Pack 2 and later fix packs.
-cl | -collect
Specifies the value of the level of performan ce information to be returned. Valid values are:

0 = collect only catalogs, db2look, dbcfg, dbmcfg, db2set
1 = collect 0 plus exfmt
2 = collect 1 plus .db2service (this is the default)
3 = collect 2 plus db2batch

-co
Collect catalogs for all tables in the database. The default is to only collect catalog information for the tables used in a query that has a problem.
-cs | -curschema
Specifies the value of the current schema to use to qualify any unqualified table names in the statement. The default value is the authorization ID of the current session user.
-C | -compress
Enables archive compression. By default, the archive data is compressed into a single file using zip compression. Archive compression is only available in archive mode and must be used with the -A parameter or a syntax error is returned.

tar
The optional value tar enables tar archiving. The tar value is only supported on UNIX® systems.

tgz
The optional value tgz enables gzip compressed tar archiving. The tgz value is only supported on UNIX systems.

-d database_name | -database database_name
Specifies the name of the database for which data is being collected.
Note: In DB2 Version 9.7 Fix Pack 2 and later fix packs, by default an attempt will be made to connect to the specified database. To override this, specify the -noconnect or -nco parameter.

-nco | -noconnect
Specifies that there is no attempt to connect to the specified database.
Note: This command parameter is available in DB2 Version 9.7 Fix Pack 2 and later fix packs.

-c | -connect
Specifies that an attempt be made to connect to the specified database.
Note: In DB2 Version 9.7 Fix Pack 2 and later fix packs, this command parameter is included by default when a database is specified.

-extenddb2batch
Specfies that db2batch information for all the optimization levels specified with the -ol or -optlevel parameter are to be captured. At least one value for the -ol parameter and a -cl parameter value of 3 must be specified with the -extenddb2batch parameter or db2support will return a syntax error.
-f | -flow
Ignores pauses when requests are made for the user to Press <Enter> key to continue. This option is useful when running or calling the db2support tool via a script or some other automated procedure where unattended execution is desired.
-fodc
Specifies that only the FODC directories and the db2diag log files are collected. If no directories are specified, the db2support command will show a list of all the FODC directories for you to choose from. The directories are listed in ascending chronological order, based on usage timestamps, making the most recently used directories most visible.

The db2support command can only collect the FODC directories on the physical database host from where the command was run. As such, the -G or -global parameters will not work with -fodc.

You can specify the time interval (-t or -time) or history (-H or -history) parameters, but if a specified FODC directory is outside the specified timeframe, db2support will return an error.

You also cannot specify the archive (-A or -archive) or basic (-B or -basic) parameters when using -fodc.

Trap | Panic | BadPage | Hang | IndexError | Perf | DBMarkedBad
Specifies the category of FODC directories to collect.

list of FODC directories
A comma-separated list of existing FODC directories.

-F | -full
Specifies that all db2support information and optimizer specific information are to be captured with nothing excluded.
-fp | -funcpath
Specifies value of the function path special register to be used to resolve unqualified user defined functions and types. The default value is "SYSIBM ", "SYSFUN", "SYSPROC", X, where X is the value of the USER special register, delimited by double quotation marks.
-g | -get_dump
Specifies that all files in a dump directory, excluding core files, are to be captured.
-global | -G
Specifies that db2support will also be run on remote hosts.
Note: This command parameter is available in DB2 Version 9.7 Fix Pack 2 and later fix packs.

-dbp database partition number | -dbpartitionnum database partition number
Specifies that db2support will be run on the remote host of the specified database partition. If no database partition is specified with the -global or -G option, db2support will run on all remote hosts.

-sdir shared directory path | -S shared directory path
Specifies the shared directory db2support will use for temporary storage.

-h | -help
Displays help information. When this option is specified, all other options are ignored, and only the help information is displayed.
-H history_period | -history history_period
Starting with Fix Pack 1, this option limits the data collected by db2support to a particular interval of time. The history_period variable must be specified. The history_period variable may be specified with a number and time type with an optional beginning time value separated by a colon. The available types are:

d = days
h = hours
m = minutes
s = seconds

The beginning of time value is specified in time stamp format. Time stamp format is YYYY-MM-DD-hh.mm.ss.nnnnnn where YYYY specifies a year, MM for a month of a year (01 through 12), DD for a day of a month (01 through 31), hh for an hour of a day (00 through 23), mm for minute of an hour (00 through 59), ss for the seconds of a minute (00 through 59), nnnnnn for microseconds on UNIX systems or milliseconds on Windows ® systems. Some or all of the fields that follow the year field may be omitted. If fields are omitted, the default values will be used. The default values are 1 for the month and day, and 0 for all other fields.

The number and time type may be positive or negative specified with + or - signs. If only a number and time type are specified the default is negative. If a number and time type are specified and a beginning time value is specified the default is positive. For example, -history 6d will collect data for the past 6 days. -history 6d:2009 will collect data for the first 6 days of 2009.

This option cannot be used with the -time or -t option.
-il | -isolation
Specifies the isolation level to use to determine how data is locked and isolated from other processes while the data is being accessed. By default, the CURRENT ISOLATION special register is set to blanks.
-l | -logs
Specifies that active logs are to be captured.
-m | -html
Specifies that all system output is dumped into HTML formatted files. By default, all system related information is dumped into flat text files if this parameter is not used.
-n | -number
Specifies the problem management report (PMR) number or identifier for the current problem.
-nc | -nocatalog
Specifies that catalog information should not be collected. The default behavior for db2support will collect catalog information.
-nl | -nodb2look
Specifies that db2look information should not be collected. The default behavior for db2support will collect db2look information.
-ol levels | -optlevel levels
Specifies the value of the optimization level special register to use. The default is the value of the dft_queryopt database configuration parameter. The optimization level value may be specified as a single value or multiple values separated by a comma.

If multiple values are specified, all optimization information is collected for the first value. For each additional optimization level value specified, the explain plans will be collected and stored in a separate file along with the initial and end times of the collection for each level.
-op | -optprofile
Specifies value of the optimization profile special register to use. It is needed only if there was an optimization profile in effect when the statement was bound. The default is "" (an empty string).
-ot | -opttables
Specifies the value of the special register called "CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION" that is used to identify the types of tables that can be considered when optimizing the processing of dynamic SQL queries. The initial value of CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION is "SYSTEM".
-p password | -password password
Specifies the password for the user ID.
-q | -question_response
Specifies that interactive problem analysis mode is to be used.
-ra | -refreshage
Specifies the value of the refresh age special register. It applies only if there are materialized query tables (MQTs) that reference tables in the statement. The default value of CURRENT REFRESH AGE is zero.
-ro | -reopt
Specifies whether EXPLAIN with REOPT ONCE should be used when explaining the query. The default is to ignore the REOPT ONCE option.
-s | -system_detail
Specifies that detailed hardware and operating system information is to be gathered.
-se embedded SQL file | -sqlembed embedded SQL file
Specifies the path of the embedded SQL file containing the SQL statement for which data is being collected.
-sf SQL file | -sqlfile SQL file
Specifies the file path containing the SQL statement for which data is being collected.
-st SQL statement | -sqlstmt SQL statement
Specifies the SQL statement for which data is being collected.
-t time_interval | -time time_interval
Starting with Fix Pack 1, this option limits the data collected by db2support to a particular time interval specified by the time interval variable. The time interval can be specified as a start time, end time, or both in time stamp format separated by a colon. Time stamp format is YYYY-MM-DD-hh.mm.ss.nnnnnn where YYYY specifies a year, MM for a month of a year (01 through 12), DD for a day of a month (01 through 31), hh for an hour of a day (00 through 23), mm for minute of an hour (00 through 59), ss for the seconds of a minute (00 through 59), nnnnnn for microseconds on UNIX systems or milliseconds on Windows systems. Some or all of the fields that follow the year field may be omitted. If fields are omitted, the default values will be used. The default values are 1 for the month and day, and 0 for all other fields.

If only a start time is specified (-t 2009), db2support will collect files that are modified after the start time. If only an end time is specified (-t :2009), db2support will collect files that are modified before end time. If both of inputs are specified (-t 2008:2009), db2support will collect files that are modified within the interval of the start time and end time. There is no default value for this option. At least one of time stamps must be specified.

This option cannot be used with the -history or -H option.
-td | -delimiter
Specifies the statement termination character. This command parameter works in the same way as the -td option of the db2 command. The default is a semicolon.
-u userid | -user userid
Specifies the user ID to connect to the database.
-v | -verbose
Specifies that verbose output is to be used while this tool is running.
-x | -xml_generate
Specifies that an XML document containing the entire decision tree logic used during the interactive problem analysis mode (-q mode) is to be generated.

Examples

The db2support tool is invoked in the optimizer mode in one of the following ways:

* As an SQL statement from a command line.

db2support output_directory -d database_name -st sql_statement

The db2support tool stores the query in the optimizer directory by copying the query into the file called bad_query.sql.
* As an SQL statement stored in a file.

db2support output_directory -d database_name -sf sql_file

The file containing the query is copied by the tool into the optimizer directory.
* As a file containing an embedded static SQL statement with the query having the problem.

db2support output_directory -d database_name -se embedded_sql_file

The file containing the query is copied by the tool into the optimizer directory. The file does not need to be in the current directory but should be readable by an invoking userID.
* While returning different levels of performance information.

db2support output_directory -d database_name -collect 0

The db2support tool collects different levels of performance information based on the level of detail requested. The values 0 to 3 collect increasing amounts of detail. Catalog information and table definitions to enable you to reproduce the database objects for a production database are collected when a level of 0 is used.

To collect information to diagnose a slow query using optimizer-related special registers that were set by default, use:

db2support . -d sample -st "SELECT * FROM EMPLOYEE"

This example returns all the data to the db2support.zip file. Diagnostic files are created in the current directory and its subdirectories (since . is specified as the output path). The system information, optimizer information, and diagnostic files are collected as well.
To collect the same information shown in the previous example but with the user-specified values for the optimizer-related special registers, use:

db2support . -d sample -st "SELECT * FROM EMPLOYEE" -cs db2usr -cd 3
-ol 5 -ra ANY -fp MYSCHEMA -op MYPROFSCHEMA.MYPROFILE -ot ALL -il CS

To collect the same information shown in the previous example but with multiple user-specified values for the optimizer-related special registers and collect db2batch information for each optimizer special register value, use:

db2support . -d sample -st "SELECT * FROM EMPLOYEE" -cs db2usr -cd 3
-ol 3,5,7 -cl 3 -extenddb2batch -ra ANY -fp MYSCHEMA -op MYPROFSCHEMA.MYPROFILE -ot ALL -il CS

This example sets the following special registers: current schema to db2usr, current degree to 3, optimization level to 5, refresh age to ANY, function path to schema MYSCHEMA, optimization profile to MYPROFSCHEMA.MYPROFILE, current maintained table types to ALL, and the isolation level to CS. These values are set only for the connection that db2support establishes to the specified database and does not affect your entire environment. Providing the same special registry variables as used when the query was run is very important when correcting diagnostics.
To limit the data collection to files modified in the last 3 days prior to current time, use:

db2support -H 3d

To limit the data collection to files modified in the first 3 days of 2009 (time period 2009–01–01–00.00.00.000000 through 2009–01–04–00.00.00.000000), use:

db2support -H 3d:2009

To limit the data collection to files modified in time period 2008–01–01–00.00.00.000000 through the current time.

db2support -t 2008

To limit the data collection to files modified in the time period of 2009–01–01–00.00.00.000000 through 2009–03–01–00.00.00.000000, use:

db2support -t 2009-01:2009-03

Usage notes

In order to protect the security of business data, this tool does not collect table data, schema (DDL), or logs. Some of the options do allow for the inclusion of some aspects of schema and data (such as archived logs). Options that expose database schema or data should be used carefully. When this tool is invoked, a message is displayed that indicates how sensitive data is dealt with.

Data collected from the db2support tool will be from the machine where the tool runs. In a client-server environment, database-related information will be from the machine where the database resides via an instance attachment or connection to the database. For example, operating system or hardware information (-s option) and files from the diagnostic directory (diagpath) will be from the local machine where the db2support tool is running. Data such as buffer pool information, database configuration, and table space information will be from the machine where the database physically resides.

There are some limitations on the type of queries accepted by the db2support optimizer tool:

* Multiple queries are not supported. If you place several queries in a file, the tool gathers all the objects necessary for each of the queries. However, only the last query is explained. This is also true for files containing embedded static SQL statements.
* The tool does not run customer applications. However, you can run the application at the same time you are running db2support provided you are using one of the three methods discussed to evaluate a particular bad or slow query.
* Stored procedures are not supported.

db2support does not collect explain data for dynamic SQL.
Related concepts
Combining DB2 database and OS diagnostics
Related tasks
Collecting environment information using db2support command