IBM Books

Command Reference

db2expln - DB2 SQL Explain Tool

Describes the access plan selection for static SQL statements in packages that are stored in the DB2 common server system catalogs. Given a database name, package name, package creator, and section number, the tool interprets and describes the information in these catalogs. This tool is located in the misc subdirectory of the instance sqllib directory.

Authorization

One of the following:

sysadm
dbadm

Required Connection

A database connection is temporarily established by this command during processing.

Command Syntax 



>>-db2expln---+--------------+--+-------------+--+-------------+->
              +--c--creator--+  +--d--dbname--+  +--e--escape--+
 
>--i---l---+--------------+--+------------+--+--------------+--->
           +--o--outfile--+  +--p--pname--+  +--s--sectnbr--+
 
>--t---+---------------+--+--h-+-------------------------------><
       +--u--user--pw--+  +--?-+

Command Parameters

-c creator
User ID of the package creator.
-d dbname
Alias name of the database that contains the packages to be explained.
-e
Escape character for LIKE predicates.
-i
Show operator ID numbers.
-l
Respect case when processing package names.
-o outfile
Output file name.
-p pname
The name of the package to be explained.
-s sectnbr
The section number to be explained within a package. To have all sections in the package explained, specify zero.
-t
Request terminal output.
-u user pw
Name and password used to connect to a database.
-h/-?
Display help information. When this option is specified, all other options are ignored, and only the help information is displayed.

Usage Notes

Package name and creator may be supplied in LIKE predicate form, which allows the percent sign (%) and the underscore (_) to be used as pattern matching characters to select multiple packages with one invocation. To prevent the percent sign and the underscore from being used as pattern matching characters, precede them with the escape character (-e option).

For example, the following command could be issued to explain the package TESTID.CALC%: 

   db2expln -c TESTID -p CALC%.

This would also, however, explain any other plans that start with CALC, such as TESTID.CALCSUM or TESTID.CALCIUM. To explain just TESTID.CALC%, use the command: 

   db2expln -c TESTID -e ! -p CALC!%

The option -e ! specifies that ! is to be used as the escape character. In the package name, !% is therefore interpreted as the percent character, not the pattern character. For more information about LIKE and escape characters, see the SQL Reference.

Users are prompted for any parameter values that are not supplied, or that are incompletely specified, except in the case of the -e, -h, -l, -p, or -w option.

If -o is specified without a file name, and -t is not specified, the user is prompted for a file name (the default name is db2expln.out). If neither -o nor -t is specified, the user is prompted for a file name (the default option is terminal output). If -o and -t are both specified, the output is directed to the terminal.

For more information about db2expln, see the Administration Guide.

See Also

db2exfmt - Explain Table Format Tool.

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

[ DB2 List of Books | Search the DB2 Books ]