EXPLAIN statement

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

• DATAACCESS authority which allows an INSERT, UPDATE, DELETE, or SELECT statement.
• DATAACCESS authority on the schema containing the explain tables
• INSERT privilege on the explain tables and at least one of the following authorizations:
  • All the privileges that are necessary to execute the explainable statement that is specified in the EXPLAIN statement (for example, if a DELETE statement is used as the explainable statement, the authorization rules for the DELETE statement are applied when the DELETE statement is explained)
  • EXPLAIN authority
  • SQLADM authority
  • DBADM authority

Syntax

Notes:

• ¹ 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

• 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.
• The following table shows the interaction of the snapshot keywords and the explain information.

  Keyword Specified	Capture Explain Information?
  none	Yes
  FOR SNAPSHOT	No
  WITH SNAPSHOT	Yes

  If neither the FOR SNAPSHOT nor the WITH SNAPSHOT clause is specified, an explain snapshot is not taken.
• The explain tables must be created by the user before invocation of the EXPLAIN statement. The information generated by this statement is stored in the explain tables, in the schema that is designated at the time the statement is compiled.
• If any errors occur during the compilation of the explainable statement supplied, then no information is stored in the explain tables.
• The access plan generated for the explainable statement is not saved and thus, cannot be invoked at a later time. The explain information for the explainable statement is inserted when the EXPLAIN statement itself is compiled.
• For a static EXPLAIN query statement, the information is inserted into the explain tables at bind time and during an explicit rebind. During precompilation, the static EXPLAIN statements are commented out in the modified application source file. At bind time, the EXPLAIN statements are stored in the SYSCAT.STATEMENTS catalog. When the package is run, the EXPLAIN statement is not executed. Note that the section numbers for all statements in the application will be sequential and will include the EXPLAIN statements. An alternative to using a static EXPLAIN statement is to use a combination of the EXPLAIN and EXPLSNAP BIND or PREP options. Static EXPLAIN statements can be used to cause the explain tables to be populated for one specific static query statement out of many; simply prefix the target statement with the appropriate EXPLAIN statement syntax and bind the application without using either of the explain BIND or PREP options. The EXPLAIN statement can also be used when it is advantageous to set the QUERYNO or QUERYTAG field at the time of the actual explain invocation.
• Static EXPLAIN statements in an SQL procedure are evaluated when the procedure is compiled.
• For an incremental bind EXPLAIN query statement, the explain tables are populated when the EXPLAIN statement is submitted for compilation. When the package is run, the EXPLAIN statement performs no processing (though the statement will be successful). When populating the explain tables, the explain table qualifier and authorization ID used during population will be those of the package owner. The EXPLAIN statement can also be used when it is advantageous to set the QUERYNO or QUERYTAG field at the time of the actual explain invocation.
• For dynamic EXPLAIN statements, the explain tables are populated at the time the EXPLAIN statement is submitted for compilation. An EXPLAIN statement can be prepared with the PREPARE statement but, if executed, will perform no processing (though the statement will be successful). An alternative to issuing dynamic EXPLAIN statements is to use a combination of the CURRENT EXPLAIN MODE and CURRENT EXPLAIN SNAPSHOT special registers to explain dynamic query statements. The EXPLAIN statement should be used when it is advantageous to set the QUERYNO or QUERYTAG field at the time of the actual EXPLAIN invocation.
• If the REOPT bind option is set to ONCE, and either the CURRENT EXPLAIN MODE or the CURRENT EXPLAIN SNAPSHOT special register is set to REOPT, the execution of static and dynamic query statements containing host variables, special registers, parameter markers, or global variables will cause explain information to be captured for the statement only when the statement is reoptimized. Alternatively, if the REOPT bind option is set to ALWAYS, explain information will be captured every time these statements are executed.

Examples

• Example 1: Explain a simple SELECT statement and tag with QUERYNO = 13.

     EXPLAIN PLAN SET QUERYNO = 13
       FOR SELECT C1
       FROM T1

• Example 2: Explain a simple SELECT statement and tag with QUERYTAG = 'TEST13'.

     EXPLAIN PLAN SELECTION SET QUERYTAG = 'TEST13'
       FOR SELECT C1
       FROM T1

• Example 3: Explain a simple SELECT statement and tag with QUERYNO = 13 and QUERYTAG = 'TEST13'.

     EXPLAIN PLAN SELECTION SET QUERYNO = 13 SET QUERYTAG = 'TEST13'
       FOR SELECT C1
       FROM T1

• Example 4: Attempt to get explain information when explain tables do not exist.

     EXPLAIN ALL FOR SELECT C1
       FROM T1

  This statement will fail because the explain tables have not been defined (SQLSTATE 42704).
• Example 5: The following statement will succeed if it is found in the package cache and has already been compiled using REOPT ONCE.

     EXPLAIN ALL WITH REOPT ONCE FOR SELECT C1
       FROM T1
       WHERE C1 = :<host variable>

• Example 6: The following example uses the db2-fn:xmlcolumn function, which takes the case- sensitive name of an XML column as an argument and returns an XML sequence that is the concatenation of XML column values.
  Consider a table called BUSINESS.CUSTOMER with an XML column called INFO. A simple XQuery that returns all documents from the INFO column is :

     EXPLAIN PLAN SELECTION 
        FOR XQUERY 'db2-fn:xmlcolumn ("BUSINESS.CUSTOMER.INFO")'

  If a column value is null, then the resulting return sequence for that row will be empty.