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 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 refer to the SQL Reference manual. For more information 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:

  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 refer to the SQL Reference manual. For more information 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.

[ DB2 List of Books | Search the DB2 Books ]