DSN_VIRTUAL_KEYTARGETS
The DSN_VIRTUAL_KEYTARGETS table contains information about expression-based indexes and XML indexes.
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
| Column name | Data Type | Description |
|---|---|---|
| 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. |
