DB2 Version 10.1 for Linux, UNIX, and Windows
DB2 Version 10.1 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 shipped 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 utility with SYSADM authority. Users with more limited privileges on the system can run this tool, however some of the data collection actions results 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-' >--+----------------------------------------------------------------------------+--> | .- -c--+--------------------------------+-. | | | '- -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-' >--+--------------------+---------------------------------------> '- -t--time interval-' >--+---------------------------------------+--+-----+-----------> '- -td--termination character delimiter-' '- -v-' >--+-----+--+--------------+------------------------------------| '- -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-'
When
you run the db2support command globally in a multiple
host environment, the absolute path of the file must be specified
for options that take a file as input. The file must also be accessible
by all hosts.
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.
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 db2batchThe 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 also cannot specify the archive (-A or -archive) or basic (-B or -basic) parameters when using -fodc.
A comma-separated list of existing FODC directories.
To show all FODC directories in a multiple-host environment, specify
the '-host all' option.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. You can omit some or all of the fields that follow the year field. If fields are omitted, 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 + 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 collects data for the past six days. -history 6d:2009 collects data for the first six days of 2009.
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.
with the exception of the -fodc parameter. but does not apply to the -fodc parameter.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 this parameter is not used, the default compressed file that is generated is db2support.zip.
If this parameter is used with the output path parameter, then the path specified by output path is ignored and the path specified by 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 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 occurs when db2support parses the profile file. If this occurs, db2support skips this collection. After the profile file is parsed, the collected information is stored in USERCOLLECTION/OUTFILE in the db2supp_system.zip file.
After the collection of data is completed, the results are compressed into a file named db2support_preupgrade.zip.
-r | -redistributeSpecifies that the diagnostic data related to data redistribution
are to be captured.10GB + number of member/CF host * 2GB db2support -d <database_name> -sf <filepath> -localhostIf only a start time is specified (-t 2009), the db2support command collects files that were modified after the start time. If only an end time is specified (-t :2009), the db2support command collects files that were modified before the end time. If both of inputs are specified (-t 2008:2009), the db2support command collects files that are modified within the interval of the start time and end time. There is no default value for this parameter. At least one of time stamps must be specified.
This
parameter cannot be used 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.
If you specify the output_path variable with the -unzip parameter, the extracted files are placed in the output_path directory. If you do not specify the output_path variable with the -unzip parameter, by default a new directory named compressed_file_name is created under the current directory and the extracted files are placed inside the compressed_file_name directory.
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.
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.
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 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 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"Using 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.
On the host run:
On the host run:
On the host run:
host:~$ db2support -cm -cfs -udaplIn order to protect the security of business data, this tool does not collect table data, schema (DDL), or logs. Some of the parameters 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 by the db2support tool is from the machine where the tool runs. In a client-server environment, database-related information is 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) is from the local machine where the db2support tool is running. Data such as buffer pool information, database configuration, and table space information is from the machine where the database physically resides.
There are some limitations on the type of queries accepted by the db2support optimizer tool:
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 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.