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.
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.
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:
|
||||
Notes:
|
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.
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:
|
||||
Notes:
|
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.
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:
|
|||
Notes:
|
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.
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 |
- 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.
The following table shows which Db2 operations can be affected when 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:
|