DB2 10.5 for Linux, UNIX, and Windows

db2exfmt - Explain table format command

Formats the contents of the EXPLAIN tables.

This tool is in the misc subdirectory of the instance sqllib directory. This tool uses the statistics from the EXPLAIN snapshot, if the snapshot is available.


To use the tool, you require read access to the explain tables being formatted.

Command syntax

Read syntax diagramSkip visual syntax diagram
             '- -1-'  '- -d--dbname-'  '- -e--schema-'   

   '- -f--+-O-+-'  |             .-----------. |  '- -l-'   
          +-Y-+    |             V           | |            
          '-C-'    '- -g--+---+----+-------+-+-'            
                          '-x-'    +-O-----+                

   '- -n--name-'  '- -s--schema-'  '- -m--module_name-'   

                                            .- -t-.   
   '- -ot--object_type-'  '- -o--outfile-'            

   '- -u--userID--password-'  '- -w--timestamp-'   

   '- -no_map_char-'  '- -no_prompt-'  '- -runstats-'   

   '- -#--sectnbr-'  '- -v--srcvers-'  '- -h-'   

Command parameters

If no options are specified, then the command enters interactive mode and you are prompted to make entries.
Use defaults -e % -n % -s % -v % -w -1 -# 0

If Explain schema is not supplied, the contents of the environment variable $USER, or $USERNAME is used as a default. If this variable is not found, you are prompted for an Explain schema.

-d dbname
Name of the database containing packages.
-e schema
Explain table SQL schema.
Formatting flags. Multiple flags can be combined together as a string. For example, to achieve an output similar to the previous versions of DB2® database products, the C option and Y option can be combined as -f CY.
Operator summary.
Force formatting of the original statement even if column EXPLAIN_STATEMENT.EXPLAIN_TEXT contains formatting. The default behavior is to automatically detect if the statement requires formatting and use the original formatting when it exists.
Use a more compact mode when formatting statements and predicates. The default is an expanded mode that is easier to read. If Y is not specified then C takes effect only if automatic detection determines that the statement requires formatting.
Graph plan.
Turn OFF options (default is to turn them ON).
If only -g is specified, a graph, followed by formatted information for all of the tables, is generated. Otherwise, any combination of the following valid values can be specified:
Generate a graph only. Do not format the table contents.
Include total cost under each operator in the graph.
Include first tuple cost in graph.
Include I/O cost under each operator in the graph.
Include the expected output cardinality (number of tuples) of each operator in the graph.

Any combination of these options is allowed, except F and T, which are mutually exclusive.

Respect case when processing package names.
-m module_name
Module name of the routine when the -ot option is P, SP, F, or SF. Module routines are ignored if this parameter is not specified. This parameter is case sensitive.
-n name
Package name (SOURCE_NAME) or object name for the explain request. Package is assumed if the -ot is not specified. This parameter is case sensitive.
Prevents the mapping of non-printable single characters to '.'. This mapping can happen for multi-byte characters where a single byte is non-printable and for non-printable binary characters.
Prevents db2exfmt from prompting for missing input options. The default value for missing inputs is used.
-s schema
Package schema (SOURCE_SCHEMA) of the package request. If a package schema is not specified, this option is set to '%'. If the object type is a procedure, function, or trigger, this is the schema of the associated object. If the object type is not a procedure, function, or trigger, the schema is set to the value of the CURRENT SCHEMA special register. When a module is provided for the procedure or function, then this option corresponds to the module schema. This parameter is case sensitive.
Type of the object specified with the -n option. The default type is package.
Package name
SQL procedure name
A specific SQL procedure name
Compiled function
A specific compiled function name
Compiled trigger
Forces runstats to execute on the explain tables. You do not need to specify this option if automatic statistics collection is enabled.
-o outfile
Output file name.
Direct the output to the terminal.
-u userID 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.

-w timestamp
Explain time stamp. Specify -1 to obtain the latest explain request.
-# sectnbr
Section number in the source. To request all sections, specify zero.
-v srcvers
Package version (SOURCE_VERSION) of the explain request. The default value is %.
Display help information. When this option is specified, all other options are ignored, and only the help information is returned.

Usage notes

You are prompted for any parameter values that are not supplied, or that are incompletely specified, except in the case of the -h and the -l options.

If an explain table SQL schema is not provided, the value of the environment variable USER is used as the default. If this variable is not found, you are prompted for an explain table SQL schema.

When you are formatting explain information using a package name, the source name, source SQL schema, and explain time stamp can be supplied in LIKE predicate form, which allows the percent sign (%) and the underscore (_) to be used as pattern matching characters to select multiple sources with one invocation. For the latest explained statement, the explain time can be specified as -1.

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 db2exfmt.out). If -o or -t is not specified, you are 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.

The db2exfmt command returns the statistics from the EXPLAIN snapshot, if the snapshot is available. Otherwise, db2exfmt returns statistics stored in the EXPLAIN_OBJECT table and also returns some statistics retrieved directly from the system catalog.

The per-partition usage, transferrate and prefetchsize values returned are retrieved when the db2exfmt command is run. Therefore, the values can differ from the actual values used when the statement was explained.

OVERHEAD, TRANSFERRATE, and PREFETCHSIZE values returned in the db2exfmt output can differ from the actual values used when the statement was compiled.

When a procedure, function, or compiled trigger type is specified, every section available in the explain tables pertaining to that routine is formatted. If a procedure or function is overloaded, you must provide the specific name of the procedure or function.


EXPLAIN snapshot examples.
db2 explain plan with snapshot for query
db2 set current explain mode yes
db2 set current explain snapshot yes
Explain all sections in a compiled trigger PARAMNT."TRIg2" and format section number 4:
db2exfmt -d MYDB -rt T -s PARAMNT -n \"TRIg2\" -no_prompt
db2exfmt -d MYDB -rt T -s PARAMNT -n \"TRIg2\" -#4 -no_prompt