db2exfmt - Explain table format command

You use the db2exfmt tool to format the contents of the EXPLAIN tables. This tool is located in the misc subdirectory of the instance sqllib directory. This tool uses the statistics from the EXPLAIN snapshot, if the snapshot is available.

Authorization

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

Command syntax

>>-db2exfmt--+-----+--+-------------+--+-------------+---------->
             '- -1-'  '- -d--dbname-'  '- -e--schema-'   

>--+--------+--+---------------------------+--+-----+----------->
   '- -f--O-'  |             .-----------. |  '- -l-'   
               |             V           | |            
               '- -g--+---+----+-------+-+-'            
                      '-x-'    +-O-----+                
                               +-I-----+                
                               +-C-----+                
                               '-+-T-+-'                
                                 '-F-'                  

                                                     .- -t-.   
>--+-----------+--+--------------+--+-------------+--+-----+---->
   '- -n--name-'  '- -o--outfile-'  '- -s--schema-'            

>--+-----------------------+--+----------------+---------------->
   '- -u--userID--password-'  '- -w--timestamp-'   

>--+--------------+--+--------------+--+-----+-----------------><
   '- -#--sectnbr-'  '- -v--srcvers-'  '- -h-'   

Command parameters

db2exfmt

If no options are specified, then the command enters interactive mode and you will be prompted to make entries.

-1

Use defaults -e % -n % -s % -v % -w -1 -# 0

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

-d dbname

Name of the database containing packages.

-e schema

Explain table SQL schema.

-f

Formatting flags. In this release, the only supported value is O (operator summary).

-g

Graph plan.

x

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:

O

Generate a graph only. Do not format the table contents.

T

Include total cost under each operator in the graph.

F

Include first tuple cost in graph.

I

Include I/O cost under each operator in the graph.

C

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.

-l

Respect case when processing package names.

-n name

Name of the source of the explain request (SOURCE_NAME).

-s schema

SQL schema or qualifier of the source of the explain request (SOURCE_SCHEMA).

-o outfile

Output file name.

-t

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

Source version of source of Explain request (default %)

-h

Display help information. When this option is specified, all other options are ignored, and only the help information is displayed.

Usage notes

You will be 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, the user is prompted for an explain table SQL schema.

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 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.

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

db2 explain plan with snapshot for query
db2exfmt

db2 set current explain mode yes
db2 set current explain snapshot yes
run the query
db2exfmt