Plan Table report

The Plan Table report helps the Db2® application designer and programmer understand the access path that was selected by Db2 for a given SQL statement.

The Plan Table report displays information that is produced by the SQL EXPLAIN statement and additional relevant Db2 catalog data.

The Plan Table report has been fully integrated into the TSO ISPF interface of Db2 SQL Performance Analyzer. The Plan Table report can also be generated in a batch job.

Information that is produced by the SQL EXPLAIN function is useful in application design and tuning. However, the SQL EXPLAIN function information is not sufficient to determine and understand the access path that is chosen by Db2 and documented by the EXPLAIN function.

Typically, the application designer and programmer must extract Db2 catalog information to understand and validate the chosen access path.

The main purpose of the Plan Table report is to combine information that is returned by the SQL EXPLAIN statement and information that is extracted from the Db2 catalog and to document that data in an understandable way.

The Plan Table report can be generated for a query number of a previous EXPLAIN operation, the name of a package that was bound with the EXPLAIN option, a QMF query, or any Data Manipulation Language (DML) statement. For a QMF query or a DML statement, the statement is explained and an explanation of the selected access path is provided. If the package was not bound with the EXPLAIN option, a dynamic EXPLAIN of the DML SQL statements in the package is performed.

Using current EXPLAIN statement data as input to the SQL Performance Analyzer cost analysis provides you the opportunity to ensure that the Db2 catalog represents an accurate view of this application environment. For an application in the early stages of development, that view could be the projected production environment or it could represent a new indexing option that is being considered to boost the performance of an existing production application.