DB2 10.5 for Linux, UNIX, and Windows

Guidelines for capturing explain information

Explain data can be captured by request when an SQL or XQuery statement is compiled.

If incremental bind SQL or XQuery statements are compiled at run time, data is placed in the explain tables at run time, not at bind time. For these statements, the inserted explain table qualifier and authorization ID are that of the package owner, not of the user running the package.

Explain information is captured only when an SQL or XQuery statement is compiled. After initial compilation, dynamic query statements are recompiled when a change to the environment requires it, or when the explain facility is active. If you issue the same PREPARE statement for the same query statement, the query is compiled and explain data is captured every time that this statement is prepared or executed.

If a package is bound using the REOPT ONCE or REOPT ALWAYS bind option, SQL or XQuery statements containing host variables, parameter markers, global variables, or special registers are compiled, and the access path is created using real values for these variables if they are known, or default estimates if the values are not known at compilation time.

If the REOPT ONCE option is used, an attempt is made to match the specified SQL or XQuery statement with the same statement in the package cache. Values for this already re-optimized and cached query statement will be used to re-optimize the specified query statement. If the user has the required access privileges, the explain tables will contain the newly re-optimized access plan and the values that were used for re-optimization.

In a multi-partition database system, the statement should be explained on the same database partition on which it was originally compiled and re-optimized using REOPT ONCE; otherwise, an error is returned.

Capturing information in the explain tables

Static or incremental bind SQL and XQuery statements
Specify either EXPLAIN ALL or EXPLAIN YES options on the BIND or the PREP command, or include a static EXPLAIN statement in the source program.
Dynamic SQL and XQuery statements
Explain table information is captured in any of the following cases.
- If the CURRENT EXPLAIN MODE special register is set to:
  - YES: The SQL and XQuery compiler captures explain data and executes the query statement.
  - EXPLAIN: The SQL and XQuery compiler captures explain data, but does not execute the query statement.
  - RECOMMEND INDEXES: The SQL and XQuery compiler captures explain data, and recommended indexes are placed in the ADVISE_INDEX table, but the query statement is not executed.
  - EVALUATE INDEXES: The SQL and XQuery compiler uses indexes that were placed by the user in the ADVISE_INDEX table for evaluation. In this mode, all dynamic statements are explained as though these virtual indexes were available. The query compiler then chooses to use the virtual indexes if they improve the performance of the statements. Otherwise, the indexes are ignored. To find out if proposed indexes are useful, review the EXPLAIN results.
  - REOPT: The query compiler captures explain data for static or dynamic SQL or XQuery statements during statement re-optimization at execution time, when actual values for host variables, parameter markers, global variables, or special registers are available.
If the EXPLAIN ALL option has been specified on the BIND or PREP command, the query compiler captures explain data for dynamic SQL and XQuery statements at run time, even if the CURRENT EXPLAIN MODE special register is set to NO.

Capturing explain snapshot information

When an explain snapshot is requested, explain information is stored in the SNAPSHOT column of the EXPLAIN_STATEMENT table.

Explain snapshot data is captured when an SQL or XQuery statement is compiled and explain data has been requested, as follows:

Static or incremental bind SQL and XQuery statements
An explain snapshot is captured when either the EXPLSNAP ALL or the EXPLSNAP YES clause is specified on the BIND or the PREP command, or when the source program includes a static EXPLAIN statement that uses a FOR SNAPSHOT or a WITH SNAPSHOT clause.
Dynamic SQL and XQuery statements
An explain snapshot is captured in any of the following cases.
- You issue an EXPLAIN statement with a FOR SNAPSHOT or a WITH SNAPSHOT clause. With the former, only explain snapshot information is captured; with the latter, all explain information is captured.
- If the CURRENT EXPLAIN SNAPSHOT special register is set to:
  - YES: The SQL and XQuery compiler captures explain snapshot data and executes the query statement.
  - EXPLAIN: The SQL and XQuery compiler captures explain snapshot data, but does not execute the query statement.
- You specify the EXPLSNAP ALL option on the BIND or PREP command. The query compiler captures explain snapshot data at run time, even if the CURRENT EXPLAIN SNAPSHOT special register is set to NO.