DB2 10.5 for Linux, UNIX, and Windows
DB2 10.5 for Linux, UNIX, and Windows
Collects environment data about either a client or server machine and places the files that contain system data into a compressed file archive.
The db2support command that is included with DB2® installation images supports only a subset of the command parameters that are available after you install the DB2 product. Until you install the DB2 product, the only db2support command parameters that you can use are the -install and -host parameters.
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, run this command with SYSADM authority, such as an instance owner. If you do not have SYSADM authority, some of the data collection actions result in reduced reporting and reduced output.
None
>>-db2support--+-| Archive Mode |-----------------+------------>< +-| Collection Mode |--------------+ +-| Installation collection mode |-+ '-| Extraction Mode |--------------' Archive Mode |-- -A--archive_path--+--------------+--------------------------| '- -C--+-----+-' +-tar-+ '-tgz-' Collection Mode |--+-------------+----------------------------------------------> '-output_path-' >--+------------------------------------------------------------------------------+--> +-| event-monitor-parameters |-------------------------------------------------+ '- -aem--+---------------------------------+--+------------------------------+-' '- -compenv--compilation_env_file-' '- -tbspname--table_space_name-' >--+-----+--+----------------------+--+-------+-----------------> '- -B-' '- -cd--current degree-' '- -cfg-' >--+---------------------+--------------------------------------> '- -cl--collect_level-' >--+-+------+--+-------+--+---------+-+--+------+---------------> | '- -cm-' '- -cfs-' '- -udapl-' | '- -co-' '-+- -ps--------+------------------' '- -purescale-' >--+----------------------+-------------------------------------> '- -cs--current_schema-' >--+----------------------------------------------------------------------------------+--> | .-alldbs--|--all_databases-. | | | .-,-------------. | .- -c--+----------------------------+-. | | | V | | | '- -u--userid-- -p--password-' | | '-+- -d----database name-+---+--+-------+--+-------------------------------------+-' '- -nco-' >--+------------------+-----------------------------------------> '- -extenddb2batch-' >--+--------------------------------------+---------------------> '- -fodc--+--------------------------+-' '-list_of_FODC_directories-' >--+----------------------------+--+-----+----------------------> '- -fodcpath--fodc_path_name-' '- -F-' >--+---------------------+--------------------------------------> '- -fp--function_path-' .-all----------. | .-,--------. | | V | | .- -host--+---hostname-+-+-------------------------. >--+--------------------------------------------------+---------> +-localhost----------------------------------------+ | .-,------------------------------. | | 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-' >--+--------------------------------+---------------------------> '-system_user--|--su--user_name -' >--+----------------------------------+-------------------------> '-system_group--|--sg--group_name -' >--+--------------------+---------------------------------------> '- -t--time_interval-' >--+---------------------------------------+--+-----+-----------> '- -td--termination_character_delimiter-' '- -v-' >--+-----+--+-----+--+--------------+---------------------------| '-wlm-' '- -x-' '- -timeout--N-' event-monitor-parameters |-- -actevm--event_monitor_name-- -appid--application_id--------> >-- -uowid--uow_id-- -actid--activity_id------------------------| Installation collection mode |--+-------------+----------------------------------------------> '-output_path-' >--+-----------------------------------------+------------------| '- -install--+--------------------------+-' | .-,--------. | | V | | +- -host----hostname-+-----+ '- -instance--instancename-' Extraction Mode |--+-------------+----------------------------------------------> '-output_path-' >--+------------------------------------------+-----------------| '- -unzip--+-------+--compressed_file_name-' +-list--+ '-quiet-'
This parameter also archives all the data from the directory specified in the alt_diagpath configuration parameter into the specified archive path. The name of this directory is ALT_DB2DUMP. 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 parameter is not available on Windows operating systems.
The db2caem command ignores the db2support command special register options. To specify the compilation environment for the db2caem command, you should use the -compenv parameter. The db2caem command executes the specified SQL statement.
The -aem parameter does not support the -se parameter.
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 db2batchBy default, an attempt is made to connect to the specified database. To override this behavior, specify the -noconnect or -nco parameter.
If you specify multiple databases, you cannot run the db2support command in optimizer mode or with the -preupgrade parameter.
The db2support command can only collect the FODC directories on the physical database host from where the command was run. The -host or -member parameters can also be used to collect FODC directories remotely. However, since the FODC directory could contain large files, like core files, it is recommended that the -fodc parameter is run on the host where FODC directories reside.
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 not collect the specified FODC directory. If the -t or -H parameters are not specified, the -fodc parameter will collect FODC directories within 14 days.
You cannot specify the archive (-A or -archive) or basic (-B or -basic) parameter when using the -fodc parameter.
Optionally, you can specify a beginning time value that is separated by a colon.You specify the beginning of time value in time stamp format. The time stamp format is YYYY-MM-DD-hh.mm.ss.nnnnnn
You can omit some or all of the fields that follow the year field. If you omit fields, the default values are used. The default values are 1 for the month and day and 0 for all other fields.
The number and time type can be positive or negative, which you specify with the plus sign (+) or minus sign (-). If you specify only a number and time type, the default is negative. If you specify a number, a time type, and a beginning time value, the default is positive. For example, -history 6d collects data for the past six days, and -history 6d:2013 collects data for the first six days of 2013.
This parameter cannot be used with the -time or -t parameter. The default value is 14 days if the -H or -t parameters are not specified.
If you specify the -host option in an environment that is not a DB2 pureScale® environment or a partitioned database environment, db2support returns an error.
To collect diagnostic data on multiple hosts specify the -host parameter followed by hostname_list, where hostname_list is a comma-separated list of hosts for which you want to collect diagnostic data.
The db2support tool runs per host. If multiple members reside on one host, db2support runs only once on the host. If you specify the members on which the command is issued, the member numbers you provide are used only to determine the hosts on which db2support runs.
If you specify the -member option in an environment that is not a DB2 pureScale environment or a partitioned database environment, db2support returns an error.
If you do not specify this parameter, the name of the compressed file is db2support.zip.
If you use this parameter with the output_path parameter, the path that you specify for the output_path parameter is ignored, and the path that you specify for the -o parameter is 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 are 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 the default directory for the file is the sqllib/adm directory. On Windows operating systems, if the sqllib/adm directory does not exist, you must create it before issuing the db2support command.
This profile file is used to collect information that is not included in the standard db2support command execution. The possible templates for the profile file are as follows. You can use 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>The db2support command skips all blank lines when it parses the file.
If the OUTFILE and either the CMD or FILE values are missing, a parse error occurs when the command parses the file. If this occurs, the db2support command skips this collection. After the profile file is parsed, the collected information is stored in the db2supp_system.zip file in the USERCOLLECTION/OUTFILE directory.
After the collection of data is completed, the results are compressed into a file named db2support_preupgrade.zip.
10 GB + number of member/CF host x 2 GB db2support -d <database_name> -sf <filepath> -localhostYou can omit some or all of the fields that follow the year field. If you omit fields, the default values are used. The default values are 1 for the month and day and 0 for all other fields.
If you specify only a start time (for example, -t 2012), the db2support command collects files that were modified after the start time. If you specify only an end time (for example, -t :2012), the db2support command collects files that were modified before the end time. If you specify both times (for example, -t 2012:2013), the db2support command collects files that were modified between the start time and end time. There is no default value for this parameter. You must specify at least one of the time stamps.
You cannot use this parameter with the -history or -H parameter.
The default value is 14 days if the -H or -t parameters are not specified.This parameter can be used with all other parameters.
This parameter can extract the db2support.zip file on the system file where extraction utilities are not available.
You cannot combine the -unzip parameter with parameters from other db2support command modes.
If you specify the output_path parameter with the -unzip parameter, the extracted files are placed in the output_path directory. If you do not specify the output_path parameter with the -unzip parameter, a new directory that is named compressed_file_name is created in the current directory, and the extracted files are placed inside the compressed_file_name directory.
The following examples show different ways to invoke the db2support command in optimizer mode:
db2support output_directory -d database_name -st sql_statementThe db2support command stores the query in the optimizer directory by copying the query into the bad_query.sql file.
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 command collects different levels of performance information based on the level of detail that you request. The values 0 - 3 collect increasing amounts of detail. If you use the 0 option, catalog information and table definitions are collected, which you can use to reproduce the database objects for a production database.
db2support . -d sample -st "SELECT * FROM EMPLOYEE"In this example, the command returns all the data to the db2support.zip file. Diagnostic files are created in the current directory and its subdirectories, because . is specified as the output path. The system information, optimizer information, and diagnostic files are collected as well.
db2support . -d sample -st "SELECT * FROM EMPLOYEE" -cs db2usr -cd 3
-ol 5 -ra ANY -fp MYSCHEMA -op MYPROFSCHEMA.MYPROFILE -ot ALL -il CSdb2support . -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 CSThese values are set only for the connection that the db2support command establishes to the specified database. The entire environment is not affected. Providing the same special registry variables that were used when the query was run is important when correcting diagnostics.
db2support -H 3ddb2support -H 3d:2009db2support -t 2008db2support -t 2009-01:2009-03<COLLECTION>
<NAME>List</NAME>
<CMD>ls -la $HOME</CMD>
<OUTFILE>list.out</OUTFILE>
</COLLECTION>db2support -unzip db2support_hostname1.zip db2support temp -unzip db2support.zipdb2support temp -unzip quiet db2support.zipThe following are examples of the -install and-host parameters:
db2support -installdb2support temp -installdb2support -install -host myhost1db2support -install -host myhost1,myhost2db2support -d sample -timeout 3db2support -d sample -c -s -timeout 15db2support -d sample -c -timeout 7 -st "select * from staff"db2support -alldbsdb2support -alldbs -u guest1 -p passworddb2support -alldbs -cfgdb2support -d mydb1, mydb2, mydb3db2support -su myuserdb2support -sg mygroupdb2support -su myuser -sg mygroupUsing db2support to collect activity event monitor data for the SQL statement:
In addition to current collection at -cl 2, this command starts the db2caem command, which creates the activity event monitor and capture information of details, section, values, and actuals for the SQL statement "select * from staff". The db2support command collects all the db2caem output.
In addition to current collection at -cl 2, this command invokes db2caem, which creates the activity event monitor and capture information of details, section, values, and actuals for the SQL statement specified in the file badquery.sql. The db2support command collects all the db2caem output.
In addition to current collection at -cl 0, this command starts the db2caem command, which captures the activity event monitor information of details, section, values, and actuals for the SQL statement identified by the event monitor options from the existing activity event monitor. The db2caem command does not create an activity event monitor in this case. The db2support command collects all the db2caem output.
Using db2support to collect data that is related to WLM:
In addition to the current optimizer collection, this command collects additional WLM information for optimizer mode under the collection level 0.
In addition to the current optimizer collection, this command collects WLM information for optimizer mode for the specified SQL statement.
In addition to the current optimizer collection, this command collects additional WLM information for optimizer mode for the specified SQL file.
On the host run:
On the host run:
On the host run:
host:~$ db2support -cm -cfs -udaplThe db2support command collects bad query-related information only if you specify the -st, -sf, or -se parameter. If there is an error or trap during optimization, use the -cl 0 (collection level zero) parameter to collect all catalog tables and db2look table definitions without trying to explain a bad query. To collect information that is related to an activity event monitor as part of optimizer collection, you can specify the -aem parameter (with the -st or -sf parameter) or event monitor options. You must specify one of these options to work with optimizer problems.
If you specify any of the options for optimizer collection, you must also specify the -d parameter.
If you do not specify the -F or -full parameter, only the db2diag.log file from the last three days are collected for the optimizer collection. If you want to collect all files in diagpath, including the full db2diag.log file for the optimizer collections, you must specify the -F or -full parameter.
If you set special registers to values other than the default values during statement execution, pass these values to the db2support command to help ensure correct problem analysis. The special register options are ignored by db2caem command collection.
The -global parameter is discontinued. The -host all parameter is the default behavior of the db2support command; thus, information from all hosts is collected by default.
The db2support command takes a long time to run because it collects most diagnostic data that is 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. However, this information can help speed up the problem determination process in DB2 pureScale environments.
To protect the security of business data, this command does not collect table data, schema (DDL statements), or logs. Some of the parameters do allow for the inclusion of some aspects of schema and data, such as archived logs. Carefully use parameters that expose database schema or data.When you issue the command, a message is displayed that indicates how sensitive data is dealt with.
The db2support command collects data from the machine where the command runs. In a client-server environment, database-related information is from the machine where the database is located, through an instance attachment or connection to the database. For example, operating system or hardware information (-s parameter) and files from the diagnostic directories (diagpath and alt_diagpath) are from the local machine where the db2support command runs. Data such as buffer pool information, database configuration information, and table space information is from the machine where the database is physically located.
The limitations on the type of queries that the db2support command accepts are as follows:
The db2support command 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 is not in a path that is specified by an FODCPATH setting, you must indicate the FODC path to the db2support command by using the -fodcpath parameter. The FODC package is then included in the db2support.zip file.