SYSIBM.SYSINDEXES table

http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=%2Fcom.ibm.db2.doc.sqlref%2Fzindxs.htm

SYSIBM.SYSINDEXES table

Contains one row for every index.

Column name Data type Description Use
|NAME
VARCHAR(128)
NOT NULL
Name of the index. G
|CREATOR
VARCHAR(128)
NOT NULL
Authorization ID of the owner of the index. G
|TBNAME
VARCHAR(128)
NOT NULL
Name of the table on which the index is defined. G
|TBCREATOR
VARCHAR(128)
NOT NULL
Authorization ID of the owner of the table. G
UNIQUERULE
CHAR(1)
NOT NULL
Whether the index is unique:
D
No (duplicates are allowed)
U
Yes
P
Yes, and it is a primary index (As in prior releases of DB2, a value of P is used for primary keys that are used to enforce a referential constraint.)
C
Yes, and it is an index used to enforce UNIQUE constraint
N
Yes, and it is defined with UNIQUE WHERE NOT NULL
R
Yes, and it is an index used to enforce the uniqueness of a non-primary parent key
G
Yes, and it is an index used to enforce the uniqueness of values in a column defined as ROWID GENERATED BY DEFAULT.
G
COLCOUNT
SMALLINT
NOT NULL
The number of columns in the key. G
CLUSTERING
CHAR(1)
NOT NULL
Whether CLUSTER was specified for the index:
N
No
Y
Yes
G
CLUSTERED
CHAR(1)
NOT NULL
Whether the table is actually clustered by the index:
N
A significant number of rows are not in clustering order, or statistics have not been gathered.
Y
Most of the rows are in clustering order.
blank
Not applicable.

This is an updatable column that can also be changed by the RUNSTATS utility.

G
DBID
SMALLINT
NOT NULL
Internal identifier of the database. S
OBID
SMALLINT
NOT NULL
Internal identifier of the index fan set descriptor. S
ISOBID
SMALLINT
NOT NULL
Internal identifier of the index page set descriptor. S
|DBNAME
VARCHAR(24)
NOT NULL
Name of the database that contains the index. G
|INDEXSPACE
VARCHAR(24)
NOT NULL
Name of the index space. G
INTEGER
NOT NULL
Not used N
 
INTEGER
NOT NULL
Not used N
NLEAF
INTEGER
NOT NULL
Number of active leaf pages in the index. The value is -1 if statistics have not been gathered. This is an updatable column. S
NLEVELS
SMALLINT
NOT NULL
Number of levels in the index tree. If the index is partitioned, it is the maximum of the number of levels in the index tree for all the partitions. The value is -1 if statistics have not been gathered. This is an updatable column. S
BPOOL
CHAR(8)
NOT NULL
Name of the buffer pool used for the index. G
PGSIZE
SMALLINT
NOT NULL
Size, in bytes, of the leaf pages in the index: 256, 512, 1024, 2048, or 4096 G
ERASERULE
CHAR(1)
NOT NULL
Whether the data sets are erased when dropped. The value is meaningless if the index is partitioned:
N
No
Y
Yes
G
 
VARCHAR(24)
NOT NULL
Not used N
CLOSERULE
CHAR(1)
NOT NULL
Whether the data sets are candidates for closure when the limit on the number of open data sets is reached:
N
No
Y
Yes
G
SPACE
INTEGER
NOT NULL
Number of kilobytes of DASD storage allocated to the index, as determined by the last execution of the STOSPACE utility. The value is 0 if the index is not related to a storage group, or if STOSPACE has not been run. If the index space is partitioned, the value is the total kilobytes of DASD storage allocated to all partitions that are defined in a storage group. 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. G
CLUSTERRATIO
SMALLINT
NOT NULL WITH
DEFAULT
Percentage of rows that are in clustering order. For a partitioning index, it is the weighted average of all index partitions in terms of the number of rows in the partition. The value is 0 if statistics have not been gathered. The value is -2 if the index is for an auxiliary table. This is an updatable column. S
|CREATEDBY
VARCHAR(128)
NOT NULL WITH
DEFAULT
Primary authorization ID of the user who created the index. G
SMALLINT
NOT NULL
Internal use only I
 
SMALLINT
NOT NULL
Not used N
STATSTIME
TIMESTAMP
NOT NULL WITH
DEFAULT
If 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’. This is an updatable column. G
INDEXTYPE
CHAR(1)
NOT NULL WITH
DEFAULT
The index type:

2
Type 2 index
blank
Type 1 index
|D
|Data-partitioned secondary index
#P
#An index that is both partitioned and is a Partitioning index#(index that is on a table that uses table-controlled partitioning).
G
FIRSTKEYCARDF
FLOAT
NOT NULL WITH
DEFAULT -1
Number of distinct values of the first key column. This number is an estimate if updated while collecting statistics on a single partition. The value is -1 if statistics have not been gathered. This is an updatable column. S
FULLKEYCARDF
FLOAT
NOT NULL WITH
DEFAULT -1
Number of distinct values of the key. The value is -1 if statistics have not been gathered. This is an updatable column. S
CREATEDTS
TIMESTAMP
NOT NULL WITH
DEFAULT
Time when the CREATE statement was executed for the index. If the index was created in a DB2 release prior to Version 5, the value is ‘0001-01-01.00.00.00.000000’. 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, ALTEREDTS has the value of CREATEDTS. If the index was created in a DB2 release prior to Version 5, the value is ‘0001-01-01.00.00.00.000000’. G
PIECESIZE
INTEGER
NOT NULL
WITH DEFAULT
Maximum size of a data set in kilobytes for secondary indexes.A value of zero (0) indicates that the index is a partitioning index or that the index was created in a DB2 release prior to Version 5. G
COPY
CHAR(1)
NOT NULL WITH
DEFAULT ‘N’
Whether COPY YES was specified for the index, which indicates if the index can be copied and if SYSIBM.SYSLGRNX recording is enabled for the index.
N
No
Y
Yes
G
COPYLRSN
CHAR(6)
NOT NULL WITH
DEFAULT
X’000000000000′
FOR BIT DATA
The value can be either an RBA or LRSN. (LRSN is only for data sharing.) If the index is currently defined as COPY YES, the value is the RBA or LRSN when the index was created with COPY YES or altered to COPY YES, not the current RBA or LRSN. If the index is currently defined as COPY NO, the value is set to X’000000000000′ if the index was created with COPY NO; otherwise, if the index was altered to COPY NO, the value in COPYLRSN is not changed when the index is altered to COPY NO. G
CLUSTERRATIOF
FLOAT
NOT NULL WITH
DEFAULT
When multiplied by 100, the value of the column is the percentage of rows that are in clustering order. For example, a value of .9125 indicates 91.25%. For a partitioning index, it is the weighted average of all index partitions in terms of the number of rows in the partition. The value is 0 if statistics have not been gathered. The value is -2 if the index is for an auxiliary table. This is an updatable column. 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
|REMARKS
VARCHAR(762)
NOT NULL WITH
DEFAULT
A character field string provided by the user with the COMMENT statement. G
|PADDED ||

CHAR(1)
|NOT NULL WITH
|DEFAULT
|
|Indicates whether keys within the index are padded |for varying-length column data:|
|
Y

|

The index contains varying-length character or graphic data |and is PADDED (the varying-length columns are padded to their maximum |length).|

|

N

|

The index contains varying-length character or graphic data |and is NOT PADDED (the varying-length columns are not padded to their |maximum length). Index-only access to all column data is possible.|

|

blank

|

The index does not contain varying-length character or graphic|data. The value is blank for indexes that have been created or altered |prior to Version 8.|

|

|G
|VERSION ||

SMALLINT
|NOT NULL WITH
|DEFAULT
|
|The version of the data row format for this index. |A value of zero indicates that a version-creating alter has never |occurred against this index. |G
|OLDEST_VERSION ||

SMALLINT
|NOT NULL WITH
|DEFAULT
|
|The version number describing the oldest format |of data in the index space and any image copies of the index. |G
|CURRENT_VERSION ||

SMALLINT
|NOT NULL WITH
|DEFAULT
|
|The version number describing the newest format |of data in the index space. A zero indicates that the index space |has never had versioning. After the version number reaches the maximum |value, the number will wrap back to one. |G
|RELCREATED ||

CHAR(1)
|NOT NULL WITH
|DEFAULT
|
|Release of DB2 that was used to create the object, |blank for indexes created before Version 8. For all other values, |see Release dependency indicators. |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

Responder

Por favor, inicia sesión con uno de estos métodos para publicar tu comentario:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s