IBM Books

Administration Guide

db2expln Syntax and Parameters



>>-db2expln---+--------------+--+--------------------+---------->
              '--c--creator--'  '--d--database name--'
 
>--+-----------------------+--+-----+--+----+-+-----+--+-----+-->
   '--e--escape character--'  '--g--'  +--h-+ '--i--'  '--l--'
                                       '--?-'
 
>--+-------------------------+-+------------------------+------><
   +--o--output file---------+ '--u--user ID--password--'
   +-+-------------------+---+
   | '--p--package name--'   |
   +-+---------------------+-+
   | '--s--section number--' |
   | .--t-.                  |
   '-+----+------------------'

Where:

-c creator
The user ID of the package creator.

If you do not specify this option, you will be prompted for it.

You may specify the creator name using the pattern matching characters, percent sign (%) and underscore (_) that may be used in a LIKE predicate.

-d database name
The name of the database that contains the packages to be explained.

If you do not specify this option, you will be prompted for it.

-e escape character
Used to specify the character that is to be interpreted as an escape character, rather than a pattern-matching character.

For example, the db2expln command to explain the package TESTID.CALC% is db2expln -c TESTID -p CALC%. However, this command would also explain any other plans that start with CALC. To explain just the TESTID.CALC% package, you must use an escape character. By changing the command to read: db2expln -c TESTID -e ! -p CALC!% you specify that the ! character will be used as an escape character and !% is interpreted as the % character.

-g
Show optimizer plan graphs. Each section is examined, and the original optimizer plan graph (as presented by Visual Explain) is constructed. Note that the generated graph may not match the original plan.
-h or -?
Obtain help information about the input parameters. Specifying this option overrides all other options.
-i
Display operator IDs in the explained plan. The operator IDs allow the output from db2expln to be matched to the output from the Explain facility.
-l
The package name can be either lower or mixed-case if this option is specified. If this -l option is not specified, the package name is converted to uppercase
-o output file
The name of the file to which db2expln will write the results.

If you specify -o without a file name, you will be prompted for a file name. The default file name is db2expln.out.

-p package name
The name of the package to be explained.

If you do not specify this option you will be prompted to provide it.

You may specify the package name using the pattern matching characters, percent sign (%) and underscore (_) that can be used in a LIKE predicate.

-s section number
The section number to explain within the package. The number zero (0) may be specified if you wish to have all sections in the package explained. If the package creator (-c) or package name (-p) arguments imply that multiple packages will be explained, and thus multiple sections, the section value, if provided, is overridden with a zero (0).

If you do not specify this option you will be prompted to provide it.

Section numbers can be found by querying the system catalog SYSCAT.STATEMENTS (See Appendix J. "Catalog Views" for a description of the system catalog tables.)

-t
The output is directed to the terminal.

If you do not specify -o or -t, you will be prompted for a file name, with the default displaying the output at the terminal.

-u user ID password
When connecting to a database, use the provided user ID and password.

Both the user id and password must be valid according to naming conventions and be recognized by the database.

Some of the option flags above may have special meaning to your operating system and, as a result, may not be interpreted correctly in the db2expln command line. However, it may be possible to enter these characters by preceding them with an escape character. For more information, see your operating system user's manual.

Help and initial status messages, produced by db2expln, are written to standard output. All prompts and other status messages produced by the explain tool are written to standard error. Explain text is written to standard output or to a file depending on the output option chosen.

With the -p and -c options, multiple plans can be explained with one invocation of explain by specifying string constants for packages and creators with LIKE patterns. That is, the underscore (_) may be used to represent a single character, and the percent sign (%) may be used to represent the occurrence of zero or more characters.

For example, to explain all sections for all packages in a database named SAMPLE, with the results being written to the file my.exp, enter 

    db2expln -d SAMPLE -p % -c % -s 0 -o my.exp

[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]

[ DB2 List of Books | Search the DB2 Books ]