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 lists any claims or drains that the utility acquires and any restrictive states that are set 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.
Notes:
  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 lists any claims or drains that the utility acquires and any restrictive states that are set 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:
  • CR: Claim read, concurrent access for SQL writers and readers
  • 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.
  • None: Any claim, drain, or restrictive state for this object does not change in this phase.
Notes:
  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 with SHRLEVEL CHANGE, the following table lists any claims or drains that the utility acquires and any restrictive states that are set 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 2 CR/UTRW DW/UTRO or DA/UTUT1 DA/UTUT
Index CR/UTRW DW/UTRO or DA/UTUT1 DA/UTUT
Partition of table space 2 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.
Notes:
  1. DA/UTUT applies if you specify DRAIN ALL.
  2. Start of changeREORG TABLESPACE SHRLEVEL CHANGE needs to enable full logging on the target page sets in the UTILINIT phase before any records are unloaded for proper log apply processing. This process requires quiescing all existing claimers on the target table space or partition objects so concurrent DML recognizes the full logging attribute. This process does not block new claimers from running on the target objects. The quiesce honors the default or specified DRAIN_WAIT duration. If REORG is unable to quiesce all existing claimers due to non-committing claimers, the utility considers this condition a drain failure and either fails or performs retry processing as specified by the RETRY parameter (or its default value). 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 4. 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
COPYTOCOPY Yes Yes Yes
DIAGNOSE Yes Yes Yes
LOAD No1 No No
MERGECOPY Yes Yes Yes
MODIFY RECOVERY Yes Yes Yes
MODIFY STATISTICS Yes Yes Yes
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
Notes: Start of change
  1. REORG SHRLEVEL CHANGE is compatible with LOAD RESUME YES SHRLEVEL CHANGE. However, REORG still requires exclusive control of the target objects in the last iteration in the LOG phase and in the SWITCH phase. Because LOAD RESUME YES SHRLEVEL CHANGE holds a write claim against the target objects for the entire duration of the utility, REORG cannot break in to complete processing until the concurrent LOAD utility completes.
End of change

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

Table 5. Db2 operations that are affected by reorganizing catalog table spaces
Catalog table space Actions that might not run concurrently
Any table space except SYSTSCPY, SYSTSCHX, SYSTSCKD, SYSTSSRG, and SYSTSCKS CREATE, ALTER, and DROP statements
SYSTSCPY1, SYSTSFAU, SYSTSCOL, SYSTSTSP, SYSTSTPT, SYSTSTAB, SYSTSIXS, SYSTSIXT, SYSTSIXR, SYSTSIPT, SYSTSREL, SYSTSFOR, SYSTSSYN, SYSTSFLD, SYSTSTAU, SYSTSDBA, SYSTSDBU, SYSTSKEY, SYSTSDBA, SYSTSDBU, SYSSTATS, SYSUSER, SYSHIST Utilities
SYSTSFAU, SYSTSCOL, SYSTSTSP, SYSTSTPT, SYSTSTAB, SYSTSIXS, SYSTSIXT, SYSTSIXR, SYSTSIPT, SYSTSREL, SYSTSFOR, SYSTSSYN, SYSTSFLD, SYSTSTAU, SYSTSDBA, SYSTSDBU, SYSTSKEY, SYSTSDBA, SYSTSDBU, SYSGPAUT, SYSTSPKL, SYSTSPLY, SYSTSPKG, SYSTSPKS, SYSTSPKX, SYSTSPVR, SYSTSPKY, SYSTSPKD, SYSTSPKA, SYSTSPLN, SYSTSPLA, SYSTSDBR, SYSTSPLD, SYSTSSTM, SYSUSER GRANT and REVOKE statements
SYSTSFAU, SYSTSCOL, SYSTSTSP, SYSTSTPT, SYSTSTAB, SYSTSIXS, SYSTSIXT, SYSTSIXR, SYSTSIPT, SYSTSREL, SYSTSFOR, SYSTSSYN, SYSTSFLD, SYSTSTAU, SYSTSDBA, SYSTSDBU, SYSTSKEY, SYSTSDBA, SYSTSDBU, SYSGPAUT, SYSTSPKL, SYSTSPLY, SYSTSPKG, SYSTSPKS, SYSTSPKX, SYSTSPVR, SYSTSPKY, SYSTSPKD, SYSTSPKA, SYSTSPLN, SYSTSPLA, SYSTSDBR, SYSTSPLD, SYSTSSTM, SYSSTATS, SYSUSER, SYSTSVEW, SYSTSVWT, SYSTSVTR, SYSTSVWD BIND and FREE commands
Notes:
  1. Most utilities that register their execution in SYSTSCPY can run concurrently with REORG SHRLEVEL CHANGE of SYSTSCPY.