Previous Topic: SYSTEM.FILEMAPNext Topic: SYSTEM.INDEXKEY

SYSTEM Tables and SYSCA Views › SYSTEM.INDEX

SYSTEM.INDEX

Description

A row of SYSTEM.INDEX represents an index that has been defined on a table with a CREATE INDEX statement.

View SYSCA.INDEX is defined on SYSTEM.INDEX.

Note: For more information, see SYSCA Objects.

Column name

Column description

Data type

Null specifi- cation

NAME

Index name.

CHAR(18)

NOT NULL

SCHEMA

Schema-name qualifier for the indexed table.

CHAR(18)

NOT NULL

TABLE

Name of the indexed table.

CHAR(18)

NOT NULL

SEGMENT

Segment containing the area where index entries are stored.

CHAR(8)

NOT NULL

AREA

Area where index entries are stored.

CHAR(18)

 

INDEXID

Internal index ID number. Index IDs are automatically assigned to each index and are unique within the index area.

SMALLINT

NOT NULL

CTIME

Date and time when the index was created.

TIMESTAMP

NOT NULL

NUMCOLUMNS

Number of columns in the index key.

SMALLINT

NOT NULL

IXCOLUMNS

Internal index key column number array. This array consists of 32 SMALLINT bytes.

BINARY(64)

NOT NULL

IXORDERS

Sort order indicator array consisting of 32 CHAR(1) bytes. Values are:

  • A—Ascending
  • D—Descending

CHAR(32)

NOT NULL

UNIQUE

Unique key indicator:

  • Y—Unique index
  • N—Not unique

CHAR(1)

NOT NULL

CLUSTER

Cluster indicator:

  • Y—Clustered index
  • N—Not clustered

CHAR(1)

NOT NULL

COMPRESS

Index entry compression indicator:

  • Y—Compressed
  • N—Not compressed

CHAR(1)

NOT NULL

IXBLKLENGTH

Index block length.

SMALLINT

NOT NULL

IXBLKCONTAINS

Number of keys in an index block.

SMALLINT

NOT NULL

DISPLACEMENT

Distance, in number of pages, an index entry can be stored from the referenced row.

SMALLINT

NOT NULL

NEXT

Offset to next db-key pointer (CALC key).

SMALLINT

NOT NULL

PRIOR

Offset to prior db-key pointer (CALC key).

SMALLINT

NOT NULL

NUMSETS

For non-CALC indexes, this value is:

  • 1, if the number of rows in the indexed table was greater than zero when statistics were last updated.
  • 0, if there were no rows in the indexed table when statistics were last updated.

For CALC indexes, this value is the number of target pages for indexed rows when statistics were last updated.

INTEGER

NOT NULL

AVGMEMROWS

For non-CALC indexes, this value is the same as the NUMROWS column value in the SYSTEM.TABLE row for the indexed table when statistics were last updated.

For CALC indexes, this value is the average number of indexed rows for each target page in the CALC index when statistics were last updated.

REAL

NOT NULL

LONGESTMEM

For non-CALC indexes, this value is the same as the NUMROWS column value in the SYSTEM.TABLE row for the indexed table when statistics were last updated.

For CALC indexes, this value is the highest number of indexed rows for the same target page when statistics were last updated.

INTEGER

NOT NULL

SECLONGMEM

For non-CALC indexes, this value is always 0.

For CALC indexes, this value is the second highest number of indexed rows for the same target page when statistics were last updated.

INTEGER

NOT NULL

NUMLONGMEM

For non-CALC indexes, this value is always 1.

For CALC indexes, this value is the number of target pages with LONGESTMEM indexed rows targeted to the page when statistics were last updated.

INTEGER

NOT NULL

AVGMEMPAGES

For non-CALC indexes, this value is the same as the NUMPAGES column value in the SYSTEM.TABLE row for the indexed table when statistics were last updated.

For CALC indexes, this value is the average number of distinct pages occupied by indexed rows that target to the same page when statistics were last updated. This average does not include pages accounted for in MAXMEMPAGES.

The nearer this value is to 1, the greater the efficiency of the index.

REAL

NOT NULL

MAXMEMPAGES

For non-CALC indexes, this value is:

  • 0, if AVGMEMPAGES was 20 or less when statistics were last updated
  • 0, if AVGMEMPAGES was more than 20 when statistics were last updated

For CALC indexes, this value is the number of target pages where the number of pages occupied by indexed rows that target to the same page occupied more than 20 pages when statistics were last updated. This average does not include pages accounted for in AVGMEMPAGES (above).

INTEGER

NOT NULL

AVGAMEMCLUSCNT

For a non-CALC index, the average number of I/Os required to read all rows of the indexed table associated with a referenced row when statistics were last updated, if one buffer page was available. This count includes I/O to read the bottom-level SR8.

For a CALC index, this value is 0.

REAL

NOT NULL

AVGBMEMCLUSCNT

For a non-CALC index, the average number of I/Os required to read all rows of the indexed table associated with a referenced row when statistics were last updated, if three buffer pages were available. This count includes I/O to read the bottom-level SR8.

For a CALC index, this value is 0.

REAL

NOT NULL

AVGCMEMCLUSCNT

For a non-CALC index, the average number of I/Os required to read all rows of the indexed table associated with a referenced row if when statistics were last updated, if five buffer pages were available. This count includes I/O to read the bottom-level SR8.

For a CALC index, this value is 0.

REAL

NOT NULL

AVGDMEMCLUSCNT

For a non-CALC index, the average number of I/Os required to read all rows of the indexed table associated with a referenced row when statistics were last updated, if 10 buffer pages were available. This count includes I/O to read the bottom-level SR8.

For a CALC index, this value is 0.

 

 

AVGEMEMCLUSCNT

For a non-CALC index, the average number of I/Os required to read all rows of the indexed table associated with a referenced row when statistics were last updated, if 20 buffer pages were available. This count includes I/O to read the bottom-level SR8.

For a CALC index, this value is 0.

REAL

NOT NULL

AVGSR8ROWS

For a non-CALC index, the number of SR8s in the index when statistics were last updated.

For a CALC index, this value is 0.

REAL

NOT NULL

LONGESTSR8

For a non-CALC index, the number of bottom-level SR8s in the index.

For a CALC index, this value is 0.

INTEGER

NOT NULL

SECLONGSR8

This value is always 0.

INTEGER

NOT NULL

NUMLONGSR8

For a non-CALC index, this value is 1.

For a CALC index, this value is 0.

INTEGER

NOT NULL

AVGSR8PAGES

For a non-CALC index, the average number of distinct pages occupied by bottom-level SR8s for the index when statistics were last updated, if the average is 1 to 20. If the average is more than 20, the value in this column is 0.

For a CALC index, this value is 0.

REAL

NOT NULL

MAXSR8PAGES

For a non-CALC index, if AVGSR8PAGES is more than 20, the value in this column is 1. Otherwise the value is 0.

For a CALC index, this value is 0.

INTEGER

NOT NULL

AVGSR8LEAFS

For a non-CALC index, the average number of bottom-level SR8s in the index when statistics were last updated.

For a CALC index, this value is 0.

REAL

NOT NULL

AVGSR8LEVELS

For a non-CALC index, the average highest level number in the index when statistics were last updated. Level number refers to the number of levels above the bottom level.

For a CALC index, this value is 0.

REAL

NOT NULL

AVGASR8CLUSCNT

For a non-CALC index, the average number of I/Os required to read all bottom-level SR8s in the index when statistics were last updated, if one buffer page was available.

For a CALC index, this value is 0.

REAL

NOT NULL

AVGBSR8CLUSCNT

For a non-CALC index, the average number of I/Os required to read all bottom-level SR8s in the index when statistics were last updated, if three buffer pages were available.

For a CALC index, this value is 0.

REAL

NOT NULL

AVGCSR8CLUSCNT

For a non-CALC index, the average number of I/Os required to read all bottom-level SR8s in the index when statistics were last updated, if five buffer pages were available.

For a CALC index, this value is 0.

REAL

NOT NULL

AVGDSR8CLUSCNT

For a non-CALC index, the average number of I/Os required to read all bottom-level SR8s in the index when statistics were last updated, if 10 buffer pages were available.

For a CALC index, this value is 0.

REAL

NOT NULL

AVGESR8CLUSCNT

For a non-CALC index, the average number of I/Os required to read all bottom-level SR8s in the index when statistics were last updated, if 20 buffer pages were available.

For a CALC index, this value is 0.

REAL

NOT NULL

NUMUNIQKEYS

The number of distinct key values in the index when statistics were last updated. For a unique index, this number should match NUMROWS in the SYSTEM.TABLE row for the underlying table.

INTEGER

NOT NULL

NUMNULLKEYS

Number of rows where all the index key columns in the row contained null key values when statistics were last updated.

INTEGER

NOT NULL

NUMLONGKEYS

Number of distinct index key values for which all referencing rows with the same key value the indexed table occupied more than 20 pages when statistics were last updated. This situation can occur only when referencing rows can contain duplicate key values.

INTEGER

NOT NULL

AVGDUPSPERKEY

For each distinct index key value, the average number of duplicate values when statistics were last updated. For a unique index, this is 1.

REAL

NOT NULL

AVGPAGESPERKEY

For each distinct index key value, the average number of pages containing rows of the indexed table with the key value when statistics were last updated. This average does not include pages accounted for in NUMLONGKEYS (above).

REAL

NOT NULL

PROCKEY

Table procedure key:

  • P—If procedure key

CHAR(1)

NOT NULL

PRIMEKEY

Primary key flag:

  • Y—If primary key

CHAR(1)

NOT NULL

FILLER

Reserved for future use.

BINARY(38)

NOT NULL