DSN_COLDIST_TABLE
The column distribution table contains non-uniform column group statistics that are obtained dynamically by DB2® from non-index leaf pages.
Qualifiers
- 'SYSIBM'
- One instance of this table can be created with the SYSIBM qualifier. DB2 and SQL optimization tools might use the table and the data that it contains. The table is created when you run job DSNTIJSG when you install or migrate DB2.
- 'user-ID'
- You can create additional instances of EXPLAIN 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.
Column descriptions
Column name | Data Type | Description |
---|---|---|
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. When 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. |
APPLNAME | VARCHAR(128) 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. When the SQL statement is embedded in a compiled SQL function or native SQL procedure, this column is not used, and is blank. |
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. 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. |
COLLID | VARCHAR(128) NOT NULL | The collection ID:
When 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. |
GROUP_MEMBER | VARCHAR(128) 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. |
SECTNOI | INTEGER NOT NULL | 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 | 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. When 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. |
EXPLAIN_TIME | TIMESTAMP NOT NULL | The
time when the EXPLAIN information was captured:
|
SCHEMA | VARCHAR(128) NOT NULL | The schema of the table that contains the column. |
TBNAME | VARCHAR(128) NOT NULL | The name of the table that contains the column. |
NAME | VARCHAR(128) NOT NULL | Name of the column. If the value of NUMCOLUMNS is greater than 1, this name identifies the first column name of the set of columns associated with the statistics. |
COLVALUE | VARCHAR(2000) NOT NULL FOR BIT DATA | Contains the data of a frequently occurring value in the column.
Statistics are not collected for an index on a ROWID column. If the value has a non-character data
type, the data might not be
printable. This column might contain values that depend on the value of the type column:
|
TYPE | CHAR(1) NOT NULL | The type of statistics:
|
CARDF | FLOAT NOT NULL | For TYPE='C', the number of distinct values for the
column group. For TYPE='H', the number of distinct values for the column group in a quantile
indicated by the value of the QUANTILENO
column. For TYPE='T', a value related to real-time statistics table values that are determined by the COLVALUE column. For TYPE= 'L', a value related to a real-time statistics column value that is determined by the COLVALUE column. The QUANTILENO column contains the column number. The NAME column contains the column name. For TYPE='P' a value related to real-time statistics partition value that is determined by the COLVALUE column. The QUANTILENO column contains the partition number. |
COLGROUPCOLNO | VARCHAR(254) NOT NULL FOR BIT DATA | The identity of 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 the NUMCOLUMNS column. This is an updatable column. |
NUMCOLUMNS | SMALLINT NOT NULL | Identifies the number of columns associated with the statistics. |
FREQUENCYF | FLOAT NOT NULL | The percentage of rows in the table with the value that is specified in the COLVALUE column when the number is multiplied by 100. For example, a value of '1' indicates 100%. A value of '.153' indicates 15.3%. |
QUANTILENO | SMALLINT NOT NULL | The ordinary sequence number of a quantile in the whole
consecutive value range, from low to high. This column is not updatable.
For TYPE= 'L', this column contains the column number. For TYPE='P', the column contains the partition number. |
LOWVALUE | VARCHAR(2000) NOT NULL FOR BIT DATA | For TYPE='H', this is the lower bound for the quantile indicated by the value of the QUANTILENO column. Not used if the value of the TYPE column is not 'H'. This column is not updatable. |
HIGHVALUE | VARCHAR(2000) NOT NULL FOR BIT DATA | For TYPE='H', this is the higher bound for the quantile indicated by the value of the QUANTILENO column. This column is not used if the value of the TYPE column is not 'H'. This column is not updatable. |