REORG TABLESPACE

The REORG TABLESPACE online utility reorganizes a table space to improve access performance and to reclaim fragmented space. In addition, the utility can reorganize a single partition or range of partitions of a partitioned table space.

You can specify the degree of access to your data during reorganization. If you specify REORG TABLESPACE UNLOAD EXTERNAL, the data is unloaded in a format that is acceptable to the LOAD utility of any DB2® subsystem. You can also delete rows during the REORG job by specifying the DISCARD option.

Start of changeTo avoid the cost of running the RUNSTATS utility afterward, you can also specify the STATISTICS option to collect inline statistics when you run the REORG TABLESPACE utility.End of change

You can determine when to run REORG for non-LOB table spaces by using the OFFPOSLIMIT or INDREFLIMIT catalog query options. If you specify the REPORTONLY option, REORG produces a report that indicates whether a REORG is recommended without actually performing the REORG. These options are not applicable and are disregarded if the target object is a directory table space.

Run the REORG TABLESPACE utility on a LOB table space to help increase the effectiveness of prefetch. For a LOB table space, REORG TABLESPACE performs these actions:

  • Removes embedded free space
  • Attempts to make LOB pages contiguous

Start of changeIf you specify SHRLEVEL REFERENCE, a REORG of a LOB table space makes LOB pages contiguous removes embedded free space, and reclaims physical space. End of change

Start of changeYou can run REORG TABLESPACE SHRLEVEL CHANGE on a LOB table space. REORG TABLESPACE SHRLEVEL CHANGE processes a LOB table space the same as REORG SHRLEVEL REFERENCE except the mapping table is ignored. The restriction for REORG TABLESPACE SHRLEVEL CHANGE on NOT LOGGED table spaces applies to LOB table spaces. REORG TABLESPACE SHRLEVEL CHANGE on a LOB table space uses shadow data sets and includes a LOG phase.End of change

Do not execute REORG on an object if another DB2 holds retained locks on the object or has long-running non-committing applications that use the object. You can use the DISPLAY GROUP command to determine whether a member status is failed. You can use the DISPLAY DATABASE command with the LOCKS option to determine whether locks are held.

You can execute the REORG TABLESPACE utility on the table spaces in the DB2 catalog database (DSNDB06) and on some table spaces in the directory database (DSNDB01). It cannot be executed on any table space in the DSNDB07 database.

Output

The following table summaries the results of REORG TABLESPACE according to the type of REORG specified.
Table 1. Summary of REORG TABLESPACE output
Type of REORG specified Results
REORG TABLESPACE Reorganizes all data and all indexes.
REORG TABLESPACE PART n Reorganizes data for PART n of the table space and PART n of all partitioned indexes.
REORG TABLESPACE PART n:m Reorganizes data for PART n through PART m of the table space and PART n through PART m of all partitioned indexes.
Note: When SCOPE PENDING is also specified, the REORG TABLESPACE utility reorganizes the specified table space only if it is in REORG-pending or advisory REORG-pending status. For a partitioned table space, REORG TABLESPACE SCOPE PENDING reorganizes only the partitions that are in REORG-pending or advisory REORG-pending status.

If the table space or partition has the COMPRESS YES attribute, the data is compressed when it is reloaded. If you specify the KEEPDICTIONARY option of REORG, the current dictionary is used; otherwise a new dictionary is built.

Authorization required

To execute this utility on a user table space, you must use a privilege set that includes one of the following authorities:

  • REORG privilege for the database
  • DBADM or DBCTRL authority for the database. If the object on which the utility operates is in an implicitly created database, DBADM authority on the implicitly created database or DSNDB04 is required.
  • SYSCTRL authority
  • SYSADM authority
  • Start of changeDATAACCESS authorityEnd of change

To execute this utility on a table space in the catalog or directory, you must use a privilege set that includes one of the following authorities:

  • REORG privilege for the DSNDB06 (catalog) database
  • DBADM or DBCTRL authority for the DSNDB06 (catalog) database
  • Installation SYSOPR authority
  • SYSCTRL authority
  • SYSADM or Installation SYSADM authority
  • STATS privilege for the database is required if STATISTICS keyword is specified.

If you specify REORG TABLESPACE SHRLEVEL CHANGE, you must create a mapping table. You must use a privilege set that includes DELETE, INSERT, and UPDATE privileges on the mapping table.

Start of changeIf either the FLASHCOPY YES or FLASHCOPY CONSISTENT option is specified, the user ID that invokes the REORG TABLESPACE utility must have the authority to execute the DFSMSdss COPY command.End of change

To run REORG TABLESPACE STATISTICS REPORT YES, you must use a privilege set that includes the SELECT privilege on the catalog tables and tables for which statistics are to be gathered.

An authority other than installation SYSADM or installation SYSOPR can receive message DSNT500I resource unavailable, while trying to reorganize a table space in the catalog or directory. This message can be issued when the DSNDB06.SYSDBAUT or DSNDB06.SYSUSER catalog table space or one of the indexes is unavailable. If this problem occurs, run the REORG TABLESPACE utility again using an authorization ID with the installation SYSADM or installation SYSOPR authority.

If you use RACF® access control with multilevel security and REORG TABLESPACE is to process a table space that contains a table that has multilevel security with row-level granularity, you must be identified to RACF and have an accessible valid security label. You must also meet the following authorization requirements: .

  • For REORG statements that include the UNLOAD EXTERNAL option, each row is unloaded only if your security label dominates the data security label. If your security label does not dominate the data security label, the row is not unloaded, but DB2 does not issue an error message.
  • For REORG statements that include the DISCARD option, qualifying rows are discarded only if one of the following situations are true:
    • Write-down rules are in effect, you have write-down privilege, and your security label dominates the data's security label.
    • Write-down rules are not in effect and your security label dominates the data's security label.
    • Your security label is equivalent to the data security label.

Execution phases of REORG TABLESPACE

The REORG TABLESPACE utility operates in these phases:

UTILINIT
Performs initialization and setup.
UNLOAD
Unloads the table space and sorts data if a clustering index exists and the utility job includes either the SORTDATA or SHRLEVEL CHANGE options. If you specify NOSYSREC, the utility passes rows in memory to the RELOAD phase; otherwise, it writes them to a sequential data set. If PART SHRLEVEL REFERENCE or PART SHRLEVEL CHANGE is specified, during UNLOAD one or more subtasks unload nonpartitioned indexes and build shadow nonpartitioned indexes.
Start of changeNonpartitioned indexes are processed in one of two ways:
  • If PART SHRLEVEL REFERENCE or PART SHRLEVEL CHANGE is specified, during UNLOAD one or more subtasks unload nonpartitioned indexes and build shadow nonpartitioned indexes.
  • If PART SHRLEVEL REFERENCE or CHANGE is specified and SORTNPSI YES or AUTO is specified or subsystem parameter REORG_PART_SORT_NPSI is enabled, during UNLOAD one or more subtasks processes nonpartitioned secondary index keys from parts that are not within the scope of the REORG. These keys are routed to a sort process to be sorted with the keys from parts within the scope of the REORG. The shadow index is built from this sorted set of keys.
End of change
RELOAD
Reloads data from the sequential data set into the table space and creates full image copies if you specify COPYDDN, RECOVERYDDN, SHRLEVEL REFERENCE, or SHRLEVEL CHANGE. A subtask sorts the index keys. The utility also updates table and table space statistics.
SORT
Sorts index keys. The sorted keys are passed in memory to the BUILD phase.
BUILD
Builds indexes and updates index statistics.
SORTBLD
If parallel index build occurs, all activities that normally occur in both the SORT and BUILD phases occur in the SORTBLD phase instead.
LOG
Processes the log iteratively and appends changed pages to the full image copies. This phase occurs only if you specify SHRLEVEL CHANGE or SHRLEVEL REFERENCE PART x.
SWITCH
Switches access to shadow copy of table space or partition. This phase occurs only if you specify SHRLEVEL REFERENCE or CHANGE.
UTILTERM
Performs cleanup.

Execution phases of REORG TABLESPACE on a LOB table space

The REORG TABLESPACE utility operates in these phases when you run it on a LOB table space:

Phase
Description
UTILINIT
Performs initialization and setup.
REORGLOB

For SHRLEVEL REFERENCE, the utility unloads LOBs to a shadow data set. RECOVER-pending is not set on the LOB table space. Any error during this phase leaves he original data set intact.

SWITCH
Switches access to shadow copy of table space or partition.
UTILTERM
Performs cleanup.

You cannot restart REORG TABLESPACE on a LOB table space in the REORGLOB phase. Before executing REORG TABLESPACE SHRLEVEL NONE on a LOB table space that is defined with LOG NO, you should take a full image copy to ensure recoverability. For SHRLEVEL REFERENCE, an inline image copy is required to ensure recoverability.