DSN_STATEMNT_TABLE

The statement table, DSN_STATEMNT_TABLE, contains information about the estimated cost of specified SQL statements.

Begin program-specific programming interface information.
Recommendation: Do not manually insert data into system-maintained EXPLAIN tables, and use care when deleting obsolete EXPLAIN table data. The data is intended to be manipulated only by the Db2 EXPLAIN function and optimization tools. Certain optimization tools depend on instances of the various EXPLAIN tables. Be careful not to delete data from or drop instances EXPLAIN tables that are created for these tools.

Qualifiers

Start of changeYour subsystem or data sharing group can contain multiple instances of these tables that are qualified by user ID. These tables are populated with statement cost information when you issue the EXPLAIN statement or bind. They are also populated when you specify EXPLAIN(YES) or EXPLAIN(ONLY) in a BIND or REBIND command. SQL optimization tools might also create EXPLAIN tables that are qualified by a user ID. You can find the SQL statement for creating an instance of these tables in member DSNTESC of the SDSNSAMP library.End of change

Sample CREATE TABLE statement

You can find a sample CREATE TABLE statement for each EXPLAIN table in member DSNTESC of the prefix.SDSNSAMP library. You can call the ADMIN_EXPLAIN_MAINT stored procedure to create EXPLAIN tables, upgrade them to the format for the current Db2 release, or complete other maintenance tasks. See ADMIN_EXPLAIN_MAINT stored procedure for information about using the action input parameter to request each of these tasks.

Column descriptions

The following table describes the content of each column in STATEMNT_TABLE.

Table 1. Descriptions of columns in DSN_STATEMNT_TABLE
Column name Data type Description
QUERYNO INTEGER NOT NULL WITH DEFAULT A number that identifies the statement that is being explained. The origin of the value depends on the context of the row:
For rows produced by EXPLAIN statements
The number specified in the QUERYNO clause, which is an optional part of the SELECT, INSERT, UPDATE, MERGE, and DELETE statement syntax.
For rows not produced by EXPLAIN statements
Db2 assigns a number that is based on the line number of the SQL statement in the source program.

When the values of QUERYNO are based on the statement number in the source program, values that exceed 32767 are reported as 0. However, in certain rare cases, the value is not guaranteed to be unique.

Start of changeWhen the SQL statement is embedded in a compiled SQL function or native SQL procedure, if the QUERYNO clause is specified, its value is used by Db2. Otherwise Db2 assigns a number based on the line number of the SQL statement in the compiled SQL function or native SQL procedure.End of change

APPLNAME VARCHAR(24) NOT NULL WITH DEFAULT The name of the application plan for the row. Applies only to embedded EXPLAIN statements that are executed from a plan or to statements that are explained when binding a plan. A blank indicates that the column is not applicable.

Start of changeWhen the SQL statement is embedded in a compiled SQL function or native SQL procedure, this column is not used, and is blank.End of change

PROGNAME VARCHAR(128) NOT NULL WITH DEFAULT The name of the program or package containing the statement being explained. Applies only to embedded EXPLAIN statements and to statements explained as the result of binding a plan or package. A blank indicates that the column is not applicable.

Start of changeWhen the SQL statement is embedded in a compiled SQL function or native SQL procedure, this column indicates the specific name of the compiled SQL function or native SQL procedure.End of change

COLLID VARCHAR(128) NOT NULL WITH DEFAULT The collection ID:
'DSNDYNAMICSQLCACHE'
The row originates from the dynamic statement cache.
'DSNEXPLAINMODEYES'
The row originates from an application that specifies YES for the value of the CURRENT EXPLAIN MODE special register.
'DSNEXPLAINMODEEXPLAIN'
The row originates from an application that specifies EXPLAIN for the value of the CURRENT EXPLAIN MODE special register.

Start of changeWhen the SQL statement is embedded in a compiled SQL function or native SQL procedure, this column indicates the schema name of the compiled SQL function or native SQL procedure.End of change

GROUP_MEMBER VARCHAR(24) NOT NULL WITH DEFAULT The member name of the Db2 that executed EXPLAIN. The column is blank if the Db2 subsystem was not in a data sharing environment when EXPLAIN was executed.
EXPLAIN_TIME TIMESTAMP NOT NULL WITH DEFAULT The time when the EXPLAIN information was captured:
All cached statements
When the statement entered the cache, in the form of a full-precision timestamp value.
Non-cached static statements
When the statement was bound, in the form of a full precision timestamp value.
Non-cached dynamic statements
When EXPLAIN was executed, in the form of a value equivalent to a CHAR(16) representation of the time appended by 4 zeros.
STMT_TYPE CHAR(6) NOT NULL WITH DEFAULT The type of statement being explained. Possible values are:
SELECT
SELECT
INSERT
INSERT
UPDATE
UPDATE
MERGE
MERGE
DELETE
DELETE
TRUNCA
TRUNCATE
SELUPD
SELECT with FOR UPDATE OF
DELCUR
DELETE WHERE CURRENT OF CURSOR
UPDCUR
UPDATE WHERE CURRENT OF CURSOR
Start of changePRUNEDEnd of change
Start of changeA query that always returns 0 rows.End of change
COST_CATEGORY CHAR(1) NOT NULL WITH DEFAULT Indicates if Db2 was forced to use default values when making its estimates. Possible values:
A
Indicates that Db2 had enough information to make a cost estimate without using default values.
B
Indicates that some condition exists for which Db2 was forced to use default values. See the values in REASON to determine why Db2 was unable to put this estimate in cost category A.
PROCMS INTEGER NOT NULL WITH DEFAULT The estimated processor cost, in milliseconds, for the SQL statement. The estimate is rounded up to the next integer value. The maximum value for this cost is 2147483647 milliseconds, which is equivalent to approximately 24.8 days. If the estimated value exceeds this maximum, the maximum value is reported. If an accelerator is used, the difference is reflected in this value.
PROCSU INTEGER NOT NULL WITH DEFAULT The estimated processor cost, in service units, for the SQL statement. The estimate is rounded up to the next integer value. The maximum value for this cost is 2147483647 service units. If the estimated value exceeds this maximum, the maximum value is reported. If an accelerator is used, this value represents the estimated cost including any impact of acceleration.
REASON VARCHAR(254) WITH DEFAULT A string that indicates the reasons for putting an estimate into cost category B.
Start of changeACCELMODEL ELIGIBLEEnd of change
Start of changeThe query is eligible for acceleration.End of change
Start of changeACCELMODEL NOT ELIGIBLEEnd of change
Start of changeThe query is not eligible for acceleration.End of change
HAVING CLAUSE
A subselect in the SQL statement contains a HAVING clause.
HOST VARIABLES
The statement uses host variables, parameter markers, or special registers.
Start of changeOPTIMIZATION HINTSEnd of change
Start of changeAn statement-level access path, or PLAN_TABLE access path hint is applied to the statement, or APREUSE(ERROR/WARN) is applied for the package.End of change
PROFILEID value
When profile monitoring is used for the statement, the value of the PROFILEID column in SYSIBM.DSN_PROFILE_TABLE.
REFERENTIAL CONSTRAINTS
Referential constraints of the type CASCADE or SET NULL exist on the target table of a DELETE statement.
TABLE CARDINALITY
Start of changeThe cardinality statistics are missing for one or more of the tables that are used in the statement, or the statement used materialized views or table expressions. End of change
TRIGGERS
Triggers are defined on the target table of an insert, update, or delete operation.
UDF
The statement uses user-defined functions.
STMT_ENCODE CHAR(1) WITH DEFAULT Encoding scheme of the statement. If the statement represents a single CCSID set, the possible values are:
A
ASCII
E
EBCDIC
U
Unicode

If the statement has multiple CCSID sets, the value is M.

Start of changeTOTAL_COSTEnd of change Start of changeFLOAT NOT NULL WITH DEFAULTEnd of change Start of changeThe overall estimated cost of the statement. If an accelerator is used, the benefit is reflected in this value. . Use this value for reference purposes only. Db2 does not always choose the access path that has the lowest TOTAL_COST value. Db2 also uses other factors during access path selection, such as the reliability of the filter factor estimates.End of change
SECTNOI INTEGER NOT NULL WITH DEFAULT The section number of the statement. The value is taken from the same column in SYSPACKSTMT or SYSSTMT tables and can be used to join tables to reconstruct the access path for the statement. This column is applicable only for static statements. The default value of -1 indicates EXPLAIN information that was captured in DB2® 9 or earlier.
VERSION VARCHAR(122) NOT NULL WITH DEFAULT The version identifier for the package. Applies only to an embedded EXPLAIN statement executed from a package or to a statement that is explained when binding a package. A blank indicates that the column is not applicable.

Start of changeWhen the SQL statement is embedded in a compiled SQL function or native SQL procedure, this column indicates the version identifier of the compiled SQL function or native SQL procedure.End of change

Start of change EXPANSION_REASON End of change Start of change CHAR(2) NOT NULL WITH DEFAULT End of change Start of changeThis column applies only to statements that reference archive tables or temporal tables. For other statements, this column is blank.

Indicates the effect of the CURRENT TEMPORAL BUSINESS_TIME special register, the CURRENT TEMPORAL SYSTEM_TIME special register, and the SYSIBMADM.GET_ARCHIVE built-in global variable. These items are controlled by the BUSTIMESENSITIVE, SYSTIMESENSITIVE, and ARCHIVESENSITIVE bind options.

Db2 implicitly adds certain syntax to the query if one of the following conditions are true:
  • The SYSIBMADM.GET_ARCHIVE global variable is set to Y and the ARCHIVESENSITIVE bind option is set to YES
  • The CURRENT TEMPORAL BUSINESS_TIME special register is not null and the BUSTIMESENSITIVE bind option is set to YES
  • The CURRENT TEMPORAL SYSTEM_TIME special register is not null and the SYSTIMESENSITIVE bind option is set to YES
This column can have one of the following values:
'A'
The query contains implicit query transformation as a result of the SYSIBMADM.GET_ARCHIVE built-in global variable.
'B'
The query contains implicit query transformation as a result of the CURRENT TEMPORAL BUSINESS_TIME special register.
'S'
The query contains implicit query transformation as a result of the CURRENT TEMPORAL SYSTEM_TIME special register.
'SB'
The query contains implicit query transformation as a result of the CURRENT TEMPORAL SYSTEM_TIME special register and the CURRENT TEMPORAL BUSINESS_TIME special register.
blank
The query does not contain implicit query transformation.
End of change

End program-specific programming interface information.