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.
>>-db2exfmt--+-----+--+-------------+--+-------------+----------> '- -1-' '- -d--dbname-' '- -e--schema-' >--+------------+--+---------------------------+--+-----+-------> '- -f--+-O-+-' | .-----------. | '- -l-' +-Y-+ | V | | '-C-' '- -g--+---+----+-------+-+-' '-x-' +-O-----+ +-I-----+ +-C-----+ '-+-T-+-' '-F-' >--+-----------+--+-------------+--+------------------+---------> '- -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-'
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.
Any combination of these options is allowed, except F and T, which are mutually exclusive.
Both the user ID and password must be valid according to naming conventions and be recognized by the database.
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.
db2 explain plan with snapshot for query
db2exfmt
or, db2 set current explain mode yes
db2 set current explain snapshot yes
query
db2exfmt
db2 "CALL SET_ROUTINE_OPTS('EXPLAIN YES')"
db2 "CALL REBIND_ROUTINE_PACKAGE('T', 'PARAMNT', '', 'TRIg2', '')"
db2exfmt -d MYDB -rt T -s PARAMNT -n \"TRIg2\" -no_prompt
db2exfmt -d MYDB -rt T -s PARAMNT -n \"TRIg2\" -#4 -no_prompt