EXPLAIN Commands

There are two explain tools available that can be run from the command line. In addition, you can use the db2exfmt to format the contents of the explain tables into a legible, organized output.

db2exfmt

• This command is used to format the contents of the explain tables into a legible, organized output.

• It is located in the instance owner /sqllib/bin directory.

To use the tool, read access for the explain tables being formatted is required.

$ db2exfmt ?d sample ?e % ?n % ?s % ?g TIC ?w ?1 ?# 0

To get the syntax and help on db2exfmt:

$ db2exfmt ?h

db2expln

• This command is used to explain SQL statements contained in packages. These include packages generated as part of the stored procedure build process.

• The statements in the packages are static SQL where the access path has already been selected and prepared through the BIND process.

• The program connects and binds itself to a database using the db2expln.bnd file the first time the database is accessed. The db2expln.bnd file is in the ../sqllib/bnd directory.

• To run db2expln, you must have SELECT privilege to the system catalog views, as well as EXECUTE authority for the db2expln package.

• When you use the dynamic options of db2expln, the statement is prepared as true dynamic SQL, and the generated plan is explained from the SQL cache.

To use db2expln, you need to specify the package name that corresponds to a given stored procedure. You can use following query to get the package name.

$ db2 "select substr(procschema,1,8),
    substr(procname,1,32),
    substr(implementation,1,8)
  from syscat.procedures"

You can then invoke db2expln on package P7597614 as follows:

$ db2expln ?d SAMPLE ?c tphan ?p P7597614 ?s 0 ?o db2expln_P7597614.rpt

Alternatively, to explain all the packages of schema tphan, you can use the wild card character %:

$ db2expln ?d SAMPLE ?c tphan ?p % ?s 0 ?o db2expln_all_packages.rpt

To explain a dynamic statement contained in the file stmt1.sql:

$ db2expln ?d SAMPLE ?stmtfile stmt1.sql ?terminator @
  ?o db2expln_stmt1.rpt

To get the syntax and help on db2expln:

$ db2expln ?h

dynexpln

• The dynexpln tool is still available for backward compatibility. It is recommended to use the dynamic options of db2expln to perform all of the functions of dynexpln.

• It is used to explain SQL statements that are not embedded and bound into packages. The access path is being determined dynamically during execution time.

• The statements are dynamic SQL.

• To run dynexpln, you must have BINDADD authority for the database, as well as any privileges needed for the SQL statements being explained.

To use dynexpln, the preferred approach is to create a file and code the SQL statements, which need to be explained in the file. Assuming the file is stmt1.sql, the dynexpln can then be invoked as follows:

$ dynexpln ?d SAMPLE ?f stmt1.sql ?o dynexpln_stmt1.out

As an alternative, an SQL statement can be explained inline:

$ dynexpln ?d SAMPLE ?o dynexpln_stmt1.out
           ?q "select acct_type from dnpnck.account_xref"

You can also invoke dynexpln interactively where you will be prompted to supply required parameters:

$ dynexpln

To get the syntax and help on db2expln:

$ dynexpln ?h