DSN_VIRTUAL_KEYTARGETS
The DSN_VIRTUAL_KEYTARGETS table contains information about expression-based indexes and XML indexes.
Introductory concepts
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.
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 shows the descriptions of the columns in the DSN_VIRTUAL_KEYTARGETS
table.
| Column name | Data Type | Description |
|---|---|---|
| ENABLE | CHAR(1) NOT NULL | Indicates whether this key target should be considered in the scenario that is being tested. This column can have one of the following values:
If this column contains 'Y', but the index definition is not valid, the index is ignored.
|
| IXNAME | VARCHAR(128) NOT NULL | The name of the index. |
| IXSCHEMA | VARCHAR(128) NOT NULL | The qualifier of the index. |
| KEYSEQ | SMALLINT NOT NULL | The numeric position of the key-target in the index. |
| COLNO | SMALLINT NOT NULL | The numeric position of the column in the table if the expression is a single column. This value is 0 if the expression is not a single column, and for XML indexes. |
| ORDERING | CHAR(1) NOT NULL | The order of the key:
|
| TYPESCHEMA | VARCHAR(128) NOT NULL | The schema of the data type. |
| TYPENAME | VARCHAR(128) NOT NULL | The name of the data type. |
| LENGTH | SMALLINT NOT NULL | The length attribute of the key-target,
or the precision of decimal key-targets. The value does not include
the internal prefixes that are used to record the actual length and
null states, when applicable.
|
| LENGTH2 | INTEGER NOT NULL | The maximum length of
the data that is retrieved from the column.
|
| SCALE | SMALLINT NOT NULL | The scale of decimal data or number of fractional second digits of timestamp or timestamp with time zone data. Otherwise the value is 0. If the column is a timestamp type, the LENGTH is 10 and the SCALE is 0, the number of fractional second digits is 6. |
| NULLS | CHAR(1) NOT NULL | Whether the key can contain
null values:
|
| CCSID | INTEGER NOT NULL | The CCSID of the key. This value is 0 if the key is a non-character data type key. |
| SUBTYPE | CHAR(1) NOT NULL | The subtype of the data,
for character keys only.
|
| DERIVE_FROM | VARCHAR(4000) NOT NULL | For an expression-based index on a scalar expression, DERIVED_FROM contains the text of the scalar expression that is used to generated the key-target value. For an XML index, this is the XML pattern that is used to generate the key-target value. Otherwise DERIVED_FROM contains an empty string. |
| CARDF | FLOAT NOT NULL WITH DEFAULT -1 | The estimated number of distinct values for the key-target. The value is -2 if the index is a node ID index. For an XML value index, the statistic is for the second key-target (the DOCID column). For all other key-targets of the XML value index, the value is -2. |
Only the first 4096 rows with 'Y' in this column are used.
