The index glossary query returns the characteristics of the index and indexspace which are referenced by the specified index name. In addition to specifying the table indexed and the columns used, it returns the such information as the freepage and percent free parameters, what storage group, bufferpool, and/or ICF catalog the indexspace uses and information about any indexspace partitions.
View type: DAQDB2390Index
Modifications:
Change the search criteria of the index name using the = (equals), IN or LIKE elements in the SQL WHERE clause. The WHERE clause may be changed to locate all indices or indexspaces which match the specified characteristics. In order to provide unique DB2/390 index names, the name has the format <>RelSystem:CreatorID:::IndexName<variation:revision>.
To find all DB2/390 indexes which have a specific simple name, use the LIKE clause with % as either or both of the first two qualifiers. To speed up your query, use % to include the colons and separators. For example, if you want all indexes starting "CUST", specify LIKE '%::CUST%'.
#MODIFIED#
#VERSION#
#SQL STATEMENT#
SELECT DISTINCT
i.db2390Index,
i.creatorID,
i.inRelationalSystem..relationalSystem,
i.inPhysicalDesign..db2390PhysicalDesign,
t.db2390Table,
i.partitionsTablespace..db2390Tablespace as partitionsTablespace,
k.keyDefinition,
k.keyColumnName,
k.ascDescFlag,
i.indexType,
i.uniqueFlag,
i.clusterFlag,
s.closeRule,
s.deferRule,
s.subPages,
-- the following apply to the index as a whole rather than any partition
s.usesBufferpool..db2390Bufferpool,
s.idxStorageGroup..db2390Storagegroup as indexStogroup,
s.idxPrimaryQty,
s.idxSecondaryQty,
s.idxICFCatalog..ICFCatalog as idxICFCatalog,
s.idxFreePage,
s.idxPercentFree,
s.numPartitions,
-- the following apply to an index partition
s.idxPartNumber,
s.idxPartStogroup..db2390Storagegroup as partitionStogroup,
s.idxPartPrimaryQty,
s.idxPartSecondaryQty,
s.idxPartICFCatalog..ICFCatalog as idxPartICFCatalog,