SET CURRENT EXPLAIN MODE 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

                             .-=-.   
>>-SET CURRENT EXPLAIN MODE -+---+------------------------------>

>--+-NO----------------------+---------------------------------><
   +-YES---------------------+   
   +-EXPLAIN--+--------+-----+   
   |          '-NORCAC-'     |   
   +-REOPT-------------------+   
   +-RECOMMEND INDEXES-------+   
   +-EVALUATE INDEXES--------+   
   +-RECOMMEND PARTITIONINGS-+   
   +-EVALUATE PARTITIONINGS--+   
   '-host-variable-----------'   

Description

NO

Disables the Explain facility. No Explain information is captured. NO is the initial value of the special register.

YES

Enables the Explain facility and causes Explain information to be inserted into the Explain tables for eligible dynamic SQL statements. All dynamic SQL statements are compiled and executed normally.

EXPLAIN

Enables the Explain facility and causes Explain information to be captured for any eligible dynamic SQL statement that is prepared. However, dynamic statements are not executed.

EXPLAIN NORCAC

Enables the Explain facility and causes Explain information to be captured for any eligible dynamic SQL statement that is prepared as if row or column access control (RCAC) was not activated. Dynamic statements are not executed. When this explain mode is set, explain facility would explain the plan as if RCAC was not present.

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.

RECOMMEND INDEXES

Enables the SQL compiler to recommend indexes. All queries that are executed in this explain mode will populate the ADVISE_INDEX table with recommended indexes. In addition, Explain information will be captured in the Explain tables to reveal how the recommended indexes are used, but the statements are neither compiled nor executed.

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.

RECOMMEND PARTITIONINGS

Specifies that the compiler is to recommend the best database partition for each table that is accessed by a specific query. The best database partitions are then written to an ADVISE_PARTITION table. The query is not executed.

EVALUATE PARTITIONINGS

Specifies that the compiler is to obtain the estimated performance of a query using the virtual database partitions specified in the ADVISE_PARTITION table.

host-variable

The host-variable must be of data type CHAR or VARCHAR and the length must not exceed 254. If a longer field is provided, an error will be returned (SQLSTATE 42815). The value specified must be NO, YES, EXPLAIN, RECOMMEND INDEXES, or EVALUATE INDEXES. 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 a 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 information for static SQL statements can be captured by using the EXPLAIN option of the PREP or BIND command. If the ALL value of the EXPLAIN option is specified, and the CURRENT EXPLAIN MODE register value is NO, explain information will be captured for dynamic SQL statements at run time. If the value of the CURRENT EXPLAIN MODE register is not NO, the value of the EXPLAIN bind option is ignored.
• RECOMMEND INDEXES and EVALUATE INDEXES are special modes which can only be set with the SET CURRENT EXPLAIN MODE statement. These modes cannot be set using PREP or BIND options, and they do not work with the SET CURRENT EXPLAIN SNAPSHOT statement.
• If the Explain 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 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 an SQL statement that is already cached, the statement will be compiled and reoptimized with the current values of the input variables, and the Explain tables will be populated 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.
• A value of 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.
• Row and column level access control (RCAC) defined on the EXPLAIN tables is enforced for user access to these tables just like any other regular tables. However, row and column level access control on the EXPLAIN tables is not enforced when the database itself is populating those EXPLAIN tables. This is considered internal housekeeping and is not subject to RCAC, much like internal SQL.