Administration Guide

Obtaining Explain Data

Before you can obtain explain data for an SQL statement, you must have a set of explain tables defined using the same schema as the authorization ID that invokes the explain facility. See Table Definitions for Explain Tables for information on how to create the tables.

Capturing Explain Table Information

Once these tables are defined, explain data is captured when an SQL statement is compiled and explain data has been requested:

For static SQL statements, explain table information will be captured when either EXPLAIN ALL or EXPLAIN YES options are specified on the BIND or PREP command; or, a static EXPLAIN SQL statement is used in the source program.

For dynamic SQL statements, explain table information will be captured for any of the following situations:

An EXPLAIN SQL statement. All explain information is captured and placed in the explain tables unless the FOR SNAPSHOT clause is used.
An example of an EXPLAIN SQL statement:
```
   EXPLAIN PLAN FOR <any valid DELETE, INSERT, SELECT, SELECT INTO,
      UPDATE, VALUES, or VALUES INTO SQL statement>
```
The CURRENT EXPLAIN MODE special register is set to YES. This setting will cause the SQL compiler to capture explain data and allow the SQL statement to execute, returning the results of the query.
The CURRENT EXPLAIN MODE special register is set to EXPLAIN. This setting will cause the SQL compiler to capture explain data, but will not execute the SQL statement.
The CURRENT EXPLAIN MODE special register is set to RECOMMEND INDEXES. This setting will cause the SQL compiler to capture explain data and the recommended indexes to be placed in the ADVISE_INDEX table; however, the SQL statement is not executed.
The CURRENT EXPLAIN MODE special register is set to EVALUATE INDEXES. This setting will cause the SQL compiler to use indexes placed in the ADVISE_INDEX table by the you. The user inserts a new row for each index they want evaluated. The required information for each index is: index name, table name, and the columns names that make up the index being evaluated. Once entered, you set the special register. Then the SQL compiler scans the ADVISE_INDEX table where the field USE_INDEX="Y" and assume those indexes exist. All dynamic statements executed in that mode are explained as if these virtual indexes were available. The SQL compiler then chooses to use the virtual indexes if they improve the performance of the statements. Otherwise, the indexes are ignored. By reviewing the EXPLAIN results, you can see if the indexes proposed by you were used by the SQL compiler. If they were used, then this is the indication that you have proposed an index that should be considered to be implemented to improve access to those tables.
The EXPLAIN ALL option has been specified on the BIND or PREP command. This setting will cause the SQL compiler to capture explain data for dynamic SQL at run-time, even if the setting of the CURRENT EXPLAIN MODE special register is NO. The SQL statement will also execute, returning the results of the query.

Note:

Explain information is only captured when the SQL statement is compiled. Following the initial compilation, dynamic SQL statements are only recompiled when a change to the environment requires the statement be recompiled. If the same PREPARE statement is issued consecutively for the same SQL statement, the SQL statement will only be compiled, and explain data captured, the first time the PREPARE statement is issued, assuming the environment does not change.

For more information about using the EXPLAIN SQL statement or about using the CURRENT EXPLAIN MODE registers, refer to the SQL Reference manual. For more information about the BIND and PREP commands, refer to the Command Reference manual.

Capturing Explain Snapshot Information

Explain snapshot data is captured when an SQL statement is compiled and explain data has been requested:

For static SQL statements, an explain snapshot will be captured when either EXPLSNAP ALL or EXPLSNAP YES options are specified on the BIND or PREP command; or, a static EXPLAIN SQL statement, using a FOR SNAPSHOT or WITH SNAPSHOT clause, is used in the source program.

For dynamic SQL statements, an explain snapshot will be captured in any of the following situations:

An EXPLAIN SQL statement using a FOR SNAPSHOT or a WITH SNAPSHOT clause. The FOR SNAPSHOT clause has no explain table information captured except the information associated with explain snapshot. The WITH SNAPSHOT clause has all explain table information captured in addition to the information associated with explain snapshot.
An example of an explain snapshot using the EXPLAIN SQL statement:
```
   EXPLAIN PLAN FOR SNAPSHOT FOR <any valid DELETE, INSERT, SELECT,
      SELECT INTO, UPDATE, VALUES, or VALUES INTO SQL statement>
```
Only an explain snapshot is taken and the captured information is placed in the EXPLAIN_INSTANCE and EXPLAIN_STATEMENT tables.
The CURRENT EXPLAIN SNAPSHOT special register is set to YES. This setting will cause the SQL compiler to take a snapshot of explain data and allow the SQL statement to execute, returning the results of the query.
The CURRENT EXPLAIN SNAPSHOT special register is set to EXPLAIN. This setting will cause the SQL compiler to take a snapshot of explain data, but will not execute the SQL statement.
The EXPLSNAP ALL option has been specified on the BIND or PREP command. This setting will cause the SQL compiler to take a snapshot of explain data at run-time, even if the setting of the CURRENT EXPLAIN SNAPSHOT special register is NO. The SQL statement will also execute, returning the results of the query.

Note:

For more information about using the EXPLAIN SQL statement and the FOR SNAPSHOT or WITH SNAPSHOT clauses, or about using the CURRENT EXPLAIN SNAPSHOT registers, refer to the SQL Reference manual. For more information about the BIND and PREP commands, refer to the Command Reference manual.

[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]

[ DB2 List of Books | Search the DB2 Books ]