SYSIBM.SYSKEYTARGETS table

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

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. 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
Start of changeTIMESTAMP WITHOUT TIME ZONEEnd of change
Start of changeThe integral part of ((p+1)/2) + 7 where p is the precision of the timestampEnd of change
Start of changeTIMESTAMP WITH TIME ZONEEnd of change
Start of changeThe integral part of ((p+1)/2) + 9 where p is the precision of the timestampEnd of change
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
 Start of changeThe scale of decimal data or number of fractional second digits of timestamp or timestamp with time zone data. Otherwise the value is 0.End of change

Start of changeIf the column is a timestamp type, the LENGTH is 10 and the SCALE is 0, the number of fractional second digits is 6.End of change

 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. 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 came from the basic machine-readable material (MRM) tape. 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

Start of changeIf RUNSTATS updated the statistics, the date and time when the last invocation of RUNSTATS updated the statistics. The default value is '0001-01-01-00.00.00.000000'. The default value indicates that statistics were not collected. This is an updatable column.End of change

 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. Start of changeSEnd of change
HIGH2KEY
VARCHAR(2000)
NOT NULL
WITH DEFAULT
FOR BIT DATA
 The second highest key-value. HIGH2KEY is an updatable column. Start of changeSEnd of change
LOW2KEY
VARCHAR(2000)
NOT NULL
WITH DEFAULT
FOR BIT DATA
 The second lowest key-value. LOW2KEY is an updatable column. Start of changeSEnd of change
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