SYSKEYTARGETS catalog table

The SYSKEYTARGETS table contains one row for each key-target that is participating in an extended index definition. The schema is SYSIBM.

Table 1. SYSIBM.SYSKEYTARGETS table column descriptions
Column name Data type Description Use
IXNAME
VARCHAR(128)
NOT NULL
 Name of the index. G
IXSCHEMA
VARCHAR(128)
NOT NULL
 Qualifier of the index. G
KEYSEQ
SMALLINT
NOT NULL
 Numeric position of the key-target in the index. G
COLNO
SMALLINT
NOT NULL
 Numeric position of the column in the table if the expression is a single column. Otherwise the value is 0. For XML indexes, this field is also 0. G
ORDERING
CHAR(1)
NOT NULL
 Order of the key:
A
Ascending
 G
TYPESCHEMA
VARCHAR(128)
NOT NULL
 Schema of the data type. G
TYPENAME
VARCHAR(128)
NOT NULL
 Name of the data type. G
DATATYPEID
INTEGER
NOT NULL
 The internal ID of the data type.

The DATATYPEID value corresponds to one of the SQLTYPE values in SQLTYPE and SQLLEN fields of the SQLDA. However, the DATATYPEID value is not a reliable indicator of the nullability of the column. A column with an even DATATYPEID value might allow nulls, and a column with an odd DATATYPEID value might not allow nulls. To determine the nullability of the column, use the NULLS column value.

 G
SOURCETYPEID
INTEGER
NOT NULL
 For a built-in data type, this column contains 0. For a distinct type, this column contains the internal ID of the built-in type on which the distinct type is based. G
LENGTH
SMALLINT
NOT NULL
 The length attribute of the key-target or its precision for a decimal key-target. The number does not include the internal prefixes that are used to record the actual length and null states, when applicable.
data type
value of the LENGTH column
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
 G
LENGTH2
INTEGER
NOT NULL
 The maximum length of the data that is retrieved from the column. Possible values include the following values:
0
Not a ROWID column
40
For a ROWID column, the length of the value that is returned
 G
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.

 G
NULLS
CHAR(1)
NOT NULL
 Whether the key can contain null values:
N
No
Y
Yes. Y also indicates that the index is an XML index.
 G
CCSID
INTEGER
NOT NULL
 The CCSID of the key. CCSID contains 0 if the key is a non-character type key. G
SUBTYPE
CHAR(1)
NOT NULL
 SUBTYPE applies to character keys only and indicated the subtype of the data:
B
BIT data
M
MIXED data
S
SBCS data
blank
non-character data
 G
—
VARCHAR(512)
NOT NULL
FOR BIT DATA
 Internal use only. I
CREATEDTS
TIMESTAMP
NOT NULL
 The timestamp for when the key-target is created. G
RELCREATED
CHAR(1)
NOT NULL
 The release of Db2 in which the key-target is created. SeeRelease dependency indicators for values. G
IBMREQD
CHAR(1)
NOT NULL
 A value of Y indicates that the row was provided with the Db2 product code. For all other values, see Release dependency indicators.

The value in this field is not a reliable indicator of release dependencies. RELCREATED should be used instead.

 G
DERIVED_FROM
VARCHAR(4000)
NOT NULL
 For an 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. G
STATSTIME
TIMESTAMP
NOT NULL
WITH DEFAULT

If RUNSTATS or another utility with inline statistics updated the statistics, the date and time when the last utility invocation updated the statistics. The default value is '0001-01-01-00.00.00.000000'. The default value indicates that statistics were not collected. This column can be updated.

 G
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 collected for the second key target (the DOCID column). For all other key targets of the XML value index, a value of -2 is set. S
HIGH2KEY
VARCHAR(2000)
NOT NULL
WITH DEFAULT
FOR BIT DATA
 The second highest key-value. HIGH2KEY is an updatable column. S
LOW2KEY
VARCHAR(2000)
NOT NULL
WITH DEFAULT
FOR BIT DATA
 The second lowest key-value. LOW2KEY is an updatable column. S
STATS_FORMAT
CHAR(1)
NOT NULL
WITH DEFAULT
 The type of statistics that are gathered:
N
VARCHAR column statistical values are not padded
blank
Statistics have not been collects or VARCHAR column statistical values are padded
STATS_FORMAT is an updatable column.		 G