DB2 Version 9.7 for Linux, UNIX, and Windows
DB2 Version 9.7 for Linux, UNIX, and Windows
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.
For the most complete output, this utility should be invoked by a user with SYSADM authority, such as an 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.
None
>>-db2support--+-| Archive Mode |----+------------------------->< +-| Collection Mode |-+ '-| Extraction Mode |-' Archive Mode |-- -A--archive path--+--------------+--------------------------| '- -C--+-----+-' +-tar-+ '-tgz-' Collection Mode |--+-------------+----------------------------------------------> '-output_path-' >--+------------------------------------------------------------------------------+--> +-| event-monitor-options |----------------------------------------------------+ '- -aem--+---------------------------------+--+------------------------------+-' '- -compenv--compilation-env-file-' '- -tbspname--table-space-name-' >--+-----+--+----------------------+--+-------+-----------------> '- -B-' '- -cd--current degree-' '- -cfg-' >--+---------------------+--+------+--+------+------------------> '- -cl--collect level-' '- -cm-' '- -co-' >--+----------------------+-------------------------------------> '- -cs--current schema-' >--+----------------------------------------------------------------------------+--> | .- -c--+--------------------------------+-. | | | '- -u--userid--+---------------+-' | | | | '- -p--password-' | | '- -d--database name--+-------+--+-----------------------------------------+-' '- -nco-' >--+------------------+-----------------------------------------> '- -extenddb2batch-' >--+--------------------------------------+---------------------> '- -fodc--+--------------------------+-' '-list of FODC directories-' >--+----------------------------+--+-----+----------------------> '- -fodcpath--fodc_path_name-' '- -F-' >--+---------------------+--------------------------------------> '- -fp--function path-' >--+--------------------------------------------------+---------> +- -global--+--------------------------------+-----+ | '--dbp database_partition_number-' | | .-,--------. | | V | | +- -host--+---hostname-+-+-------------------------+ | '-all----------' | | .-,------------------------------. | | V | | '- -member--+---member_number--|--member_range-+-+-' '-all--------------------------------' >-- -sdir--shared directory path--+-----+-----------------------> '- -h-' >--+---------------------+--+-----------------------+-----------> '- -H--history period-' '- -il--isolation level-' >--+-----+--+-----+--+-----+--+------+--+------+----------------> '- -l-' '- -m-' '- -n-' '- -nc-' '- -nl-' >--+---------------+--+------------------+----------------------> '- -o--filename-' | .-,------. | | V | | '- -ol----levels-+-' >--+----------------------------+-------------------------------> '- -op--optimization profile-' >--+---------------------------+--+----------------+------------> '- -ot--optimization tables-' '- -pf--filename-' >--+--------------+--+-----+--+-------------------+--+-----+----> '- -preupgrade-' '- -q-' '- -ra--refresh age-' '- -r-' >--+------+--+-----+--+-------------------------+---------------> '- -ro-' '- -s-' '- -se--embedded SQL file-' >--+----------------+--+---------------------+------------------> '- -sf--SQL file-' '- -st--SQL statement-' >--+--------------------+---------------------------------------> '- -t--time interval-' >--+---------------------------------------+--+-----+-----------> '- -td--termination character delimiter-' '- -v-' >--+-----+--+--------------+------------------------------------| '- -x-' '- -timeout--N-' event-monitor-options |-- -actevm--event-monitor-name-- -appid--application-id--------> >-- -uowid--uow-id-- -actid--activity-id------------------------| Extraction Mode |--+-------------+----------------------------------------------> '-output_path-' >--+------------------------------------------+-----------------| '- -unzip--+-------+--compressed_file_name-' +-list--+ '-quiet-'
In DB2® Version 9.7 Fix Pack 4 and later fix packs, this option also archives all the data from the directory specified in the alt_diagpath configuration parameter into the specified archive path. A new directory will be created in the specified archive path with the name ALT_DB2DUMP with the system hostname and timestamp appended, for example ALT_DB2DUMP_systemhostname_2009-01-12-12.01.01. Also, files from the events/ subdirectory are archived into the ALT_EVENTS directory, and files from the stmmlog/ subdirectory are archived into the ALT_STMM directory.
This option is not available on Windows operating systems.
0 = collect only catalogs, db2look, dbcfg, dbmcfg, db2set
1 = collect 0 plus db2exfmt, db2caem (if -aem or -actevm, -appid, -uowid, -actid are specified)
2 = collect 1 plus .db2service (this is the default)
3 = collect 2 plus db2batchOn AIX® and Linux operating systems, if a cluster manager is installed, the db2support command collects diagnostic data for all hosts in the cluster by default and stores the data in the TSA folder in the generated .zip file. Specifying the -cm parameter collects additional cluster manager data that is space intensive or takes a long time to get collected.
The db2support command can only collect the FODC directories on the physical database host from where the command was run. As such, the -global parameter 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.
d = days
h = hours
m = minutes
s = secondsThe 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 operating systems or milliseconds on Windows operating 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.
If this option is not used, the default compressed file that is generated is db2support.zip.
If this option is used with the output path option, then the path specified by output path will be ignored and the path specified by the -o option will be used.
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.
The default profile is the db2support.profile file and is stored in the sqllib/adm directory. The sqllib/adm directory is the default location of the db2support.profile file. On Windows operating systems, if the sqllib/adm directory does not exist, you must create the adm directory under sqllib before issuing the db2support command.
This profile file is used to collect additional information that is not included in the standard db2support command execution. The following are the possible templates for the profile file which can be composed of any combination and number of template 1 and template 2:
<COLLECTION>
<NAME>...</NAME>
<CMD>...</CMD>
<OUTFILE>...</OUTFILE>
<TIMEOUT>...</TIMEOUT>
</COLLECTION><COLLECTION>
<NAME>...</NAME>
<FILE>...</FILE>
<OUTFILE>...</OUTFILE>
<TIMEOUT>...</TIMEOUT>
</COLLECTION>If the OUTFILE and CMD or FILE values are missing, a parse error will occur when db2support parses the profile file. If this occurs, db2support will skip this collection. After the profile file is parsed, the collected information is stored in USERCOLLECTION/OUTFILE in the db2supp_system.zip file.
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.
This option can be used with all other options.
Example 1
The db2support tool is invoked in the optimizer mode in one of the following ways:
db2support output_directory -d database_name -st sql_statementThe db2support tool stores the query in the optimizer directory by copying the query into the file called bad_query.sql.
db2support output_directory -d database_name -sf sql_filedb2support output_directory -d database_name -se embedded_sql_filedb2support output_directory -d database_name -collect 0The 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.
Example 2
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.
Example 3
db2support . -d sample -st "SELECT * FROM EMPLOYEE" -cs db2usr -cd 3
-ol 5 -ra ANY -fp MYSCHEMA -op MYPROFSCHEMA.MYPROFILE -ot ALL -il CSExample 4
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 CSThis 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.
Example 5
db2support -H 3dExample 6
db2support -H 3d:2009Example 7
db2support -t 2008Example 8
db2support -t 2009-01:2009-03Example 9
<COLLECTION>
<NAME>List</NAME>
<CMD>ls -la $HOME</CMD>
<OUTFILE>list.out</OUTFILE>
</COLLECTION>Example 10
db2support temp -unzip db2support.zipdb2support temp -unzip quiet db2support.zipExample 11
db2support -d sample -timeout 3Example 12
db2support -d sample -c -s -timeout 15Example 13
db2support -d sample -c -timeout 7 -st "select * from staff"Using db2support to collect activity event monitor data for the SQL statement:
In addition to current collection at -cl 2, this will invoke db2caem, which will create the activity event monitor and capture information of details, section and values, as well as actuals for the SQL statement "select * from staff". db2support will collect all the db2caem output.
In addition to current collection at -cl 2, this will invoke db2caem, which will create the activity event monitor and capture information of details, section and values, as well as actuals for the SQL statement specified in the file badquery.sql. db2support will collect all the db2caem output.
In addition to current collection at -cl 0, this will invoke db2caem, which will capture the activity event monitor information of details, section and values, as well as actuals for the SQL statement identified by the event monitor options from the existing activity event monitor. db2caem will not create activity event monitor in this case. db2support will collect all the db2caem output.
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 directories (diagpath and alt_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:
db2support does not collect explain data for dynamic SQL.
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, 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.