Concurrency and compatibility for REORG TABLESPACE

The REORG TABLESPACE utility has certain concurrency and compatibility characteristics associated with it.

DB2® treats individual data and index partitions, and individual logical partitions of nonpartitioning indexes as distinct target objects. Utilities that operate on different partitions of the same table space or index space are compatible. However, REORG SHRLEVEL CHANGE or REFERENCE on a partition or range of partitions rebuild entire nonpartitioned indexes; therefore, two REORG SHRLEVEL CHANGE or REFERENCE PART jobs on different partitions of the same table space are not compatible.

Restriction: You cannot run concurrent REORG TABLESPACE SHRLEVEL CHANGE PART integer on the same table space. Instead of submitting multiple jobs, you can merge the jobs into one job and specify a range using REORG TABLESPACE SHRLEVEL CHANGE PART integer1:integer2, or specify REORG TABLESPACE SHRLEVEL CHANGE SCOPE PENDING if multiple partitions are in a REORG-pending state.

This information includes a series of tables that show which claim classes REORG drains and any restrictive state that the utility sets on the target object.

For nonpartitioned indexes, if you specify SHRLEVEL NONE, REORG PART:

  • Drains only the logical partition (and the repeatable read class for the entire index)
  • Does not set the page set REBUILD-pending status (PSRCP)
  • Does not use PCTFREE or FREEPAGE attributes when inserting keys

Claim classes that REORG TABLESPACE drains

For SHRLEVEL NONE, the following table shows which claim classes REORG drains and any restrictive state that the utility sets on the target object. For each column, the table indicates the claim or drain that is acquired and the restrictive state that is set in the corresponding phase. UNLOAD CONTINUE and UNLOAD PAUSE, unlike UNLOAD ONLY, include the RELOAD phase and thus include the drains and restrictive states of that phase.

Table 1. Claim classes of REORG TABLESPACE SHRLEVEL NONE operations
Target UNLOAD phase of REORG RELOAD phase of REORG if UNLOAD CONTINUE or PAUSE UNLOAD phase of REORG PART RELOAD phase of REORG PART if UNLOAD CONTINUE or PAUSE
Table space, partition, or a range of partitions of a table space DW/UTRO DA/UTUT DW/UTRO DA/UTUT
Partitioning index, data-partitioned secondary index, or partition of either type of index1 DW/UTRO DA/UTUT DW/UTRO DA/UTUT
Nonpartitioned index2 DW/UTRO DA/UTUT None DR
Logical partition of nonpartitioning index3 None None DW/UTRO DA/UTUT
Legend:
  • DA: Drain all claim classes, no concurrent SQL access.
  • DR: Drain the repeatable read class, no concurrent access for SQL repeatable readers.
  • DW: Drain the write claim class, concurrent access for SQL readers.
  • UTUT: Utility restrictive state, exclusive control.
  • UTRO: Utility restrictive state, read-only access allowed.
  • None: Any claim, drain, or restrictive state for this object does not change in this phase.
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.

For SHRLEVEL REFERENCE, the following table shows which claim classes REORG drains and any restrictive state that the utility sets on the target object. For each column, the table indicates the claim or drain that is acquired and the restrictive state that is set in the corresponding phase.

Table 2. Claim classes of REORG TABLESPACE SHRLEVEL REFERENCE operations
Target UNLOAD phase of REORG SWITCH phase of REORG UNLOAD phase of REORG PART SWITCH phase of REORG PART
Table space or partition of table space DW/UTRO DA/UTUT DW/UTRO DA/UTUT
Partitioning index, data-partitioned secondary index, or partition of either1 DW/UTRO DA/UTUT DW/UTRO DA/UTUT
Nonpartitioned secondary index2 DW/UTRO DA/UTUT CR/UTRW DA/UTUT
Logical partition of nonpartitioning index3 None None DW/UTRO DA/UTUT
Legend:
  • Start of changeCR: Claim read, concurrent access for SQL writers and readersEnd of change
  • DA: Drain all claim classes, no concurrent SQL access.
  • DW: Drain the write claim class, concurrent access for SQL readers.
  • UTUT: Utility restrictive state, exclusive control.
  • UTRO: Utility restrictive state, read-only access allowed.
  • Start of changeUTRW: Utility restrictive state, read-write access allowed.End of change
  • None: Any claim, drain, or restrictive state for this object does not change in this phase.
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.

For REORG of an entire table space with SHRLEVEL CHANGE, the following table shows which claim classes REORG drains and any restrictive state that the utility sets on the target object.

Table 3. Claim classes of REORG TABLESPACE SHRLEVEL CHANGE operations
Target UNLOAD phase Last iteration of LOG phase SWITCH phase
Table space CR/UTRW1 DW/UTRO DA/UTUT
Index CR/UTRW1 DW/UTRO DA/UTUT
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.
  • UTUT: Utility restrictive state, exclusive control.
  • UTRO: Utility restrictive state, read-only access allowed.
  • UTRW: Utility restrictive state, read-write access allowed.

For REORG of a partition with SHRLEVEL CHANGE, the following table shows which claim classes REORG drains and any restrictive state that the utility sets on the target object.

Start of change
Table 4. Claim classes of REORG TABLESPACE SHRLEVEL CHANGE operations on a partition
Target UNLOAD phase Last iteration of LOG phase SWITCH phase
Partition of table space CR/UTRW DW/UTRO or DA/UTUT1 DA/UTUT
Partition of partitioned index CR/UTRW DW/UTRO or DA/UTUT1 DA/UTUT
Non-partitioned index CR/UTRW DW/UTRO or DA/UTUT1 DA/UTUT
Legend:
  • CR: Claim the read claim class.
  • DA: Drain all claim classes, no concurrent SQL access.
  • DDR: Dedrain the read claim class, no concurrent access for SQL repeatable readers.
  • DR: Drain the repeatable read class, no concurrent access for SQL repeatable readers.
  • DW: Drain the write claim class, concurrent access for SQL readers.
  • UTUT: Utility restrictive state, exclusive control.
  • UTRO: Utility restrictive state, read-only access allowed.
  • UTRW: Utility restrictive state, read-write access allowed.
  • None: Any claim, drain, or restrictive state for this object does not change in this phase.
Note:
  1. DA/UTUT applies if you specify DRAIN ALL.
End of change

Compatibility of REORG TABLESPACE with other utilities

The following table shows which utilities can run concurrently with REORG on the same target object. The target object can be a table space, an index space, or a partition of a table space or index space. If compatibility depends on particular options of a utility, that information is also shown.

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

The following table shows which DB2 operations can be affected when reorganizing catalog table spaces.

Table 6. DB2 operations that are affected by reorganizing catalog table spaces
Catalog table space Actions that might not run concurrently
Any table space except SYSCOPY and SYSSTR CREATE, ALTER, and DROP statements
SYSCOPY, SYSDBASE, SYSDBAUT, SYSSTATS, SYSUSER, SYSHIST Utilities
SYSDBASE, SYSDBAUT, SYSGPAUT, SYSPKAGE, SYSPLAN, SYSUSER GRANT and REVOKE statements
SYSDBAUT, SYSDBASE, SYSGPAUT, SYSPKAGE, SYSPLAN, SYSSTATS, SYSUSER, SYSVIEWS BIND and FREE commands
SYSPKAGE, SYSPLAN Plan or package execution