The REORG TABLESPACE online utility reorganizes a table space, partition, or range of partitions to reclaim fragmented space and improve access performance. You can also run REORG TABLESPACE to materialize pending definition changes.
You can use the DSNACCOX stored procedure to get recommendations on when to run REORG TABLESPACE.
To avoid the cost of running the RUNSTATS utility after running REORG TABLESPACE, you can request that REORG collect inline statistics by specifying the STATISTICS option.
You can run REORG TABLESPACE on the table spaces in the Db2 catalog database (DSNDB06) and on some table spaces in the directory database (DSNDB01). You cannot run REORG TABLESPACE on any table space in the DSNDB07 database.
Do not execute REORG on an object if another member 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.
If you issue REORG TABLESPACE with the INITCDDS YES option, REORG does not reorganize the table space. REORG copies the existing compression dictionaries for a table space that is defined with DATA CAPTURE CHANGES into the compression dictionary data set (CDDS). This option applies only to a data sharing group in a GDPS® Continuous Availability with zero data loss (GDPS Continuous Availability with zero data loss) environment.
You can use the REORG TABLESPACE utility in conjunction with z/OS® DFSMS data set encryption to encrypt or decrypt table spaces and indexes. REORG TABLESPACE always allocates new Db2-managed data sets unless the REUSE option is specified.
|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.
FL 509 If the table space or partition is defined with compression, 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. If a table has DATA CAPTURE CHANGES active, any previously existing dictionary is written to the log. After REORG completes successfully, the utility updates the COMPRESS_USED column of the SYSTABLEPART catalog table with the type of the compression dictionary that was used on each target page set.
REORG TABLESPACE materializes pending limit key changes if you specify SHRLEVEL REFERENCE or CHANGE.
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
- DATAACCESS authority
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 and you create a mapping table, you must use a privilege set that includes DELETE, INSERT, and UPDATE privileges on the mapping table.
If 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.
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:
- Performs initialization and setup.
the table space and sorts data if a clustering index exists and the utility job does not include the
SORTDATA NO options. For SORTDATA processing, if you specify NOSYSREC, the utility passes rows in
memory to the RELOAD phase; otherwise, it writes them to a sequential data set.
Nonpartitioned 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.
- 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.
- Sorts index keys. The sorted keys are passed in memory to the BUILD phase.
- Builds indexes and updates index statistics.
- If parallel index build occurs, all activities that normally occur in both the SORT and BUILD phases occur in the SORTBLD phase instead.
- 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.
- Switches access to shadow copy of table space or partition. This phase occurs only if you specify SHRLEVEL REFERENCE or CHANGE.
- 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:
- Performs initialization and setup.
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.
- Switches access to shadow copy of table space or partition.
- 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.