Explaining SQL statements

An Explain takes one of the following forms:

• A dynamic Explain that asks the Db2 optimizer for an explanation of the access path. This process evaluates access paths, SQL text, and key catalog statistics in real time.
• A static Explain from a bind with EXPLAIN(YES). This process reports the access path information that was derived when you performed a bind with EXPLAIN(YES).
• An Explain Statement Cache on a dynamic SQL statement. This process performs an EXPLAIN STMTCACHE STMTID on a dynamic SQL statement that was executed and is still in the statement cache. The access path used at the time that the statement was executed is retrieved from the statement cache and reported on.
• An Explain Package, in which the current access path for a static SQL statement is retrieved and reported on even if a BIND with EXPLAIN(YES) was not previously performed.

The resulting Explain plan is the hierarchical representation of rows extracted from the Db2 plan table.

To Explain an SQL statement

1. Use any of the following methods to select a SQL statement as the baseline statement:

   • Analyzing-a-statement-from-the-Db2-statement-cache
   • Analyzing-a-statement-from-a-Db2-package
   • Identifying-performance-issues-in-SQL-statements
   • Analyzing-an-ad-hoc-statement

   The Performance perspective displays the SQL statement and its parameters. For more information, see Explain-tab.

2. Click Generate explain plan .The Explain results are displayed along with the following tabs:
   • SQL
   • Indexes
   • Explain
   • Cost
   • Predicates
3. (BMC.DB2.SPE2304) (Optional) To export the explain plan to a PDF file, perform the following steps:
   1. Click Generate Report .
      Based on your browser settings, after Command Center generates the report, either the Save As dialog box is displayed or the browser saves the file automatically.
   2. Enter a name for the file.
   3. Click Save.