SYSIBM.SYSINDEXPART table

The SYSIBM.SYSINDEXPART table contains one row for each nonpartitioned secondary index (NPSI) and one row for each partition of a partitioning index or a data-partitioned secondary index.

Column name Data type Description Use
PARTITION
SMALLINT
NOT NULL
 Partition number; Zero if index is not partitioned. G
IXNAME
VARCHAR(128)
NOT NULL
 Name of the index. G
IXCREATOR
VARCHAR(128)
NOT NULL

The schema of the index.

 G
PQTY
INTEGER
NOT NULL
 For user-managed data sets, the value is the primary space allocation in units of 4KB storage blocks or -1.

PQTY is based on a value of PRIQTY in the appropriate CREATE or ALTER INDEX statement. Unlike PQTY, however, PRIQTY asks for space in 1KB units.

A value of -1 indicates that either of the following cases is true:

  • PRIQTY was not specified for a CREATE INDEX statement or for any subsequent ALTER INDEX statements.
  • -1 was the most recently specified value for PRIQTY, either on the CREATE INDEX statement or a subsequent ALTER INDEX statement.
G
SQTY
SMALLINT
NOT NULL
 For user-managed data sets, the value is the secondary space allocation in units of 4KB storage blocks or -1.

SQTY is based on a value of SECQTY in the appropriate CREATE or ALTER INDEX statement. Unlike SQTY, however, SECQTY asks for space in 1KB units.

A value of -1 indicates that either of the following cases is true:

  • SECQTY was not specified for a CREATE INDEX statement or for any subsequent ALTER INDEX statements.
  • -1 was the most recently specified value for SECQTY, either on the CREATE INDEX statement or a subsequent ALTER INDEX statement.

If the value does not fit into the column, the value of the column is 32767. See the description of column SECQTYI.

 G
STORTYPE
CHAR(1)
NOT NULL
 Type of storage allocation:
E
Explicit, and STORNAME names an integrated catalog facility catalog
I
Implicit, and STORNAME names a storage group
 G
STORNAME
VARCHAR(128)
NOT NULL
 Name of storage group or integrated catalog facility catalog used for space allocation.Start of changeBlank for the catalog indexes.End of change G
VCATNAME
VARCHAR(24)
NOT NULL
 Name of integrated catalog facility catalog used for space allocation. G
 CARD
INTEGER
NOT NULL
Not used N
 FAROFFPOS
INTEGER
NOT NULL
Not used N
LEAFDIST
INTEGER
NOT NULL
 100 times the average number of leaf pages between successive active leaf pages of the index. The value is -1 if statistics have not been gathered. The value is -2 if the index is an auxiliary index, a node ID index, or an XML index. S
 NEAROFFPOS
INTEGER
NOT NULL
Not used S
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.

 G
LIMITKEY
VARCHAR(512)
NOT NULL
FOR BIT DATA
 The high value of the limit key of the partition in an internal format. An empty string if the index is not partitioned or for a data-partitioned secondary index (DPSI).

If any column of the key has a field procedure, the internal format is the encoded form of the value.

 S
FREEPAGE
SMALLINT
NOT NULL
 Number of pages that are loaded before a page is left as free space. G
PCTFREE
SMALLINT
NOT NULL
 Percentage of each leaf or nonleaf page that is left as free space. G
SPACE
INTEGER
NOT NULL WITH
DEFAULT
 Number of kilobytes of DASD storage allocated to the index space partition, as determined by the last execution of the STOSPACE utility.
0
Start of changeThe STOSPACE or RUNSTATS utility has not been run or the data set for the index has been created during the first insert operation or when the LOAD utility was run.End of change
-1
The index was defined with the DEFINE NO clause, which defers the physical creation of the data sets until data is first inserted into the index, and data has yet to be inserted into the index.
Start of changeA non-negative valueEnd of change
Start of changeIndicates that the data sets for the index space are defined with the underlying data sets allocated.End of change
The value is updated by STOSPACE if the index is related to a storage group. The value is updated by RUNSTATS if the utility is executed as RUNSTATS INDEX with UPDATE(ALL) or UPDATE(SPACE).		 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
 
CHAR(1)
NOT NULL
 Not used N
GBPCACHE
CHAR(1)
NOT NULL WITH
DEFAULT
 Group buffer pool cache option specified for this index or index partition.
blank
Only changed pages are cached in the group buffer pool.
A
Changed and unchanged pages are cached in the group buffer pool.
N
No data is cached in the group buffer pool.
 G
FAROFFPOSF
FLOAT
NOT NULL WITH
DEFAULT -1
 Number of referred to rows far from optimal position because of an insert into a full page. The value is -1 if statistics have not been gathered. The value is -2 if the index is an auxiliary index, a node ID index, or an XML index. The column is not applicable for an index on an auxiliary table. S
NEAROFFPOSF
FLOAT
NOT NULL WITH
DEFAULT -1
 Number of referred to rows near, but not at optimal position, because of an insert into a full page. The value is -2 if the index is an auxiliary index, a node ID index, or an XML index. Not applicable for an index on an auxiliary table. S
CARDF
FLOAT
NOT NULL WITH
DEFAULT -1
 Start of changeNumber of RIDs in the index that refer to data rows or LOBs. The value is -1 if statistics have not been gathered.End of change S
SECQTYI
INTEGER
NOT NULL WITH
DEFAULT
 Secondary space allocation in units of 4KB storage. For user-managed data sets, the value is the secondary space allocation in units of 4KB blocks. G
IPREFIX
CHAR(1)
NOT NULL WITH
DEFAULT 'I'
 The first character of the instance qualifier for this index's data set name. 'I' or 'J' are the only valid characters for this field. The default is 'I'. G
ALTEREDTS
TIMESTAMP
NOT NULL WITH
DEFAULT
 Time when the most recent ALTER INDEX statement was executed for the index. If no ALTER INDEX statement has been applied, the value is '0001-01-01.00.00.00.000000'. G
SPACEF
FLOAT(8)
NOT NULL WITH
DEFAULT -1
 Kilobytes of DASD storage. The value is -1 if statistics have not been gathered. This is an updatable column. G
DSNUM
INTEGER
NOT NULL WITH
DEFAULT -1
 Number of data sets. The value is -1 if statistics have not been gathered. This is an updatable column. G
EXTENTS
INTEGER
NOT NULL WITH
DEFAULT -1
 Number of data set extents. The value is -1 if statistics have not been gathered. This is an updatable column. This value is only for the last DSNUM for the object. G
PSEUDO_DEL_
ENTRIES
INTEGER
NOT NULL WITH
DEFAULT -1
 Number of pseudo deleted entries (entries that are logically deleted but still physically present in the index). For a non-unique index, value is the number of RIDs that are pseudo deleted. For a unique index, the value is the number of keys and RIDs that are pseudo deleted. The value is -1 if statistics have not been gathered. This is an updatable column. G
LEAFNEAR
INTEGER
NOT NULL WITH
DEFAULT -1
 Number of leaf pages physically near previous leaf page for successive active leaf pages. The value is -1 if statistics have not been gathered. This is an updatable column. S
LEAFFAR
INTEGER
NOT NULL WITH
DEFAULT -1
 Number of leaf pages located physically far away from previous leaf pages for successive (active leaf) pages accessed in an index scan. The value is -1 if statistics have not been gathered. This is an updatable column. S
OLDEST_VERSION
SMALLINT
NOT NULL WITH
DEFAULT
 The version number describing the oldest format of data in the index part and any image copies of the index part. G
CREATEDTS
TIMESTAMP
NOT NULL WITH
DEFAULT -1
 Time when the partition was created. G
AVGKEYLEN
INTEGER
NOT NULL WITH
DEFAULT -1
 Average length of keys within the index. The value is -1 if statistics have not been gathered. G