DSN_STAT_FEEDBACK
The DSN_STAT_FEEDBACK table contains recommendations for capturing missing or conflicting statistics that are defined during EXPLAIN. Collecting these statistics by the RUNSTATS utility might improve the performance of the query.
Information is
captured in this table only for EXPLAIN operations that use the access path selection process. The
access path selection process is not used when you issue an EXPLAIN statement with the STMTCACHE or
PACKAGE options.
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 contains descriptions of the columns in the DSN_STAT_FEEDBACK table.| Column name | Data Type | Descriptions |
|---|---|---|
| QUERYNO | INTEGER NOT NULL | 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.
|
| APPLNAME | VARCHAR(24) NOT NULL | 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.
|
| PROGNAME | VARCHAR(128) NOT NULL | 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.
|
| COLLID | VARCHAR(128) NOT NULL WITH DEFAULT | The collection ID:
|
| GROUP_MEMBER | VARCHAR(24) NOT NULL | 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 | The
time when the EXPLAIN information was captured:
|
| SECTNOI | INTEGER NOT NULL WITH DEFAULT 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.
|
| TBCREATOR | VARCHAR(128) NOT NULL | The creator of the table. |
| TBNAME | VARCHAR(128) NOT NULL | The name of the table. |
| IXCREATOR | VARCHAR(128) NOT NULL | The creator of the index. |
| IXNAME | VARCHAR(128) NOT NULL | The name of the index. |
| COLNAME | VARCHAR(128) NOT NULL | The name of the column. |
| NUMCOLUMNS | SMALLINT NOT NULL | The number of columns in the column group. |
| COLGROUPCOLNO | VARCHAR(254) NOT NULL FOR BIT DATA | A hex representation that identifies the set of columns associated with the statistics. If the statistics are only associated with a single column, the field contains a zero length. Otherwise, the field is an array of SMALLINT column numbers with a dimension equal to the value in NUMCOLUMNS. |
| TYPE | CHAR(1) NOT NULL | The type of statistic to collect:
|
| DBNAME | VARCHAR(24) NOT NULL | The name of the database. |
| TSNAME | VARCHAR(24) NOT NULL | The name of the table space. |
| REASON | CHAR(8) NOT NULL | The reason for the statistics collection recommendation:
|
| REMARKS | VARCHAR(254) NOT NULL | Free form text for extensibility. |
