REORG INDEX/INDEXES command

The REORG INDEXES/INDEXES command reorganizes indexes.

You can reorganize all indexes that are defined on a table by rebuilding the index data into unfragmented, physically contiguous pages. On a data partitioned table, you can reorganize a specific nonpartitioned index on a partitioned table, or you can reorganize all the partitioned indexes on a specific data partition.

If you specify the CLEANUP option of the index clause, cleanup is performed without rebuilding the indexes. You can cleanup a specific index on a table, or you can cleanup all of the indexes on the table.

This command cannot be used against indexes on declared temporary tables or created temporary tables (SQLSTATE 42995).

Restriction: Online index reorganization with the REBUILD clause is not supported in Db2® pureScale® environments. Running the operation with the REBUILD clause generates an SQL1419N error.

Scope

This command affects all database partitions in the database partition group.

Authorization

One of the following authorities:

SYSADM or SCHEMAADM
SYSCTRL
SYSMAINT
DBADM
SQLADM
CONTROL privilege on the table.

Required connection

Database

Command syntax

Index clause

space-reclaim-options

Table partitioning clause

Database partition clause

Partition selection clause

Command parameters

INDEXES ALL FOR TABLE table-name

Specifies the table whose indexes are to be reorganized. The table can be in a local or a remote database.

INDEX index-name

Specifies an individual index to be reorganized.

Reorganization of individual indexes is supported in the following scenarios:

Nonpartitioned indexes on a data partitioned table that are not block indexes
Any index on any permanent table if CLEANUP ALL is specified and RECLAIM EXTENTS is not specified

FOR TABLE table-name: Specifies the name of the table on which the index index-name is created. This parameter is optional, given that index names are unique across the database.

Index clause

ALLOW NO ACCESS

For REORG INDEXES, specifies that no other users can access the table while the indexes are being reorganized. If the ON DATA PARTITION clause is specified for a partitioned table, only the specified partition is restricted to the access mode level.

For REORG INDEX, specifies that no other users can access the table while the index is being reorganized.

ALLOW READ ACCESS

For REORG INDEXES, specifies that other users can have read-only access to the table while the indexes are being reorganized. ALLOW READ ACCESS mode is not supported for REORG INDEXES of a partitioned table unless the CLEANUP or RECLAIM EXTENTS option or the ON DATA PARTITION clause is specified. If the ON DATA PARTITION clause is specified for a partitioned table, only the specified partition is restricted to the access mode level.

For REORG INDEX, specifies that can have read-only access to the table while the index is being reorganized.

ALLOW WRITE ACCESS

For REORG INDEXES, specifies that other users can read from and write to the table while the indexes are being reorganized. ALLOW WRITE ACCESS mode is not supported for a partitioned table unless the CLEANUP or RECLAIM EXTENTS option or the ON DATA PARTITION clause is specified. If the ON DATA PARTITION clause is specified for a partitioned table, only the specified partition is restricted to the access mode level.

For REORG INDEX, specifies that can read from and write to the table while the index is being reorganized.

ALLOW WRITE ACCESS mode is not supported for multidimensional clustering (MDC) or insert time clustering (ITC) tables or extended indexes unless the CLEANUP or RECLAIM EXTENTS option is specified.

The ALLOW WRITE ACCESS parameter is not supported for column-organized tables if you specify the REBUILD parameter.

The following items apply for a data partitioned table when the ON DATA PARTITION clause is specified with the REORG INDEXES ALL command:

Only the specified data partition is restricted to the access mode level. Users are allowed to read from and write to the other partitions of the table while the partitioned indexes of a specified partition are being reorganized.

The following table lists the access modes that are supported and the concurrent access that is allowed on other partitions of the table when the ON DATA PARTITION clause is specified:

Table 1. Access modes supported and concurrent access allowed when the ON DATA PARTITION clause is specified with REORG INDEXES ALL
Access mode	Concurrent access that is allowed on the specified partition	Concurrent access that is allowed on other partitions
ALLOW NO ACCESS	No access	Read and write access
ALLOW READ ACCESS	Read on the partition up until index is updated	Read and write access
ALLOW WRITE ACCESS	Read and write access on the partition up until index is updated	Read and write access

Only the partitioned indexes for the specified partition are reorganized. The nonpartitioned indexes on the partitioned table are not reorganized.
If there are any nonpartitioned indexes on the table marked "invalid" or "for rebuild", these indexes are rebuilt before reorganization. If not, only the partitioned indexes on the specified partition are reorganized or rebuilt if the index object is marked "invalid" or "for rebuild".
Only partitioned indexes for the specified partition are cleaned when the CLEANUP or RECLAIM EXTENTS option is also specified.

The following table lists the supported access modes for index reorganization of partitioned and nonpartitioned tables:

Table 2. Supported access modes for index reorganization on partitioned and nonpartitioned table
Command	Table type	Table partitioning clause	Additional parameters that are specified for index clause	Supported access mode
REORG INDEXES	Nonpartitioned table	Not applicable	Any	ALLOW NO ACCESS, ALLOW READ ACCESS¹, ALLOW WRITE ACCESS²
REORG INDEX	Nonpartitioned table	Not applicable	CLEANUP ALL	ALLOW NO ACCESS, ALLOW READ ACCESS¹, ALLOW WRITE ACCESS
REORG INDEX	Nonpartitioned index on partitioned table	Not applicable	Any	ALLOW NO ACCESS, ALLOW READ ACCESS¹, ALLOW WRITE ACCESS
REORG INDEX	Partitioned index on partitioned table	With or without the ON DATA PARTITION clause	CLEANUP ALL	ALLOW NO ACCESS, ALLOW READ ACCESS¹, ALLOW WRITE ACCESS
REORG INDEXES	Partitioned table	None	REBUILD (this is the default if none specified)	ALLOW NO ACCESS ¹
REORG INDEXES	Partitioned table	ON DATA PARTITION	REBUILD (this is the default if none specified)	ALLOW NO ACCESS, ALLOW READ ACCESS¹, ALLOW WRITE ACCESS
REORG INDEXES	Partitioned table	With or without the ON DATA PARTITION clause	CLEANUP or RECLAIM EXTENTS specified	ALLOW NO ACCESS, ALLOW READ ACCESS¹, ALLOW WRITE ACCESS

Note:

Default mode when an access clause is not specified.
ALLOW WRITE ACCESS is not supported for column-organized tables if you specify the REBUILD parameter.

Use the ALLOW READ ACCESS or ALLOW WRITE ACCESS option to allow other transactions either read-only or read-write access to the table while the indexes are being reorganized. No access to the table is allowed when you rebuild an index during the period in which the reorganized copies of the indexes are made available.

REBUILD: The REBUILD option is the default and represents the same functionality that is provided by index reorganization in previous releases when the CLEANUP and CONVERT clauses were not specified. The REBUILD option of index reorganization rebuilds the index data into physically contiguous pages. The default access mode depends on the table type.

Space-reclaim-options

CLEANUP: When CLEANUP is requested, a cleanup rather than a REBUILD is done. The indexes are not rebuilt and any pages that are freed up are available for reuse by indexes that are defined on this table only.
ALL: Specifies that indexes must be cleaned up by removing committed pseudo deleted keys and committed pseudo empty pages.
The CLEANUP ALL option frees committed pseudo empty pages, as well as remove committed pseudo deleted keys from pages that are not pseudo empty. This option also attempts to merge adjacent leaf pages if it results in a merged leaf page that has at least PCTFREE free space on the merged leaf page. PCTFREE is the percent free space that is defined for the index at index creation time. The default PCTFREE is 10 percent. If two pages can be merged, one of the pages are freed. The number of pseudo deleted keys in an index, except for the keys on pseudo empty pages, can be determined by running RUNSTATS and then selecting the NUMRIDS DELETED from SYSCAT.INDEXES. The ALL option cleans the NUMRIDS DELETED and the NUM EMPTY LEAFS if they are determined to be committed.
PAGES: Specifies that committed pseudo empty pages must be removed from the index tree. This step does not clean up pseudo deleted keys on pages that are not pseudo empty. Since it is checking only the pseudo empty leaf pages, it is considerably faster than using the ALL option in most cases.
The CLEANUP PAGES option searches for and free committed pseudo empty pages. A committed pseudo empty page is one where all the keys on the page are marked as deleted and all these deletions are known to be committed. The number of pseudo empty pages in an index can be determined by running RUNSTATS and looking at the NUM EMPTY LEAFS column in SYSCAT.INDEXES. The PAGES option cleans up the NUM EMPTY LEAFS if they are determined to be committed.
RECLAIM EXTENTS: Specifies the index to reorganize and reclaim extents that are not being used. This action moves index pages around within the index object to create empty extents. Then, this step free these empty extents from exclusive use by the index object and makes the space available for use by other database objects within the table space. Extents are reclaimed from the index object back to the table space. ALLOW READ ACCESS is the default, but all access modes are supported.

Table partitioning clause

ON DATA PARTITION partition-name

For data partitioned tables, specifies the data partition for the reorganization.

For Db2 9.7 Fix Pack 1 and later releases, the clause can be used with the REORG INDEXES ALL command to reorganize the partitioned indexes on a specific partition and the REORG TABLE command to reorganize data of a specific partition.

When you use the clause with a REORG TABLE or REORG INDEXES ALL command on a partitioned table, the reorganization fails and returns SQL2222N with reason code 1 if the partition partition-name does not exist for the specified table. The reorganization fails and returns SQL2222N with reason code 3 if the partition partition-name is in the attached or detached state.

If the REORG INDEX command is run with the ON DATA PARTITION clause for a nonpartitioned index, the reorganization fails and returns SQL2222N with reason code 2. If the REORG INDEX command is run for a nonpartitioned index without the CLEANUP ALL clause, or if the RECLAIM clause is also specified, then the reorganization fails and returns SQL0270N with reason code 89.

Database partition

ON DBPARTITIONNUM | ON DBPARTITIONNUMS: Perform operation on a set of database partitions.
ALL DBPARTITIONNUMS: Specifies that operation is to be done on all database partitions that are specified in the db2nodes.cfg file. This option is the default if a database partition clause is not specified.
EXCEPT: Specifies that operation is to be done on all database partitions that are specified in the db2nodes.cfg file, except those partitions specified in the database partition list.

Partition selection clause

db-partition-number1: Specifies a database partition number in the database partition list.
db-partition-number2: Specifies the second database partition number so that all database partitions from db-partition-number1 up to and including db-partition-number2 are included in the database partition list.

Examples

To clean up the pseudo deleted keys and pseudo empty pages in all the indexes on the EMPLOYEE table while you allow other transactions to read and update the table, enter:

   db2 reorg indexes all for table homer.employee allow write 
    access cleanup

To clean up the pseudo empty pages in all the indexes on the EMPLOYEE table while you allow other transactions to read and update the table, enter:

   db2 reorg indexes all for table homer.employee allow write 
    access cleanup pages

To clean up the pseudo-deleted keys and pseudo-empty pages in index EMPID on data partition PART1 of the data partitioned EMPLOYEE table, while allowing other transactions to read and update the table, enter:

   db2 reorg index EMPID on table HOMER.EMPLOYEE 
    cleanup all on data partition PART1

Usage notes

Restrictions:

The REORG utility does not support the use of nicknames.
Before you run a reorganization operation against a table to which event monitors write, you need to deactivate the event monitors on that table.
For data partitioned tables:
- Reorganization skips data partitions that are in a restricted state due to an attach or detach operation. If the ON DATA PARTITION clause is specified, that partition must be fully accessible.
- If an error occurs during index reorganization when the ALLOW NO ACCESS mode is used, some indexes on the table might be left invalid. For nonpartitioned RID indexes on the table, only the index that is being reorganized at the time of the failure is left invalid. For MDC tables with nonpartitioned block indexes, one or more of the block indexes might be left invalid if an error occurs. For MDC or ITC tables with partitioned indexes, only the index object on the data partition that is reorganized is left invalid. Any indexes marked invalid will be rebuilt on the next access to the table or data partition.

Information about the current progress of table and index reorganization is written to the history file for database activity. The history file contains a record for each reorganization event. To view this file, execute the LIST HISTORY command for the database that contains the table you are reorganizing.

You can also use table snapshots to monitor the progress of table and index reorganization. Table reorganization monitoring data is recorded regardless of the Database Monitor Table Switch setting.

If an error occurs, an SQLCA dump is written to the history file.

When you modify an index table too many times, the data in the indexes might become fragmented, limiting the effectiveness of index page prefetching. To resolve the index fragmentation a reorg that rebuilds the index is needed. However the impact of this fragmentation has been minimized by the introduction of read-ahead prefetching.

Note that reorg operations require various locks, depending on the type of operation (INPLACE or CLASSIC) and whether the ALLOW READ access or ALLOW WRITE options are specific. Lock-wait conditions can be monitored using traditional methods, see 'Diagnosing a lock wait problem'

When the REORG rebuilds the indexes on an MDC table, the Full_Block hint bits are not set. Because the Full_Block hint is not set, you might experience degraded insert performance if you insert rows from existing dimension values after the REORG completes and the DB2_TRUST_MDC_BLOCK_FULL_HINT registry variable is turned on. The insert performance automatically improves for each dimension value after an insert of that dimension value completes. For more information, see DB2_TRUST_MDC_BLOCK_FULL_HINT performance variable.

With Db2 9.7 Fix Pack 1 and later, REORG INDEXES ALL commands can be issued on a data partitioned table to concurrently reorganize different data partitions or partitioned indexes on a partition. When concurrently reorganizing data partitions or the partitioned indexes on a partition, users can access the unaffected partitions but cannot access the affected partitions. All the following criteria must be met to issue REORG commands that operate concurrently on the same table:

Each REORG command must specify a different partition with the ON DATA PARTITION clause.
Each REORG command must use the ALLOW NO ACCESS mode restrict access to the data partitions.

For a partitioned table T1 with no nonpartitioned indexes (except system-generated XML path indexes) and with partitions P1, P2, P3, and P4, the following REORG commands can run concurrently:

REORG INDEXES ALL FOR TABLE T1 ALLOW NO ACCESS ON DATA PARTITION P1
REORG TABLE T1 ALLOW NO ACCESS ON DATA PARTITION P2
REORG INDEXES ALL FOR TABLE T1 ALLOW NO ACCESS ON DATA PARTITION P3

Operations such as the following are not supported when you use concurrent REORG commands:

Using a REORG command without the ON DATA PARTITION clause on the table.
Using an ALTER TABLE statement on the table to add, attach, or detach a data partition.
Loading data into the table.
Performing an online backup that includes the table.

If the table is distributed across several database partitions, and the table or index reorganization fails on any of the affected database partitions, only the failing database partitions have the table or index reorganization rolled back.

If the reorganization is not successful, temporary files must not be deleted. The database manager uses these files to recover the database.

Indexes over XML data might be recreated by the REORG TABLE command. For more information, see Recreation of indexes over XML data.

A page map index is used internally to track pages within column-organized tables. A modification state index, which is used during index scans, might be created when creating indexes on column-organized tables. Part of space management for column-organized tables is managing the space that is used by indexes, including the page map and modification state indexes. Reorganizing the table generates pseudo-deleted keys in the page map indexes. Update operations result in pseudo-deleted keys in the modification state index. Update and delete operations result in pseudo-deleted keys in regular indexes. Consider running a REORG INDEXES command, specifying the CLEANUP and RECLAIM EXTENTS parameters, or a REORG INDEXES command with the CLEANUP parameter, to clean up pseudo-deleted keys. Automatic table maintenance, if enabled, manages the cleanup and space reclamation process for indexes.