In this topic, online index reorganization
applies to an index reorganization that is run with the ALLOW
READ ACCESS or ALLOW WRITE ACCESS parameters.
These options allow you to access the
table while its indexes are being reorganized. During online index
reorganization with the REBUILD option, new indexes
are built as additional copies while the original indexes remain intact.
Concurrent transactions use the original indexes while the new indexes
are created. At the end of the reorganization operation, the original
indexes are replaced by the new indexes. Transactions that are committed
in the meantime are reflected in the new indexes after the replacement
of the original indexes. If the reorganization operation fails and
the transaction is rolled back, the original indexes remain intact.
During online index reorganization with the CLEANUP and RECLAIM
EXTENTS options, space is reclaimed and made available
for use for all objects in the table space.
An online index reorganization operation
can hold the following locks:
- To ensure access to table spaces, an IX-lock is acquired on the
table spaces affected by the reorganization operation. This includes
table spaces that hold the table, as well as partition, and index
objects.
- To prevent the affected table from being altered during reorganization,
an X alter table lock is acquired.
- A table lock is acquired and held throughout the reorganization
operation. The type of lock depends on the table type, access mode,
and reorganization option:
- For nonpartitioned tables:
- If ALLOW READ ACCESS is specified, a U-lock
is acquired on the table.
- If ALLOW WRITE ACCESS is specified, an IN-lock
is acquired on the table.
- If CLEANUP is
specified, an S-lock is acquired on the table for READ access, and
IX-lock for WRITE access.
- For partitioned tables, reorganization with ALLOW READ
ACCESS or ALLOW WRITE ACCESS is supported
at partition level only:
- If ALLOW READ ACCESS is specified, a U-lock
is acquired on the partition.
- If ALLOW WRITE ACCESS is specified, an IS-lock
is acquired on the partition.
- If CLEANUP is
specified, an S-lock is acquired on the partition for READ access,
and an IX-lock for WRITE access.
- An IS-lock is acquired on the table regardless of which access
mode or option is specified.
- An exclusive Z-lock on the table or partition is requested at
the end of index reorganization. If a partitioned table contains nonpartitioned
indexes, then the Z-lock is acquired on the table as well as the partition.
This lock suspends table and partition access to allow for the replacement
of the original indexes by the new indexes. This lock is held until
transactions that are committed during reorganization are reflected
in the new indexes.
- The IS table lock and NS row lock are acquired on the system catalog
table SYSIBM.SYSTABLES.
- For a partition level reorganization, IS table lock and NS row
lock are also acquired on the system catalog table SYSIBM.SYSDATAPARTITIONS.
- Certain internal locks might also be acquired during an online
index reorganization operation.
- Online index reorganization might have impact on concurrency if
the reorganization operation fails. For example, the reorganization
might fail due to insufficient memory, lack of disk space, or a lock
timeout. The reorganization transaction performs certain updates before
ending. To perform updates, reorganization must wait on existing transaction
to be committed. This delay might block other transactions in the
process. Starting in DB2® Version
9.7 Fix
Pack 1, reorganization requests a special drain lock on the index
object. Reorganization operations wait for existing transactions to
finish; however, new requests to access the index object are allowed.