The
SET CURRENT EXPLAIN SNAPSHOT statement changes the value of the CURRENT
EXPLAIN SNAPSHOT special register.
This statement is not under transaction control.
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
.-=-.
>>-SET CURRENT EXPLAIN SNAPSHOT--+---+--+-NO------------+------><
+-YES-----------+
+-EXPLAIN-------+
+-REOPT---------+
'-host-variable-'
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.