DB2 10.5 for Linux, UNIX, and Windows

EXPLAIN statement

The EXPLAIN statement captures information about the access plan chosen for the supplied explainable statement and places this information into the explain tables.

An explainable statement can either be a valid XQuery statement or one of the following SQL statements: CALL, Compound SQL (Dynamic), DELETE, INSERT, MERGE, REFRESH, SELECT, SELECT INTO, SET INTEGRITY, UPDATE, VALUES, or VALUES INTO.

Invocation

This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared.

The statement to be explained is not executed.

Authorization

The authorization ID of the statement must hold at least one of the following authorizations:

Syntax

Read syntax diagramSkip visual syntax diagram
>>-EXPLAIN--+-PLAN SELECTION-+--+--------------------+---------->
            +-ALL------------+  '-+-FOR--+--SNAPSHOT-'   
            |      (1)       |    '-WITH-'               
            '-PLAN-----------'                           

>--+-----------------+--+------------------------+-------------->
   '-WITH REOPT ONCE-'  '-SET QUERYNO = -integer-'   

>--+---------------------------------+-------------------------->
   '-SET QUERYTAG = -string-constant-'   

>--FOR--+-explainable-sql-statement--------------+-------------><
        '-XQUERY--'explainable-xquery-statement'-'   

Notes:
  1. The PLAN option is supported only for syntax toleration of existing DB2® for z/OS® EXPLAIN statements. There is no PLAN table. Specifying PLAN is equivalent to specifying PLAN SELECTION.

Description

PLAN SELECTION
Indicates that the information from the plan selection phase of query compilation is to be inserted into the explain tables.
ALL
Specifying ALL is equivalent to specifying PLAN SELECTION.
PLAN
The PLAN option provides syntax toleration for existing database applications from other systems. Specifying PLAN is equivalent to specifying PLAN SELECTION.
FOR SNAPSHOT
This clause indicates that only an explain snapshot is to be taken and placed into the SNAPSHOT column of the EXPLAIN_STATEMENT table. No other explain information is captured other than that present in the EXPLAIN_INSTANCE and EXPLAIN_STATEMENT tables.
WITH SNAPSHOT
This clause indicates that, in addition to the regular explain information, an explain snapshot is to be taken.

The default behavior of the EXPLAIN statement is to only gather regular explain information and not the explain snapshot.

default (neither FOR SNAPSHOT nor WITH SNAPSHOT specified)
Puts explain information into the explain tables.
WITH REOPT ONCE
This clause indicates that the specified explainable statement is to be reoptimized using the values for host variables, parameter markers, special registers, or global variables that were previously used to reoptimize this statement with REOPT ONCE. The explain tables will be populated with the new access plan. If the user has DBADM authority, or the database registry variable DB2_VIEW_REOPT_VALUES is set to YES, the EXPLAIN_PREDICATE table will also be populated with the values if they are used to reoptimize the statement.
SET QUERYNO = integer
Associates integer, via the QUERYNO column in the EXPLAIN_STATEMENT table, with the explainable statement. The integer value supplied must be a positive value.

If this clause is not specified for a dynamic EXPLAIN statement, a default value of one (1) is assigned. For a static EXPLAIN statement, the default value assigned is the statement number assigned by the precompiler.

SET QUERYTAG = string-constant
Associates string-constant, via the QUERYTAG column in the EXPLAIN_STATEMENT table, with the explainable statement. string-constant can be any character string up to 20 bytes in length. If the value supplied is less than 20 bytes in length, the value is padded on the right with blanks to the required length.

If this clause is not specified for an EXPLAIN statement, blanks are used as the default value.

FOR explainable-sql-statement
Specifies the SQL statement to be explained. This statement can be any valid CALL, Compound SQL (Dynamic), DELETE, INSERT, MERGE, REFRESH, SELECT, SELECT INTO, SET INTEGRITY, UPDATE, VALUES, or VALUES INTO SQL statement. If the EXPLAIN statement is embedded in a program, the explainable-sql-statement can contain references to host variables (these variables must be defined in the program). Similarly, if EXPLAIN is being dynamically prepared, the explainable-sql-statement can contain parameter markers.

The explainable-sql-statement must be a valid SQL statement that could be prepared and executed independently of the EXPLAIN statement. It cannot be a statement name or host variable. SQL statements referring to cursors defined through CLP are not valid for use with this statement.

To explain dynamic SQL within an application, the entire EXPLAIN statement must be dynamically prepared.

FOR XQUERY 'explainable-xquery-statement'
Specifies the XQUERY statement to be explained. This statement can be any valid XQUERY statement.

If the EXPLAIN statement is embedded in a program, the 'explainable-xquery-statement' can contain references to host variables, provided that the host variables are not used in the top level XQUERY statement, but are passed in through an XMLQUERY function, by an XMLEXISTS predicate, or by an XMLTABLE function. The host variables must be defined in the program.

Similarly, if EXPLAIN is being dynamically prepared, the 'explainable-xquery-statement' can contain parameter markers, provided that the same restrictions as for passing host variables are followed.

Alternatively, the DB2 XQUERY function db2-fn:sqlquery can be used to embed SQL statements with references to host variables and parameter markers.

The 'explainable-xquery-statement' must be a valid XQUERY statement that could be prepared and executed independently of the EXPLAIN statement. Query statements referring to cursors defined through CLP are not valid for use with this statement.

Notes

Examples