Index names and guidelines

Index names

The name for an index is an SQL identifier of up to 128 characters. You can qualify this name with an identifier, or schema, of up to 128 characters. An example index names is MYINDEX. For more information, see Identifiers in SQL.

The following rules apply to index names:

index-name

A qualified or unqualified name that designates an index.

A qualified index name is an authorization ID or schema name followed by a period and an SQL identifier.

An unqualified index name is an SQL identifier with an implicit qualifier. The implicit qualifier is an authorization ID, which is determined by the context in which the unqualified name appears as described by the rules in Unqualified object name resolution.

For an index on a declared temporary table, the qualifier must be SESSION.

The index space name is an eight-character name, which must be unique among names of all index spaces and table spaces in the database.

Sequence of index entries

The sequence of the index entries can be in ascending order or descending order. The ASC and DESC keywords of the CREATE INDEX statement indicate ascending and descending order. ASC is the default.

Indexes on tables with large objects

You can use indexes on tables with LOBs the same way that you use them on other tables, but consider the following facts:

• A LOB column cannot be a column in an index.
• An auxiliary table can have only one index. (An auxiliary table, which you create by using the SQL CREATE AUXILIARY TABLE statement, holds the data for a column that a base table defines.
• Indexes on auxiliary tables are different than indexes on base tables.

Creation of an index

If the table that you are indexing is empty, Db2 creates the index. However, Db2 does not actually create index entries until the table is loaded or rows are inserted. If the table is not empty, you can choose to have Db2 build the index when the CREATE INDEX statement is executed. Alternatively, you can defer the index build until later. Optimally, you should create the indexes on a table before loading the table. However, if your table already has data, choosing the DEFER option is preferred; you can build the index later by using the REBUILD INDEX utility.

Copies of an index

If your index is fairly large and needs the benefit of high availability, consider copying it for faster recovery. Specify the COPY YES clause on a CREATE INDEX or ALTER INDEX statement to allow the indexes to be copied. Db2 can then track the ranges of log records to apply during recovery, after the image copy of the index is restored. (The alternative to copying the index is to use the REBUILD INDEX utility, which might increase the amount of time that the index is unavailable to applications.)

Deferred allocation of index space data sets

When you execute a CREATE INDEX statement with the USING STOGROUP clause, Db2 generally defines the necessary VSAM data sets for the index space. In some cases, however, you might want to define an index without immediately allocating the data sets for the index space.

For example, you might be installing a software program that requires creation of many indexes, but your company might not need some of those indexes. You might prefer not to allocate data sets for indexes that you do not plan to use.

To defer the physical allocation of Db2-managed data sets, use the DEFINE NO clause of the CREATE INDEX statement. When you specify the DEFINE NO clause, Db2 defines the index but defers the allocation of data sets. The Db2 catalog table contains a record of the created index and an indication that the data sets are not yet allocated. Db2 allocates the data sets for the index space as needed when rows are inserted into the table on which the index is defined.