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