EXPLAIN statement

The EXPLAIN statement obtains information about access path selection for an explainable statement. Start of change

A statement is explainable if it is a SELECT, MERGE, TRUNCATE, or INSERT statement, or the searched form of an UPDATE or DELETE statement. End of change

The information that is obtained is placed in a set of supplied user tables that are called EXPLAIN tables.

Begin program-specific programming interface information.

The plan table contains information about the access path for the specified statement or statements. The statement table can be populated with information about the estimated cost of executing the explainable statement. The function table can be populated with information about how Db2 resolves the user-defined functions that are referred to in the explainable statement. Other EXPLAIN tables can be populated with additional information about the execution of the explainable statement. For a complete list of EXPLAIN tables, see EXPLAIN tables.

Using EXPLAIN for queries that reference system-period temporal tables that are enabled for system data versioning, the result will show the system-period temporal tables and the history tables in EXPLAIN output if the query needs to reference both tables to satisfy the query.

Invocation for EXPLAIN

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

Authorization for EXPLAIN

The authorization rules are those defined for the SQL statement specified in the EXPLAIN statement. For example, see the description of the DELETE statement for the authorization rules that apply when EXPLAIN records are captured for DELETE statements.

If the EXPLAIN statement is embedded in an application program, the authorization rules that apply are those defined for embedding the specified SQL statement in an application program. In addition, the owner of the plan or package must also have one of the following characteristics:

Be the owner of a plan table named PLAN_TABLE
Have an alias on a plan table named owner.PLAN_TABLE and have SELECT and INSERT privileges on the table

If the EXPLAIN statement is dynamically prepared, the authorization rules that apply are those defined for dynamically preparing the specified SQL statement. In addition, the SQL authorization ID of the process or the role this is associated with the process (if the EXPLAIN statement is running in a trusted context that specifies the ROLE AS OBJECT OWNER AND QUALIFIER clause) must also have one of the following characteristics:

Be the creator of a plan table named PLAN_TABLE
Have an alias on a plan table named creator.PLAN_TABLE and have SELECT and INSERT privileges on the table

For EXPLAIN statements with the PLAN and ALL keywords, the privilege set that is defined below must include at least one of the following:

EXPLAIN
SQLADM
System DBADM
The authorization rules that are defined for the SQL statement specified in the EXPLAIN statement. For example, the authorization rules that apply when EXPLAIN records are captured for a DELETE statement are the authorization rules for the DELETE statement.

Start of change FL 500The authorization rules are different if the STMTCACHE or STABILIZED DYNAMIC QUERY keywords are specified. The privilege set must include at least one of the following: End of change

SQLADM authority
SYSADM authority
The authority that is required to share the cached statement. For more information about the authority to use the dynamic statement cache, see Conditions for statement sharing.
System DBADM authority

For EXPLAIN statements that contain the STMTCACHE ALL clause, the privilege set must include at least one of the following:

SQLADM authority
System DBADM authority
SYSADM authority

If the privilege set does not have the required authority, EXPLAIN records are captured only those statements that have the same authorization ID as the privilege set.

For the PACKAGE keyword, the privilege set must include at least one of the following:

SQLADM authority
SYSADM authority
SYSOPR authority
SYSCTRL authority

Privilege set: The privilege set comprises the union of authorities that are held by the authorization IDs of the process. If the process is running in a trusted context with a role, this role would be included as an authorization ID of the process.

Syntax for EXPLAIN

package-scope-specification:

Description for EXPLAIN

PLAN

Specifies that access path information is captured for the SQL statement. Under this option, Db2 uses the access path selection process to generate the EXPLAIN records for the statement.

One row is inserted into the PLAN_TABLE for each step used in executing explainable-sql-statement. The steps for enforcing referential constraints are not included.

If a statement table exists, one row that provides a cost estimate of processing the explainable statement is inserted into the statement table. If the explainable statement is a SELECT FROM data-change-statement, two rows are inserted into the statement table.

If a function table exists, one row is inserted into the function table for each user-defined function that is referred to by the explainable statement.

If additional EXPLAIN tables exist, rows are also inserted into those tables.

For more information, see:

ALL

Has the same effect as PLAN.

SET QUERYNO = integer

Associates integer with explainable-sql-statement. The column QUERYNO is given the value integer in every row inserted into the plan table, statement table, or function table by the EXPLAIN statement. If QUERYNO is not specified, Db2 itself assigns a number. For an embedded EXPLAIN statement, the number is the statement number that was assigned by the precompiler and placed in the DBRM.

FOR explainable-sql-statement

Specifies the text of an SQL statement for which EXPLAIN records are captured. explainable-sql-statement can be any explainable SQL statement. Start of change

A statement is explainable if it is a SELECT, MERGE, TRUNCATE, or INSERT statement, or the searched form of an UPDATE or DELETE statement. End of change

If the EXPLAIN statement is embedded in a program, the statement can contain references to host variables. If EXPLAIN is dynamically prepared, the statement can contain parameter markers. Host variables that appear in the statement must be defined in the statement's program.

The statement must refer to objects at the current server.

explainable-sql-statement must not contain a QUERYNO clause. To specify the value of the QUERYNO column, use the SET QUERYNO = integer clause of the EXPLAIN statement.

explainable-sql-statement cannot be a statement-name or a host-variable. To use capture EXPLAIN records for dynamic SQL statements, you must prepare the entire EXPLAIN statement dynamically.

To obtain information about an explainable SQL statement that references a declared temporary table, the EXPLAIN statement must be executed in the same application process in which the table was declared. For static EXPLAIN statements, the information is not obtained at bind-time but at run time when the EXPLAIN statement is incrementally bound.

STMTCACHE

Specifies that EXPLAIN records for the specified dynamic SQL statements in the dynamic statement cache are extracted and written to EXPLAIN tables. Under this option, no new access path selection processing occurs. The EXPLAIN records are extracted from the existing access paths that were selected when the dynamic SQL statements were prepared and entered the statement cache. In a data-sharing environment, the EXPLAIN records are extracted from the dynamic statement cache of the data sharing member where EXPLAIN STMTCACHE statement is executed.

ALL

Specifies that EXPLAIN records are extracted for all cached statements. STMTCACHE ALL returns one row for each cached statement to the DSN_STATEMENT_CACHE_TABLE. These rows contain identifying information about the statements in the cache, as well as statistics that reflect the execution of the statements by all processes that have executed the statement. Records are not returned to other EXPLAIN tables when STMTCACHE ALL is specified.

STMTID id-host-variable or integer-constant

Specifies that EXPLAIN records are extracted for the cached statement with the specified statement ID. The value contained in id-host-variable or specified by integer-constant identifies the statement ID. STMTCACHE STMTID returns rows to the following EXPLAIN tables:

PLAN_TABLE
DSN_STATEMNT_TABLE
DSN_FUNCTION_TABLE
DSN_STATEMENT_CACHE_TABLE

The statement ID is an integer that uniquely identifies a statement that has been cached in the dynamic statement cache. The statement ID of a cached statement can be retrieved through IFI monitor facilities from IFCID 316 or 124. Some diagnostic trace records, such as IFCIDs 0173, 0196, and 0337, also show the statement ID.

The QUERYNO column of each EXPLAIN table record that is returned contains the statement ID value.

STMTTOKEN id-host-variable or string-constant

Specifies that EXPLAIN records are extracted for the cached statements with the specified statement token and written to certain EXPLAIN tables. The value contained in token-host-variable or specified by string-constant identifies the statement token. STMTCACHE STMTTOKEN writes records to the following EXPLAIN tables:

PLAN_TABLE
DSN_STATEMNT_TABLE
DSN_FUNCTION_TABLE
DSN_STATEMENT_CACHE_TABLE

The statement token must be a character string that is no longer than 240 bytes. The application program that originally prepares and inserts a statement into the cache associates a statement token with the cached statement. The program can make this association with the RRSAF SET_ID function, or the sqleseti API if the program is connected remotely.

The STMTTOKEN column of each PLAN_TABLE record that is returned contains the statement token value. The QUERYNO column of each EXPLAIN table record that is returned contains the statement ID value.

For more information, see:

PACKAGE

Specifies that EXPLAIN records for all static SQL statements in the package that matches the specified scope are extracted and written to EXPLAIN tables. Under this option, no new access path selection processing occurs. The records are extracted from the existing access paths that were selected when the package was bound. The EXPLAIN information is added to the PLAN_TABLE that is owned by the current user. Other EXPLAIN tables are not populated.

COLLECTION collection-name

Specifies that EXPLAIN records are captured only for statements under the specified collection-name. collection-name is a string constant or a host variable that represents the collection name.

PACKAGE package-name

Specifies that EXPLAIN records are captured only for statements under the specified package-name. package-name is a string constant or a host variable that represents the package name.

VERSION version-name

Specifies that EXPLAIN records are captured only for statements under the specified version-name. version-name is a string constant or a host variable that represents the version name. If version-name is all blanks or an empty string, records are captured only for those versions of the package that contain all blanks for the version name.

If the VERSION clause is not specified, EXPLAIN records are captured for statements in all versions of the package package-name.

COPY copy-id

Specifies that EXPLAIN records are captured only for statements under the specified copy-id. copy-id must be one of the following values:

CURRENT
PREVIOUS
ORIGINAL

Start of change FL 500If the COPY clause is not specified, statements, EXPLAIN records are captured for the current, previous, and original copies that exist for that package. End of change

The HINT_USED column in the PLAN_TABLE is populated with EXPLAIN PACKAGE: copy-id. copy-id in the HINT_USED column will be one of the following values: Start of change

CURRENT - the current copy
PREVIOUS - the previous copy
ORIGINAL - the original copy

STABILIZED DYNAMIC QUERY

FL 500Specifies that the explain records for the stabilized dynamic statement identified by the STMTID keyword are extracted and written to the following EXPLAIN tables:

PLAN_TABLE
DSN_STATEMNT_TABLE
DSN_FUNCTION_TABLE

STMTID id-host-variable or integer-constant

Specifies that EXPLAIN information is captured for the statement with the specified statement identifier. This value is inserted into the PER_STMT_ID column of the EXPLAIN tables.

COPY copy-id

Specifies that EXPLAIN information is captured only for statements under the specified copy identifier value. copy-id is a string constant and must be one of the following values:

CURRENT
INVALID

The PLAN_TABLE.HINT_USED column is populated with the string 'EXPLAIN SDQ: copy-id-number' where copy-id-number is one of the following values:

CURRENT: The current copy.
INVALID: The invalid copy.

The QUERYNO column of each EXPLAIN table record that is returned is set to the default value 0, and the value of the COLLID column is set to 'DSNSTBLQRYEXPLAIN.'

Notes for EXPLAIN

Output from EXPLAIN:

Db2 inserts one or more rows of data into a plan table and other existing EXPLAIN tables.

For a list of all EXPLAIN tables, see EXPLAIN tables.

A plan table must exist before the operation that results in EXPLAIN output. You can find a sample CREATE TABLE statement for each EXPLAIN table in member DSNTESC of the prefix.SDSNSAMP library.

Unless you need the information that is provided by the additional EXPLAIN tables, it is not necessary to create those tables to use EXPLAIN. However, a statement cache table is required when the STMTCACHE ALL keyword is specified as part of an EXPLAIN statement.

Db2 uses the access path selection process to generate EXPLAIN records only for certain types of EXPLAIN statements, as shown in the following table.

Table 1. Origin of EXPLAIN records for various EXPLAIN statement options
Options Specified	How Db2 Creates EXPLAIN records
`EXPLAIN PLAN FOR explainable-sql-statement`	Uses the access path selection process to generate the EXPLAIN records
`EXPLAIN PACKAGE ...`	Extracts existing access path information from the package to create the EXPLAIN records.
`EXPLAIN STMTCACHE ...`	Extracts access path information from the dynamic statement cache to create the EXPLAIN records.
`EXPLAIN STABILIZED DYNAMIC QUERY ...`	Extracts access path information from catalog tables for the specified stabilized dynamic SQL statements.

Each row in an EXPLAIN table describes some aspect of a step in the execution of a query or subquery in an explainable statement. The column values for the row identify, among other things, the query or subquery, the tables and other objects involved, the methods used to carry out each step, and cost information about those methods.

Instances of these tables might also be created and used by certain optimization tools. For information about the meanings of different values in plan table and other EXPLAIN tables, see Interpreting data access by using EXPLAIN.

For information about how to correlate information across EXPLAIN tables, see Correlating information across EXPLAIN tables.

EXPLAIN tables might contain names that begin with DSN that have been generated by Db2.

Important: Do not manually manipulate the data in EXPLAIN tables that are created by optimization tools.

Column access control or row permissions enforced for EXPLAIN tables:

Column access control and row permissions can be enforced for EXPLAIN tables. However, row permissions and column masks are not applied when Db2 inserts rows into those tables.

If the specified statement references tables for which row or column access control is activated, the following information from row permission and column mask definitions created for the tables might appear in the EXPLAIN tables:

DSN_FUNCTION_TABLE - user-defined functions
DSN_PREDICAT_TABLE - predicates (except predicates in CASE WHEN clauses)
DSN_STRUCT_TABLE - query blocks
PLAN_TABLE - access path of subqueries

In addition, the complete or partial definition text might appear in EXPLAIN tables like DSN_FUNCTION_TABLE, DSN_PREDICAT_TABLE, DSN_QUERY_TABLE, DSN_SORTKEY_TABLE, DSN_STATEMENT_CACHE_TABLE, and DSN_STATEMENT_RUNTIME_INFO.

Impact to the existing access paths when the table has enforced column access control or row permissions:

The predicates from the row permissions are considered in the access path selection. Therefore, they are shown in the EXPLAIN tables for the performance tuning purpose.

Impact to EXPLAIN tables when referencing a non-existing object in a dynamic SQL statement

In some situations in which EXPLAIN is run on a dynamic SQL statement that references a non-existent object, a SQLCODE -204 is issued to indicate that the object is not defined in the Db2 subsystem. However, changes to the related EXPLAIN tables might not be rolled back. End of change

Considerations when capturing EXPLAIN records for 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

You cannot use a static EXPLAIN statement to determine whether a rowset query is passed to an accelerator server, because you cannot specify the WITH ROWSET POSITIONING cursor attribute for a static EXPLAIN statement. Instead, you must use a dynamic EXPLAIN statement, where the WITH ROWSET POSITIONING clause is specified in the attribute string. You also can specify the WITH RETURN clause in the attribute string to see the ineligibility of result sets.

Also, the EXPLAIN statement cannot be used to determine that a rowset query cannot be passed to an accelerator server because the query is being run remotely or under an SQL PL routine. If the PREPARE of the EXPLAIN statement is run locally, Db2 determines if the rowset query can be accelerated as a local query offload. However, if the PREPARE of the EXPLAIN statement runs remotely, Db2 indicates that the rowset query cannot be accelerated. Instead, you can use the CURRENT EXPLAIN MODE special register to determine the behavior for eligible dynamic SQL statements during application execution. For more information, see CURRENT EXPLAIN MODE special register.

EXPLAIN tables

For a descriptions of the EXPLAIN tables, see EXPLAIN tables.

Examples for EXPLAIN

Example 1: Determine the steps required to execute the query '

SELECT
X.ACTNO...

'. Assume that no set of rows in the PLAN_TABLE has the value 13 for the QUERYNO column.

   EXPLAIN PLAN SET QUERYNO = 13
   FOR SELECT X.ACTNO, X.PROJNO, X.EMPNO, Y.JOB, Y.EDLEVEL
       FROM DSN8C10.EMPPROJACT X, DSN8C10.EMP Y
          WHERE X.EMPNO = Y.EMPNO
             AND X.EMPTIME > 0.5
             AND (Y.JOB = 'DESIGNER' OR Y.EDLEVEL >= 12)
          ORDER BY X.ACTNO, X.PROJNO;

Example 2: Retrieve the information returned in Example 1. Assume that a statement table exists, so also retrieve the estimated cost of processing the query. Use the following query, which joins the plan table and the statement table.

  SELECT * FROM PLAN_TABLE A, DSN_STATEMNT_TABLE B
    WHERE A.QUERYNO = 13 and B.QUERYNO = 13
    ORDER BY A.QBLOCKNO, A.PLANNO, A.MIXOPSEQ;

Example 3: Extract existing access path information to capture EXPLAIN records for the cached statement with statement ID 124. Assume that host variable SID contains 124.

EXPLAIN STMTCACHE STMTID :SID;

Example 4: Extract existing access path information to capture one row of EXPLAIN data for each statement in the dynamic statement cache. The records are written only to the DSN_STATEMENT_CACHE_TABLE.

EXPLAIN STMTCACHE ALL;

Example 5: Assume that you want to use the plan table that was created by ADMF001 and your authorization ID is SYSADM. If you have an alias on ADMF001.PLAN_TABLE (CREATE ALIAS SYSADM.PLAN_TABLE FOR ADMF001.PLAN_TABLE) and sufficient INSERT and SELECT privileges on the table, the following EXPLAIN statement will execute and ADMF001.PLAN_TABLE will be populated.

  EXPLAIN PLAN SET QUERYNO = 101
    FOR SELECT * FROM DSN8C10.EMP;

Example 6: Extract existing access path information to capture EXPLAIN records to the current user's PLAN_TABLE for all static SQL statements in the current copy of the package 'COLLA.PACK52604':

  EXPLAIN PACKAGE COLLECTION 'COLLA' PACKAGE 'PACK52604' COPY 'CURRENT';

End program-specific programming interface information.