The CURRENT EXPLAIN MODE special register holds a VARCHAR(254)
value which controls the behavior of the Explain facility with respect
to eligible dynamic SQL statements.
This facility generates and inserts Explain information
into the Explain tables. This information does not include the Explain
snapshot. Possible values are YES, EXPLAIN, NO, REOPT, RECOMMEND INDEXES,
and EVALUATE INDEXES. (For static SQL, the EXPLAIN bind option provides
the same control. In the case of the PREP and BIND commands, the EXPLAIN
option values are: YES, NO, and ALL.)
- YES
- Enables the Explain facility and causes Explain information for
a dynamic SQL statement to be captured when the statement is compiled.
- EXPLAIN
- Enables the facility, but dynamic statements are not executed.
- NO
- Disables the Explain facility.
- REOPT
- Enables the Explain facility and causes Explain information for
a dynamic (or incremental-bind) SQL statement to be captured only
when the statement is reoptimized using real values for the input
variables (host variables, special registers, global variables, or
parameter markers).
- RECOMMEND INDEXES
- Recommends a set of indexes for each dynamic query. Populates
the ADVISE_INDEX table with the set of indexes.
- EVALUATE INDEXES
- Enables
the SQL compiler to evaluate virtual recommended indexes for dynamic
queries. Queries executed in this explain mode will be compiled and
optimized using fabricated statistics based on the virtual indexes.
The statements are not executed. The indexes to be evaluated are read
from the ADVISE_INDEX table if the USE_INDEX column contains 'Y'.
Existing non-unique indexes can also be ignored by setting the USE_INDEX
column to 'I' and the EXISTS column to 'Y'. If a combination of USE_INDEX='I'
and EXISTS='N' is given then index evaluation for the query will continue
normally but the index in question will not be ignored.
The initial value is NO. The value can be changed by invoking
the SET CURRENT EXPLAIN MODE statement.
The CURRENT EXPLAIN MODE and CURRENT EXPLAIN SNAPSHOT
special register values interact when the Explain facility is invoked.
The CURRENT EXPLAIN MODE special register also interacts with the
EXPLAIN bind option. RECOMMEND INDEXES and EVALUATE INDEXES can only
be set for the CURRENT EXPLAIN MODE register, and must be set using
the SET CURRENT EXPLAIN MODE statement.
Example: Set the host variable EXPL_MODE (VARCHAR(254))
to the value currently in the CURRENT EXPLAIN MODE special register.
VALUES CURRENT EXPLAIN MODE
INTO :EXPL_MODE