Skip to Content
Information

This site will undergo a brief period of maintenance on Friday, 18 December at 12:30 AM Central/12:00 PM IST. During a 30 minute window, site availability may be intermittent.

Default language.

Information
Space announcement This space provides the same content as before, but the organization of the home page has changed. The content is now organized based on logical branches instead of legacy book titles. We hope that the new structure will help you quickly find the content that you need.

Object status for SHRLEVEL REFERENCE UNLOADONLY, SHRLEVEL REFERENCE, and SHRLEVEL CHANGE

For SHRLEVEL REFERENCE UNLOADONLY, SHRLEVEL REFERENCE, and SHRLEVEL CHANGE reorganizations, this section describes the initial status requirements and how REORG PLUS changes the status of the objects during and after the reorganization.

Related topics

Getting started

Serialization and concurrency

 

Warning

Important

When REORG PLUS invokes DSNUTILB, status checking and changing are handled by DSNUTILB. For information about status handling for these jobs, see the documentation for the REORG utility in the IBM Db2 Utility Guide and Reference.

Initial status

The database that is associated with the objects that are participating in the reorganization must have one of the following initial statuses requirements for SHRLEVEL REFERENCE UNLOADONLY, SHRLEVEL REFERENCE, and SHRLEVEL CHANGE:

Condition

Requirement

You are using a DDLIN data set or the REBALANCE command option

Read/write (RW)

All other SHRLEVEL CHANGE

RW

All other SHRLEVEL REFERENCE UNLOADONLY or SHRLEVEL REFERENCE

One of the following statuses:

  • RW
  • Read-only (RO)

The objects that you are reorganizing can be in any status except those that the Statuses that are not permitted for SHRLEVEL REFERENCE UNLOADONLY, SHRLEVEL REFERENCE, and SHRLEVEL CHANGE table describes. If the object is in a status that REORG PLUS does not permit, REORG PLUS terminates. An X in a column in this table indicates the following information:

  • For a full table space reorganization, the table space cannot be in that restrictive status (but any indexes, if applicable, can be unless otherwise noted).
  • For a partial table space reorganization, the partitions that are participating in the reorganization and any nonpartitioned indexes (if applicable) cannot be in that restrictive status (but partitioned indexes, if applicable, can be).
  • For an index reorganization, the index cannot be in that restrictive status.
Warning

Important

If an object is in RREPL status, REORG PLUS functions as follows:

  • If you specify SHRLEVEL CHANGE, REORG PLUS invokes DSNUTILB.
  • If you specify SHRLEVEL REFERENCE, REORG PLUS functions normally in most cases. However, if you specify SELECT, UPDATE, or DELETE for a partition that is in RREPL status, REORG PLUS terminates.

The following table describes the statuses that are not permitted for SHRLEVEL REFERENCE UNLOADONLY, SHRLEVEL REFERENCE, and SHRLEVEL CHANGE:

Status that is not permitted

SHRLEVEL REFERENCE UNLOADONLY reorganization fails

SHRLEVEL REFERENCE reorganization fails

SHRLEVEL CHANGE reorganization fails

AREST (advisory restart pending)

X

X

X

GRECP (group RECOVER pending)

 

X

X

LPL (logical page list)

X

X

X

PRO (persistent read only) 1

X

X

X

PSRBD (page set REBUILD pending)

X

X 2

X

RBDP (REBUILD pending)

X

X 2

X

RBDP* (logical part REBUILD pending)

X

X

X

RECP (RECOVER pending)

X

X

X

REFP (refresh pending)

X

X

X

RESTP (restart pending)

X

X

X

RO (read-only)

 

 

X

RREPL (read-or-replication-only)

X

X

X

UTRO (utility restrictive state, read-only access allowed)

X

X

X

UTRW (utility restrictive state, read/write access allowed)

X

X

X

UTUT (utility restrictive state, utility exclusive control)

X

X

X

WEPR (write page error range)

X

X

X

1 The reorganization terminates if one or more of the partitions being reorganized is in this status.

2 When reorganizing a LOB table space, REORG PLUS also fails if the associated auxiliary index is in this status.

Status changes for SHRLEVEL REFERENCE UNLOADONLY, SHRLEVEL REFERENCE, and SHRLEVEL CHANGE

The following tables list status changes (for the database and the table space or index space) that occur during specific phases of a SHRLEVEL REFERENCE UNLOADONLY, SHRLEVEL REFERENCE, or SHRLEVEL CHANGE reorganization. The following additional considerations apply:

  • For SHRLEVEL REFERENCE UNLOADONLY and SHRLEVEL REFERENCE, for a partial reorganization, REORG PLUS starts or stops only partitions that you specify with the PART option of the REORG command. REORG PLUS starts or stops nonpartitioned indexes in their entirety.
  • REORG PLUS does not set CHECK pending status for reorganizations that result in referential integrity violations.
  • If ENBLUTXX=Y is in use, for SHRLEVEL REFERENCE UNLOADONLY and SHRLEVEL REFERENCE, REORG PLUS sets UTRO status in UTILINIT phase on the object being reorganized and resets it only when reorganization completes. For SHRLEVEL CHANGE, REORG PLUS sets UTRW status on the object in UTILINIT phase and resets it only when the reorganization completes. For partial reorganizations, only the participating parts will be set to UTRO or UTRW status for SHRLEVEL REFERENCE (UNLOADONLY) or SHRLEVEL CHANGE respectively. 

The following table describes the status changes during a SHRLEVEL REFERENCE UNLOADONLY reorganization:

Phase

Status

UTILINIT and ANALYZE

The status of the spaces remains RW, RO, or UT (the initial status).

Beginning of UNLOAD

REORG PLUS starts the table space and all associated indexes (for a table space reorganization) or the index space (for an index reorganization) in RO status.

Beginning of RELOAD

  • REORG PLUS stops the table space and associated indexes (for a table space reorganization) or the index space (for an index reorganization).
  • Processing continues in the RELOAD and UTILTERM phases as with SHRLEVEL NONE.

The following table describes the status changes during a SHRLEVEL REFERENCE reorganization:

Phase

Status

UTILINIT and ANALYZE

The status of the spaces remains RW, RO, or UT (the initial status).

Beginning of UNLOAD (two-phase reorganization) or beginning of REORG (single-phase reorganization)

REORG PLUS starts the table space and all associated indexes (for a table space reorganization) or the index space (for an index reorganization) in RO status.

UTILTERM

  • REORG PLUS stops and sets the RECOVER pending status of the table space and all associated index spaces (for a table space reorganization) or the index space (for an index reorganization).

    After both of the following steps are complete, REORG PLUS starts the table space and index spaces in the status that they were in when the reorganization began, and resets the RECOVER pending status:

    • The rename or FASTSWITCH operations associated with the staging data sets are complete.
    • The REORG or COPY row is registered in the SYSIBM.SYSCOPY catalog table.
  • If you specify COPY YES, REORG PLUS resets the COPY pending status on the table space. If you specify COPY NO, REORG PLUS sets the COPY pending status on the table space. In either case, REORG PLUS also sets any indexes defined with COPY YES to informational COPY pending (ICOPY) status.
  • REORG PLUS resets REORG pending status for each object that was in REORG pending status before the reorganization.
  • If materializing pending definition changes during the reorganization, REORG PLUS resets advisory REORG-pending (AREOR) status.

The following table describes the status changes during a SHRLEVEL CHANGE reorganization:

Phase

Status

UTILINIT and ANALYZE

The status of the spaces remains RW, RO, or UT (the initial status).

Beginning of LOGFINAL

REORG PLUS prevents updates to the affected table and index spaces while applying the last of the log records.  1

REORG PLUS either creates the incremental copy data sets (if incremental copies are being taken) or updates the full copies, depending on the ICTYPE option. 1   

UTILTERM

  • REORG PLUS prevents any access to the objects.
  • REORG PLUS sets the RECOVER pending status on the table space and all associated index spaces (for a table space reorganization) or the index space (for an index reorganization).
  • After completing the rename or FASTSWITCH operations that are associated with the staging data sets and registering the REORG or COPY row in the SYSIBM.SYSCOPY catalog table, REORG PLUS allows all access to the objects to resume. The table space and index spaces remain in the status that they had when the reorganization began. REORG PLUS then resets the RECOVER pending status.  2
  • REORG PLUS resets REORG pending status for each object that was in REORG pending status before the reorganization.
  • If materializing pending definition changes during the reorganization, REORG PLUS resets advisory REORG-pending (AREOR) status.

1 For a partial reorganization, REORG PLUS prevents updates only to partitions that you specify (using the PART option of the REORG command) while it initializes XBM. In addition, REORG PLUS prevents updates to any nonpartitioned indexes.

2 If the value of the ORIGDISP installation option or the ORIGINALDISP command option is DELETE, REORG PLUS deletes the now-obsolete original data sets. If this option is set to RENAME and the FASTSWITCH process is not in effect, REORG PLUS renames the original data sets to the staging data set names. This renaming process preserves the space that the original data sets initially used. The process results in staging data sets that are ready for use in the next SHRLEVEL CHANGE reorganization. 

 

 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*

REORG PLUS for DB2 13.1