SQL Reference

CURRENT EXPLAIN MODE

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 (for more information see the Administration Guide). This information does not include the Explain snapshot.

The possible values are YES, NO, EXPLAIN, RECOMMEND INDEXES and EVALUATE INDEXES. ¹⁹

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 like YES, however, the dynamic statements are not executed.

NO

Disables the Explain facility.

RECOMMEND INDEXES

For each dynamic query, a set of indexes is recommended. ADVISE_INDEX table is populated with the set of indexes.

EVALUATE INDEXES

Dynamic queries are explained as if the recommended indexes existed. The indexes are picked up from ADVISE_INDEX table.

The initial value is NO.

Its value can be changed by the SET CURRENT EXPLAIN MODE statement (see SET CURRENT EXPLAIN MODE for information on this statement).

The CURRENT EXPLAIN MODE and CURRENT EXPLAIN SNAPSHOT special register values interact when the Explain facility is invoked (see Table 142 for details). The CURRENT EXPLAIN MODE special register also interacts with the EXPLAIN bind option (see Table 143 for details). The values 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

¹⁹

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.