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 or incremental bind SQL statements, explain table information is captured when either EXPLAIN ALL or EXPLAIN YES options are specified on the BIND or the PREP commands; or, a static EXPLAIN SQL statement is used in the source program.

  Note:

  When incremental bind SQL statements are compiled at run-time, they are placed in the explain tables at run-time and not bind-time. Also, the explain table qualifier and authorization ID used for the insert to the explain tables is that of the package owner and not that of the use running the package.

• For dynamic SQL statements, explain table information is 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 causes 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 causes the SQL compiler to capture explain data, but does not execute the SQL statement.
  • The CURRENT EXPLAIN MODE special register is set to RECOMMEND INDEXES. This setting causes 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 causes the SQL compiler to use indexes placed by the user in the ADVISE_INDEX table. The user inserts a new row for each index that should be 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, the special register, CURRENT EXPLAIN MODE should be set to EVALUATE INDEXES. Then the SQL compiler scans the ADVISE_INDEX table where the field USE_INDEX is set to "Y" (these are called virtual indexes). All dynamic statements executed in EVALUATE INDEXES 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 the user were used by the SQL compiler. Those that were used should be considered to be implemented to improve access.
  • The EXPLAIN ALL option has been specified on the BIND or PREP command. This setting causes 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 also executes, 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 or incremental bind SQL statements, explain snapshot is captured when either EXPLSNAP ALL or EXPLSNAP YES clauses are specified on the BIND or the PREP commands; or, a static EXPLAIN SQL statement that uses a FOR SNAPSHOT or a WITH SNAPSHOT clause is used in the source program.

  Note:

  When incremental bind SQL statements are compiled at run-time, they are placed in the explain tables at run-time and not bind-time. Also, the explain table qualifier and authorization ID used for the insert to the explain tables is that of the package owner and not that of the use running the package.

• For dynamic SQL statements, an explain snapshot is 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 causes the SQL compiler to take a snapshot of explain data and allows the SQL statement to execute, returning the results of the query.
  • The CURRENT EXPLAIN SNAPSHOT special register is set to EXPLAIN. This setting causes the SQL compiler to take a snapshot of explain data, but does not execute the SQL statement.
  • The EXPLSNAP ALL option has been specified on the BIND or PREP command. This setting causes 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:

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