DSN_VIRTUAL_KEYTARGETS

The DSN_VIRTUAL_KEYTARGETS table contains information about expression-based indexes and XML indexes.

Start of change If the DSN_VIRTUAL_KEYTARGETS table contains more than 4096 rows with ENABLE='Y', Db2 ignores any rows after the first 4096 that it reads with this value. End of change

Begin program-specific programming interface information.

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. To upgrade the EXPLAIN tables to the format of the current Db2, you can call the ADMIN_EXPLAIN_MAINT stored procedure. ADMIN_EXPLAIN_MAINT can also 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. Alternatively, to perform only the task of upgrading EXPLAIN tables to the format of the current Db2, you can run job DSNTIJXA. DSNTIJXA invokes REXX exec DSNTXTA to perform the upgrade. See Migration step 21: Convert EXPLAIN tables to the current format for more information on running DSNTIJXA.

COLUMN descriptions

The following table shows the descriptions of the columns in the DSN_VIRTUAL_KEYTARGETS table.

Table 1. Descriptions of 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: Y Use this key target. N Do not use this key target. If this column contains 'Y', but the index definition is not valid, the index is ignored. If the DSN_VIRTUAL_KEYTARGETS table contains more than 4096 rows with ENABLE='Y', Db2 ignores any rows after the first 4096 that it reads with this value.
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: 'A' Ascending order.
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. INTEGER 4 SMALLINT 2 FLOAT 4 or 8 CHAR The length of the string VARCHAR The maximum length of the string DECIMAL The precision of the number GRAPHIC The number of DBCS characters VARGRAPHIC The maximum number of DBCS characters DATE 4 TIME 3 TIMESTAMP WITHOUT TIME ZONE The integral part of `((p+1)/2) + 7` where `p`is the precision of the timestamp TIMESTAMP WITH TIME ZONE The integral part of `((p+1)/2) + 9` where `p` is the precision of the timestamp BIGINT 8 BINARY The length of the string VARBINARY The maximum length of the string DECFLOAT 8 or 16
LENGTH2	INTEGER NOT NULL	The maximum length of the data that is retrieved from the column. 0 Not a ROWID column. 40 For a ROWID column, the length of the value that is returned.
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: 'N' No 'Y' Yes
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. 'B' Bit data 'M' Mixed data 'S' SBCS data blank Non-character data.
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.

End program-specific programming interface information.