Defining index space storage
Generally, the CREATE INDEX statement creates an index space in the same Db2 database that contains the table on which the index is defined, even if you defer building the index.
- If you specify the USING VCAT clause for indexes that are not created on the Db2 catalog, you create and manage the data sets yourself.
- If you specify the DEFINE NO clause on a CREATE INDEX statement with the USING STOGROUP clause, Db2 defers the allocation of the data sets for the index space.
Procedure
To
define your index space storage:
Optionally, for indexes that are not on Db2 catalog tables, include the USING clause to specify whether you want Db2-managed or user-managed data sets. For Db2-managed data sets, you can also specify the primary and secondary space allocation parameters for the index or partition in the USING clause. If you do not specify USING, Db2 assigns the index data sets to the default storage groups with the default space attributes.
For indexes on Db2 catalog tables, Db2 defines and manages the index data sets. The data sets are defined in the same SMS environment that is used for the catalog data sets with default space attributes. If you specify the USING clause for indexes on the catalog, Db2 ignores that clause.
Results
Information about space allocation for the index is stored in the Db2 catalog table SYSIBM.SYSINDEXPART. Other information about the index is in the SYSIBM.SYSINDEXES table.