DSN_VIRTUAL_INDEXES

The virtual indexes table, DSN_VIRTUAL_INDEXES, enables optimization tools to test the effect of creating and dropping indexes on the performance of particular queries.

Begin program-specific programming interface information.If virtual indexes are being used, queries that are submitted using EXPLAIN PLAN FOR explainable-sql-statement will use virtual indexes as well as regular indexes during optimization. Any virtual index specifications in the DSN_VIRTUAL_INDEXES table will be used during query optimization. The following table describes the columns in DSN_VIRTUAL_INDEXES.
Tip: You can use tools such as IBM® Data Server Manager and related Db2 Tools for z/OS® solutions to simplify this task.
Recommendation: Do not manually insert data into or delete data from this table, it is intended to be used only by optimization tools.
Table 1. DSN_VIRTUAL_INDEXES description
Column name Data type Description
TBCREATOR VARCHAR(128) The schema or authorization ID of the owner of the table on which the index is being created or dropped.
TBNAME VARCHAR(128) The name of the table on which the index is being created or dropped.
IXCREATOR VARCHAR(128) The schema or authorization ID of the owner of the index.
IXNAME VARCHAR(128) The index name.
ENABLE CHAR(1) Indicates whether this index should be considered in the scenario that is being tested. This column can have one of the following values:
Y
Use this index.
N
Do not use this index.

If this column contains 'Y', but the index definition is not valid, the index is ignored.

MODE CHAR(1) Indicates whether the index is being created or dropped. This column can have one of the following values:
C
This index is to be created.
D
This index is to be dropped.
UNIQUERULE CHAR(1) Indicates whether the index is unique. This column can have one of the following values:
D
The index is not unique. (Duplicates are allowed.)
U
This index is unique.
COLCOUNT SMALLINT The number of columns in the key.
CLUSTERING CHAR(1) Indicates whether the index is clustered. This column can have one of the following values:
Y
The index is clustered.
N
The index is not clustered.
NLEAF INTEGER The number of active leaf pages in the index. If unknown, the value is -1.
NLEVELS SMALLINT The number of levels in the index tree. If unknown, the value is -1.
INDEXTYPE CHAR(1) The index type. This column can have one of the following values:
2
The index is a nonpartitioned secondary index.
D
The index is a data-partitioned secondary index.
PGSIZE SMALLINT The size, in kilobytes, of the leaf pages in the index. This column can have one of the following values: 4, 8, 16, or 32.
FIRSTKEYCARDF FLOAT The number of distinct values of the first key column. If unknown, the value is -1.
FULLKEYCARDF FLOAT The number of distinct values of the key. If unknown, the value is -1.
CLUSTERRATIOF FLOAT The percentage of rows that are in clustering order. Multiply this column value by 100 to get the percent value. For example, a value of .9125 in this column indicates that 91.25%. of the rows are in clustering order. If unknown, the value is -1.
PADDED CHAR(1) Indicates whether keys within the index are padded for varying-length column data. This column can have one of the following values:
Y
The keys are padded.
N
The keys are not padded.
COLNO1 SMALLINT The column number of the first column in the index key.
ORDERING1 CHAR(1) Indicates the order of the first column in the index key. This column can have one of the following values:
A
Ascending
D
Descending
COLNOn SMALLINT The column number of the nth column in the index key, where n is a number between 2 and 64, including 2 and 64. If the number of index keys is less than n, this column is null.
ORDERINGn CHAR(1) Indicates the order of the nth column in the index key, where n is a number between 2 and 64, including 2 and 64. This column can have one of the following values:
A
Ascending
D
Descending

If the number of index keys is less than n, this column is null.

Start of changeKEYTARGET_COUNTEnd of change Start of changeSMALLINT NOT NULL WITH DEFAULTEnd of change Start of changeThe number of key-targets for an extended index. A 0 value indicates a simple index.End of change
Start of changeUNIQUE_COUNTEnd of change Start of changeSMALLINT NOT NULL WITH DEFAULTEnd of change Start of changeThe number of columns or key-targets that make up the unique constraint of an index, when other non-constraint enforcing columns or key-targets exist. Otherwise, the value is 0.End of change
Start of changeIX_EXTENSION_TYPEEnd of change Start of changeCHAR(1) NOT NULL WITH DEFAULTEnd of change Start of changeThe type of extended index:
'S'
Index on a scalar expression
'V'
XML index.
blank
A simple index.
End of change
Start of changeDATAREPEATFACTORFEnd of change Start of changeFLOAT NOT NULL WITH DEFAULT -1End of change Start of changeThe number of data pages that are expected to be touched when an index key order is followed. The values is -1 if statistics have not been collected. The valid values are -1 or any value that is equal to or greater than 1.End of change
Start of changeSPARSEEnd of change Start of changeCHAR(1) NOT NULL WITH DEFAULTEnd of change Start of changeWhether the index is sparse:
'N'
No, the index contains an entry for each data row in the data table.
'Y'
Yes, the index might not contain an entry for each data row in the table.
'X'
This index excludes entries for data rows in which the key column contains the NULL value.
End of change