SET CURRENT EXPLAIN SNAPSHOT statement

Invocation

This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared.

Authorization

None required.

Syntax

Description

NO

Disables the Explain snapshot facility. No snapshot is taken. NO is the initial value of the special register.

YES

Enables the Explain snapshot facility, creating a snapshot of the internal representation for each eligible dynamic SQL statement. This information is inserted in the SNAPSHOT column of the EXPLAIN_STATEMENT table.

EXPLAIN

Enables the Explain snapshot facility, creating a snapshot of the internal representation for each eligible dynamic SQL statement that is prepared. However, dynamic statements are not executed.

REOPT

Enables the Explain facility and causes Explain information to be captured for a static or dynamic SQL statement during statement reoptimization at execution time; that is, when actual values for the host variables, special registers, global variables, or parameter markers are available.

host-variable

The host-variable must be of data type CHAR or VARCHAR and the length of its contents must not exceed 8. If a longer field is provided, an error will be returned (SQLSTATE 42815). The value contained in this register must be either NO, YES, or EXPLAIN. If the actual value provided is larger than the replacement value specified, the input must be padded on the right with blanks. Leading blanks are not allowed (SQLSTATE 42815). All input values are treated as being case-insensitive. If host-variable has an associated indicator variable, the value of that indicator variable must not indicate a null value (SQLSTATE 42815).

Notes

• The Explain facility uses the following IDs as the schema when qualifying Explain tables that it is populating:
  • The session authorization ID for dynamic SQL
  • The statement authorization ID for static SQL
  The schema can be associated with a set of Explain tables, or aliases that point to a set of Explain tables under a different schema. If no Explain tables are found under the schema, the Explain facility checks for Explain tables under the SYSTOOLS schema and attempts to use those tables.
• Explain snapshots for static SQL statements can be captured by using the EXPLSNAP option of the PREP or BIND command. If the ALL value of the EXPLSNAP option is specified, and the CURRENT EXPLAIN SNAPSHOT register value is NO, Explain snapshots will be captured for dynamic SQL statements at run time. If the value of the CURRENT EXPLAIN SNAPSHOT register is not NO, the EXPLSNAP option is ignored.
• If the Explain snapshot facility is activated, the current authorization ID must have INSERT privilege for the Explain tables or an error (SQLSTATE 42501) is raised.
• When SQL statements are explained from a routine, the routine must be defined with an SQL data access indicator of MODIFIES SQL DATA (SQLSTATE 42985).
• If the special register is set to REOPT, and the SQL statement does not qualify for reoptimization at execution time (that is, if the statement does not have input variables, or if the REOPT bind option is set to NONE), then no Explain information will be captured. If the REOPT bind option is set to ONCE, Explain snapshot information will be captured only once when the statement is initially reoptimized. After the statement is cached, no further Explain information will be acquired for this statement on subsequent executions.
• If the Explain facility is enabled, the REOPT bind option is set to ONCE, and you attempt to execute a reoptimizable SQL statement that is already cached, the statement will be compiled and reoptimized with the current values of the input variables, and the Explain snapshot will be captured accordingly. The newly generated access plan for this statement will not be cached or executed. Other applications that are concurrently executing this cached statement will continue to execute, and new requests to execute this statement will pick up the already cached access plan.
• The value REOPT for the CURRENT EXPLAIN MODE and CURRENT EXPLAIN SNAPSHOT special registers will override the value of the EXPLAIN and EXPLSNAP bind options at bind time if a static or dynamic SQL statement has input variables, and the REOPT bind option is set to ONCE or ALWAYS.

Examples

• Example 1:  The following statement sets the CURRENT EXPLAIN SNAPSHOT special register, so that an Explain snapshot will be taken for any subsequent eligible dynamic SQL statements and the statement will be executed.

     SET CURRENT EXPLAIN SNAPSHOT = YES

• Example 2:  The following example retrieves the current value of the CURRENT EXPLAIN SNAPSHOT special register into the host variable called SNAP.

     EXEC SQL VALUES (CURRENT EXPLAIN SNAPSHOT) INTO :SNAP;