CURRENT EXPLAIN MODE

This facility generates and inserts EXPLAIN information into the EXPLAIN tables. Possible values for the CURRENT EXPLAIN MODE special register are YES, NO, and EXPLAIN. The data type is VARCHAR(128).

NO

Disable the ability to use EXPLAIN. No EXPLAIN information is kept. NO is the initial value of the EXPLAIN MODE special register.

YES

Enables the EXPLAIN facility and causes EXPLAIN information to be inserted into the EXPLAIN tables for eligible dynamic SQL statements after the statement is prepared and executed. 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 after the statement is prepared. This setting behaves similarly to YES, however, dynamic statements, except for SET statements, are not executed.

For values YES and EXPLAIN, prepared statements are not saved into the dynamic statement cache.

The initial value is NO. The initial value of CURRENT EXPLAIN MODE in a user-defined function or stored procedure is inherited according to the rules in Special registers in a user-defined function or a stored procedure.

The value can be changed using the SET CURRENT EXPLAIN MODE statement.

Prerequisites for using CURRENT EXPLAIN MODE:

• Both the PLAN_TABLE and DSN_STATEMENT_CACHE_TABLE exist on the DB2® server and the table names are qualified with the current SQLID that is used when running the application.
• The Dynamic statement cache is enabled.
• The client application contains some explainable statements.

Required authorization for using CURRENT EXPLAIN MODE:

If CURRENT EXPLAIN MODE is set to YES or EXPLAIN, the privilege set for the underlying statement must have the necessary authorization to use the EXPLAIN facility.

When the EXPLAIN privilege is in effect and CURRENT EXPLAIN MODE is set to EXPLAIN, any SQLCODE that is returned due to the EXPLAIN privilege override any SQLCODE that is returned due to CURRENT EXPLAIN MODE being set to EXPLAIN.

Considerations when using the CURRENT EXPLAIN MODE special register to explain the acceleration of rowset queries:

A rowset query cannot be passed to an accelerator server for processing in the following cases:

• If the rowset query is run remotely
• If the rowset query is declared WITH RETURN
• If the rowset query is run under an SQL PL routine

When a rowset query is run with EXPLAIN MODE YES or EXPLAIN MODE EXPLAIN, the EXPLAIN function indicates whether the local execution of a rowset cursor can be accelerated, depending on the content of the query. The EXPLAIN function will always indicate that remote rowset queries, rowset queries that are declared WITH RETURN, and rowset queries that run under an SQL PL routine cannot be accelerated.