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.
This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared.
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
- 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.
The 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:
- 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
- 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.
- 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.
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.
- 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. A statement is explainable if it is a SELECT, MERGE, TRUNCATE, or INSERT statement, or the searched form of an UPDATE or DELETE statement. 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.
- 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.
- 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:
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:
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:
- 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:
If the COPY clause is not specified, statements, EXPLAIN records are captured for the current, previous, and original copies that exist for that package.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:
CURRENT- the current copy
PREVIOUS- the previous copy
ORIGINAL- the original copy
- STABILIZED DYNAMIC QUERY
- Specifies that the explain records for the stabilized dynamic statement identified by the STMTID
keyword are extracted and written to the following EXPLAIN tables:
- 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
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:
- The current copy.
- 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.'
- 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
DSNthat 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.
- 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.
- EXPLAIN tables
- For a descriptions of the EXPLAIN tables, see EXPLAIN tables.
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;
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;
EXPLAIN STMTCACHE STMTID :SID;
EXPLAIN STMTCACHE ALL;
EXPLAIN PLAN SET QUERYNO = 101 FOR SELECT * FROM DSN8C10.EMP;
EXPLAIN PACKAGE COLLECTION 'COLLA' PACKAGE 'PACK52604' COPY 'CURRENT';