Concurrency and compatibility for REBUILD INDEX

The REBUILD INDEX utility has certain concurrency and compatibility characteristics associated with it.

Db2 treats individual data and index partitions as distinct target objects. Utilities that operate on different partitions of the same table space or index space are compatible.

REBUILD INDEX SHRLEVEL CHANGE jobs cannot be run to rebuild indexes on the same table space concurrently. As an alternative, REBUILD INDEX can build indexes in parallel by specifying multiple indexes in a single utility statement. Concurrency for rebuilding indexes in different table space is still allowed, as is the concurrency in rebuilding different partitions of an index in a partitioned table space.

Restriction: REBUILD INDEX SHRLEVEL CHANGE should only be used to fix a broken or restricted index, or to build an index after DEFER. You should not use the REBUILD INDEX SHRLEVEL CHANGE utility to move an index to different volumes; instead you should use the online REORG utility. REBUILD INDEX SHRLEVEL CHANGE on a unique index will not allow the INSERT option, the DELETE option, or updates that affect the unique index.

Claims

The following table lists any claims or drains that the utility acquires and any restrictive states that are set on the target object.

Table 1. Claim classes of REBUILD INDEX operations.
Target REBUILD INDEX SHRLEVEL REFERENCE REBUILD INDEX PART SHRLEVEL REFERENCE REBUILD INDEX SHRLEVEL CHANGE
Table space or partition DW/UTRO DW/UTRO CR/UTRW
Partitioning index, data-partitioned secondary index, or physical partition1 DA/UTUT DA/UTUT CR/UTRW
Nonpartitioned secondary index2 DA/UTUT DR CR/UTRW
Logical partition of an index3 N/A DA/UTUT CR/UTRW
Legend:
  • CR - Claim the read claim class
  • DA - Drain all claim classes; no concurrent SQL access
  • DW - Drain the write claim class; concurrent access for SQL readers
  • DR - Drains the repeatable-read claim class
  • N/A - Not applicable
  • UTUT - Utility restrictive state; exclusive control
  • UTRO - Utility restrictive state; read-only access allowed
  • UTRW - Utility restrictive state; read and write access allowed
Note:
  1. Includes document ID indexes and node ID indexes over partitioned XML table spaces
  2. Includes document ID indexes and node ID indexes over nonpartitioned XML table spaces and XML indexes
  3. Includes logical partitions of an XML index over partitioned XML table spaces

Compatibility

The following table shows which utilities can run concurrently with REBUILD INDEX on the same target object. The target object can be an index space or a partition of an index space. If compatibility depends on particular options of a utility, that information is also shown. REBUILD INDEX does not set a utility restrictive state if the target object is DSNDB01.SYSUTILX.

Table 2. Compatibility of REBUILD INDEX with other utilities
Action REBUILD INDEX
CHECK DATA No
CHECK INDEX No
CHECK LOB Yes
COPY INDEX No
COPY TABLESPACE SHRLEVEL CHANGE No
COPY TABLESPACE SHRLEVEL REFERENCE Yes
DIAGNOSE Yes
LOAD No
MERGECOPY Yes
MODIFY Yes
QUIESCE No
REBUILD INDEX No
RECOVER INDEX No
RECOVER TABLESPACE No
REORG INDEX No
REORG TABLESPACE UNLOAD CONTINUE or PAUSE No
REORG TABLESPACE UNLOAD ONLY or EXTERNAL with cluster index No
REORG TABLESPACE UNLOAD ONLY or EXTERNAL without cluster index Yes
REPAIR LOCATE by KEY No
REPAIR LOCATE by RID DELETE or REPLACE No
REPAIR LOCATE by RID DUMP or VERIFY Yes
REPAIR LOCATE INDEX PAGE DUMP or VERIFY No
REPAIR LOCATE TABLESPACE or INDEX PAGE REPLACE No
REPAIR LOCATE TABLESPACE PAGE DUMP or VERIFY Yes
REPORT Yes
RUNSTATS INDEX No
RUNSTATS TABLESPACE Yes
STOSPACE Yes
UNLOAD Yes

To run REBUILD INDEX on SYSIBM.DSNLUX01 or SYSIBM.DSNLUX02, ensure that REBUILD INDEX is the only utility in the job step and the only utility that is running in the Db2 subsystem. Unloading a base table that has LOB columns is not compatible with REBUILD INDEX.