DSN_STATEMNT_TABLE
The statement table, DSN_STATEMNT_TABLE, contains information about the estimated cost of specified SQL statements.
Qualifiers
Your 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.
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.
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:
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. When the SQL statement is embedded in a compiled SQL function, native SQL procedure, or advanced trigger, 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, native SQL procedure, or advanced trigger. |
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. When the SQL statement is embedded in a compiled SQL function, native SQL procedure, or advanced trigger, this column is not used, and is blank. |
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. When 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. When the SQL statement is embedded in an advanced trigger, this column contains the name of the trigger. |
COLLID | VARCHAR(128) NOT NULL WITH DEFAULT | The collection ID:
When the SQL statement is embedded in a compiled SQL function, native SQL procedure, or advanced trigger, this column indicates the schema name of the compiled SQL function, native SQL procedure, or advanced trigger. |
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:
|
STMT_TYPE | CHAR(6) NOT NULL WITH DEFAULT | The type of statement being explained. Possible
values are:
|
COST_CATEGORY | CHAR(1) NOT NULL WITH DEFAULT | Indicates if Db2 was forced to use default values when making its
estimates. Possible values:
|
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.
|
STMT_ENCODE | CHAR(1) WITH DEFAULT | Encoding scheme of the statement. If the statement represents a single CCSID
set, the possible values are:
If the statement has multiple CCSID sets, the value is M. |
TOTAL_COST | FLOAT NOT NULL WITH DEFAULT | The 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. |
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. If the value is not blank, the value is the same as the VERSION value for the package that was used to create this EXPLAIN table row. The value is blank for a statement in:
|
EXPANSION_REASON | CHAR(2) NOT NULL WITH DEFAULT |
This 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:
This column can have one of the following values:
|
APCOMPARE_STATUS | CHAR(1) NOT NULL WITH DEFAULT | The status of the access
path comparison operation for the APCOMPARE option of a BIND or REBIND command.
|
APREUSE_STATUS | CHAR(1) NOT NULL WITH DEFAULT | The status of the access
path reuse operation for the APREUSE option of a BIND or REBIND command.
|
APREUSE_VERSION | VARCHAR(122) NOT NULL WITH DEFAULT | The version identifier for the package. The value is the bind version of the package whose access path is reused. The default value blank is used when APREUSE_STATUS is blank. |
APREUSE_COPYID | INTEGER NOT NULL WITH DEFAULT | The copy number of identifier for the package. The value is the copy number of the package whose access plan is being taken to be reused. The default value -1 blank is used when APREUSE_STATUS is blank. |
EXPLAIN_TYPE | CHAR(1) | The type of action that
created the row:
|
PER_STMT_ID | BIGINT NOT NULL | The persistent statement identifier for SQL statements in Db2 catalog tables. For example, this column corresponds to the following catalog table columns that identify SQL statements:
|
QUERY_HASH | CHAR(16) NOT NULL FOR BIT DATA | The hash key that is generated by the statement text. This value is not unique for each statement. Other columns for the collection ID, package name, section number, and query number can be used with the hash key for uniqueness. |
FUNCTION_LVL | VARCHAR(10) NOT NULL WITH DEFAULT | The function level of the Db2 subsystem when the access path was selected for the statement. |
STMT_HASHID2 | CHAR(8) FOR BIT DATA | Used to identify an SQL statement. Based on normalized SQL statement text.
|
STMT_HASH2VER | INTEGER | Used to identify the version of the hash algorithm that is used to compute the STMT_HASHID2 value. |
AP_PLANID | CHAR(16) FOR BIT DATA | A unique identifier for BIND or PREPARE optimizations for an SQL statement, in the form of an extended timestamp value. |
AP_PLANHASH | CHAR(16) FOR BIT DATA | Used to identify an access path. Based on selected columns of the PLAN_TABLE which contribute to the access path. Excludes cols that don’t affect the access path, such as APPLNAME, PROGNAME, VERSION, etc. |
AP_PLANHASHVER | INTEGER | Used to identify the version of the hash algorithm that is used to compute the AP_PLANHASH value. |
AP_SERVICE_DATA | VARCHAR(512) FOR BIT DATA | Used to save optimizer-sensitive environment variables at the time of optimization. |
CONNECTION_TYPE | CHAR(8) | Connection type which did the PREPARE or BIND for the statement. |
CLIENT_USERID | VARCHAR(128) | The client user ID name information. |
CLIENT_APPLNAME | VARCHAR(255) | The client application name information. |
CLIENT_WRKSTNNAME | VARCHAR(255) | The client workstation name information. |