DB2 10.5 for Linux, UNIX, and Windows
DB2 10.5 for Linux, UNIX, and Windows
Reorganizes an index or a table.
You can reorganize all indexes 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. This command cannot be used against indexes on declared temporary tables or created temporary tables (SQLSTATE 42995).
The table option reorganizes a table by reconstructing the rows to eliminate fragmented data, and by compacting information. On a partitioned table, you can reorganize a single partition.
This command affects all database partitions in the database partition group.
Database
>>-REORG--------------------------------------------------------> >--+-+-INDEXES ALL FOR TABLE--table-name------------+--| Index clause |-+--> | '-INDEX--index-name--+-----------------------+-' | | '-FOR TABLE--table-name-' | '-TABLE--table-name--| Table clause |--------------------------------' >--+-------------------------------+----------------------------> '-| Table partitioning clause |-' >--+-------------------------------+--------------------------->< '-| Database partition clause |-' Index clause .-REBUILD---------------. |--+--------------------+--+-----------------------+------------| +-ALLOW NO ACCESS----+ '-space-reclaim-options-' +-ALLOW WRITE ACCESS-+ '-ALLOW READ ACCESS--' space-reclaim-options |--+--------------------+--+-----------------+------------------| | .-ALL---. | '-RECLAIM EXTENTS-' '-CLEANUP--+-------+-' '-PAGES-' Table clause .-CLASSIC-. |--+-+---------+--| classic-options |--------+------------------| +-INPLACE--+-| inplace-options |-+--------+ | '-+-STOP--+-----------' | | '-PAUSE-' | | .-ALLOW WRITE ACCESS-. | '-RECLAIM EXTENTS--+--------------------+-' +-ALLOW READ ACCESS--+ '-ALLOW NO ACCESS----' classic-options |--+-------------------+--+-------------------+-----------------> +-ALLOW NO ACCESS---+ '-USE--tbspace-name-' '-ALLOW READ ACCESS-' >--+-------------------+--+-----------+-------------------------> '-INDEX--index-name-' '-INDEXSCAN-' >--+----------------------------------------+-------------------> '-LONGLOBDATA--+-----------------------+-' '-USE--longtbspace-name-' .-KEEPDICTIONARY--. >--+-----------------+------------------------------------------| '-RESETDICTIONARY-' inplace-options .-ALLOW WRITE ACCESS-. |--+--------------------+---------------------------------------> '-ALLOW READ ACCESS--' .-FULL-. .-TRUNCATE TABLE---. .-START--. >--+-+------+--+-------------------+--+------------------+-+--+--------+--| | '-INDEX--index-name-' '-NOTRUNCATE TABLE-' | '-RESUME-' '-CLEANUP OVERFLOWS-------------------------------------' Table partitioning clause |--ON DATA PARTITION--partition-name----------------------------| Database partition clause |--ON-----------------------------------------------------------> >--+-+-DBPARTITIONNUM--+--| Partition selection clause |-------------------------------------+--| | '-DBPARTITIONNUMS-' | '-ALL DBPARTITIONNUMS--+----------------------------------------------------------------+-' '-EXCEPT--+-DBPARTITIONNUM--+--| Partition selection clause |--)-' '-DBPARTITIONNUMS-' Partition selection clause .-,--------------------------------------------------. V | |--(----db-partition-number1--+--------------------------+-+--)--| '-TO--db-partition-number2-'
The RECLAIM EXTENTS parameter is the only parameter that is supported for column-organized tables.
For typed tables, the specified table name must be the name of the hierarchy's root table.
You cannot specify an index for the reorganization of a multidimensional clustering (MDC) or insert time clustering (ITC) table. In place reorganization of tables cannot be used for MDC or ITC tables.
| Command | Table type | Table partitioning clause | Supported access mode |
|---|---|---|---|
| REORG TABLE | Nonpartitioned table | Not applicable | ALLOW NO ACCESS, |
| REORG TABLE | Partitioned table | Not specified | ALLOW NO ACCESS1 |
| REORG TABLE (There are no indexes or only partitioned indexes defined on the table.) | Partitioned table | ON DATA PARTITION | ALLOW NO ACCESS, |
| REORG TABLE (there are nonpartitioned indexes defined on the table, excluding system-generated XML path indexes.) | Partitioned table | ON DATA PARTITION | ALLOW NO ACCESS1 |
For a data partitioned table, a table reorganization rebuilds the nonpartitioned indexes and partitioned indexes on the table after reorganizing the table. If the ON DATA PARTITION clause is used to reorganize a specific data partition of a data partitioned table, a table reorganization rebuilds the nonpartitioned indexes and partitioned indexes only for the specified partition.
For REORG INDEX, specifies that no other users can access the table while the nonpartitioned index is being reorganized.
For REORG INDEX, specifies that can have read-only access to the table while the nonpartitioned index is being reorganized.
For REORG INDEX, specifies that can read from and write to the table while the nonpartitioned 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.
| Access mode | Concurrent access allowed on the specified partition | Concurrent access 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 |
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".
| Command | Table type | Table partitioning clause | Additional parameters specified for index clause | Supported access mode |
|---|---|---|---|---|
| REORG INDEXES | Nonpartitioned table | Not applicable | Any | ALLOW NO ACCESS, |
| REORG INDEX | Partitioned table | Not applicable | Any | ALLOW NO ACCESS, |
| REORG INDEXES | Partitioned table | None | REBUILD (this is the default if none specified) | ALLOW NO ACCESS 1 |
| REORG INDEXES | Partitioned table | ON DATA PARTITION | REBUILD (this is the default if none specified) | ALLOW NO ACCESS, |
| REORG INDEXES | Partitioned table | With or without the ON DATA PARTITION clause | CLEANUP or RECLAIM EXTENTS specified | ALLOW NO ACCESS, |
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 rebuilding an index during the period in which the reorganized copies of the indexes are made available.
The CLEANUP ALL option will free committed pseudo empty pages, as well as remove committed pseudo deleted keys from pages that are not pseudo empty. This option will also try to merge adjacent leaf pages if doing so will result in a merged leaf page that has at least PCTFREE free space on the merged leaf page, where PCTFREE is the percent free space defined for the index at index creation time. The default PCTFREE is ten percent. If two pages can be merged, one of the pages will be freed. The number of pseudo deleted keys in an index , excluding those on pseudo empty pages, can be determined by running RUNSTATS and then selecting the NUMRIDS DELETED from SYSCAT.INDEXES. The ALL option will clean the NUMRIDS DELETED and the NUM EMPTY LEAFS if they are determined to be committed.
The CLEANUP PAGES option will search 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 indexes can be determined by running RUNSTATS and looking at the NUM EMPTY LEAFS column in SYSCAT.INDEXES. The PAGES option will clean the NUM EMPTY LEAFS if they are determined to be committed.
INPLACE table reorganization is allowed only on nonpartitioned, non-MDC and non-ITC tables without extended indexes and with no indexes defined over XML columns in the table. INPLACE table reorganization can be run only on tables that are at least three pages in size.
Before START can be issued for an INPLACE reorganization on a table, any paused or running in place reorganization must be completed or stopped on that table.
In place table reorganization takes place asynchronously, and might not be effective immediately.
In place table reorganization is not supported for range partitioned tables.
For REORG TABLE RECLAIM EXTENTS when the ON DATA PARTITION clause is specified, the access clause only applies to the named partition. Users can read from and write to the rest of the table while the extents on the specified partition are being reclaimed. This situation also applies to the default access levels.
The ALLOW NO ACCESS mode is the default and only supported access mode when reorganizing a partitioned table without the ON DATA PARTITION clause.
The ALLOW READ ACCESS mode is the default mode for a nonpartitioned table.
For an 8 KB, 16 KB, or 32 KB table object, if the page size of the system temporary table space that you specify does not match the page size of the table spaces in which the table data resides, the DB2® database product will try to find a temporary table space of the correct size of the LONG/LOB objects. Such a table space must exist for the reorganization to succeed.
For partitioned tables, the temporary table space is used as temporary storage for the reorganization of data partitions in the table. Reorganization of the entire partitioned table reorganizes a single data partition at a time. The temporary table space must be able to hold the largest data partition in the table, and not the entire table. When the ON DATA PARTITION clause is specified, the temporary table space must be able to hold the specified partition.
If you do not supply a table space name for a partitioned table, the table space where each data partition is located is used for temporary storage of that data partition. There must be enough free space in each data partition's table space to hold a copy of the data partition.
For an in place table reorganization, if a clustering index is defined on the table and an index is specified, it must be the clustering index. If the in place option is not specified, any index specified will be used. If you do not specify the name of an index, the records are reorganized without regard to order. If the table has a clustering index defined, however, and no index is specified, then the clustering index is used to cluster the table. You cannot specify an index if you are reorganizing an MDC or ITC table.
If a table reorganization uses both the INDEX and ON DATA PARTITION clauses, only the specified partition is reorganized using the index index-name.
For tables with extended row size, the default method of scanning and sorting the result to reorganize the table is not supported.
For indexes that are expression-based, the default method of scanning and sorting the result to reorganize the table is not supported.
This is not required even if the table contains long or LOB columns. The default is to avoid reorganizing these objects because it is time consuming and does not improve clustering. However, running a reorganization with the LONGLOBDATA option on tables with XML columns will reclaim unused space and thereby reduce the size of the XML storage object.
This parameter is required when converting existing LOB data into inlined LOB data.
For tables with extended row size, the first offline REORG after a table is altered enforces LONGLOBDATA.
If USE longtbspace-name is specified, USE tbspace-name must also be specified. If it is not, the longtbspace-name argument is ignored.
When the LONGLOBDATA option is not specified, only the table row data is reorganized. The following table describes the behavior of KEEPDICTIONARY syntax in REORG command when the LONGLOBDATA option is not specified.
| Compress | Dictionary Exists | Result; outcome |
|---|---|---|
| Y | Y | Preserve dictionary; rows compressed. |
| Y | N | Build dictionary; rows compressed |
| N | Y | Preserve dictionary; all rows uncompressed |
| N | N | No effect; all rows uncompressed |
The following table describes the behavior of KEEPDICTIONARY syntax in REORG command when the LONGLOBDATA option is specified.
| Compress | Table row data dictionary exists | XML storage object dictionary exists1 | Compression dictionary | Data compression |
|---|---|---|---|---|
| Y | Y | Y | Preserve dictionaries. | Existing data is compressed. New data will be compressed. |
| Y | Y | N | Preserve table row dictionary and create an XML storage object dictionary. | Existing data is compressed. New data will be compressed. |
| Y | N | Y | Create table row dictionary and preserve the XML dictionary. | Existing data is compressed. New data will be compressed. |
| Y | N | N | Create table row and XML dictionaries. | Existing data is compressed. New data will be compressed. |
| N | Y | Y | Preserve table row and XML dictionaries. | Table data is uncompressed. New data will be not be compressed. |
| N | Y | N | Preserve table row dictionary. | Table data is uncompressed. New data will be not be compressed. |
| N | N | Y | Preserve XML dictionary. | Table data is uncompressed. New data will be not be compressed. |
| N | N | N | No effect. | Table data is uncompressed. New data will be not be compressed. |
For any reinitialization or truncation of a table (such as for a replace operation), if the compress attribute for the table is NO, the dictionary is discarded if one exists. Conversely, if a dictionary exists and the compress attribute for the table is YES then a truncation will save the dictionary and not discard it. The dictionary is logged in its entirety for recovery purposes and for future support with data capture changes (that is, replication).
If the LONGLOBDATA option is not specified, only the table row data is reorganized. The following table describes the behavior of RESETDICTIONARY syntax in REORG command when the LONGLOBDATA option is not specified.
| Compress | Dictionary Exists | Result; outcome |
|---|---|---|
| Y | Y | Build new dictionary*; rows compressed. If DATA CAPTURE CHANGES option is specified on the CREATE TABLE or ALTER TABLE statements, the current dictionary is kept (referred to as the historical compression dictionary). |
| Y | N | Build new dictionary; rows compressed |
| N | Y | Remove dictionary; all rows uncompressed. If the DATA CAPTURE NONE option is specified on the CREATE TABLE or ALTER TABLE statements, the historical compression dictionary is also removed for the specified table. |
| N | N | No effect; all rows uncompressed |
* If a dictionary exists and the compression attribute is enabled but there currently isn't any data in the table, the RESETDICTIONARY operation will keep the existing dictionary. Rows which are smaller in size than the internal minimum record length and rows which do not demonstrate a savings in record length when an attempt is made to compress them are considered "insufficient" in this case.
The following table describes the behavior of RESETDICTIONARY syntax in REORG command when the LONGLOBDATA option is specified.
| Compress | Table row data dictionary exists | XML storage object dictionary exists1 | Data dictionary | Data compression |
|---|---|---|---|---|
| Y | Y | Y | Build dictionaries2 3. | Existing data is compressed. New data will be compressed. |
| Y | Y | N | Build new table row dictionary and create a new XML dictionary3. | Existing data is compressed. New data will be compressed. |
| Y | N | Y | Create table row data dictionary and build a new XML dictionary. | Existing data is compressed. New data will be compressed. |
| Y | N | N | Create dictionaries. | Existing data is compressed. New data will be compressed. |
| N | Y | Y | Remove dictionaries. Existing and new data is not compressed. | Existing table data is uncompressed. New data will be not be compressed. |
| N | Y | N | Remove table row dictionary. All data is uncompressed. |
Existing table data is uncompressed. New data will be not be compressed. |
| N | N | Y | Remove XML storage object dictionary. | Existing table data is uncompressed. New data will be not be compressed. |
| N | N | N | No effect. | Existing table data is uncompressed. New data will be not be compressed. |
For an in place table reorganization, if a clustering index is defined on the table and an index is specified, it must be the clustering index. If the in place option is not specified, any index specified will be used. If you do not specify the name of an index, the records are reorganized without regard to order. If the table has a clustering index defined, however, and no index is specified, then the clustering index is used to cluster the table. You cannot specify an index if you are reorganizing an MDC or ITC table.
If a table reorganization uses both the INDEX and ON DATA PARTITION clauses, only the specified partition is reorganized using the index index-name.
For DB2 V9.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 using 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 issued with the ON DATA PARTITION clause, the reorganization fails and returns SQL2222N with reason code 2.
The REORG TABLE command fails and returns SQL1549N (SQLSTATE 5U047) if the partitioned table is in the reorg pending state and there are nonpartitioned indexes defined on the table.
db2 reorg table homer.employee use mytemp1 db2 reorg table employee index empid on dbpartitionnum (1,3,4)
db2 reorg table homer.employee index homer.empid on all
dbpartitionnums except dbpartitionnum (2) db2 reorg indexes all for table homer.employee allow write
access cleanup only db2 reorg indexes all for table homer.employee allow write
access cleanup only pages db2 reorg table homer.employee use tempspace1 db2 reorg table employee index empid inplace start
db2 reorg table employee inplace pause
db2 reorg table homer.employee inplace allow read access
notruncate table resumeInformation about the current progress of table 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 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. For an inplace table reorganization, the status is recorded as PAUSED.
When an indexed table has been modified many times, the data in the indexes might become fragmented. If the table is clustered with respect to an index, the table and index can get out of cluster order. Both of these factors can adversely affect the performance of scans using the index, and can impact the effectiveness of index page prefetching. REORG INDEX or REORG INDEXES with the REBUILD option can be used to reorganize one or all of the indexes on a table. Index reorganization rebuild will remove any fragmentation and restore physical clustering to the leaf pages. Use the REORGCHK command to help determine if an index needs reorganizing. Be sure to complete all database operations and release all locks before invoking index reorganization. This can be done by issuing a COMMIT after closing all cursors opened WITH HOLD, or by issuing a ROLLBACK.
A classic table reorganization (offline reorganization) rebuilds the indexes during the last phase of the reorganization. When more than one temporary table space exists, it is possible that a temporary table space in addition to the one specified on the REORG TABLE command may be utilized for additional sorts that can accompanying table reorg processing. However, the inplace table reorganization (online reorganization) does not rebuild the indexes. It is recommended that you issue a REORG INDEXES command after the completion of an inplace table reorganization. An inplace table reorganization is asynchronous, therefore care must be taken to ensure that the inplace table reorganization is complete before issuing the REORG INDEXES command. Issuing the REORG INDEXES command before the inplace table reorganization is complete, might cause the reorganization to fail (SQLCODE -2219).
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.
Tables that have been modified so many times that data is fragmented and access performance is noticeably slow are candidates for the REORG TABLE command. You should also invoke this utility after altering the inline length of a structured type column in order to benefit from the altered inline length. Use the REORGCHK command to determine whether a table needs reorganizing. Be sure to complete all database operations and release all locks before invoking REORG TABLE. This can be done by issuing a COMMIT after closing all cursors opened WITH HOLD, or by issuing a ROLLBACK. After reorganizing a table, use RUNSTATS to update the table statistics, and REBIND to rebind the packages that use this table. The reorganize utility will implicitly close all the cursors.
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 P3If the table contains mixed row format because the table value compression has been activated or deactivated, an offline table reorganization can convert all the existing rows into the target row format.
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 will have the table or index reorganization rolled back.
If the reorganization is not successful, temporary files should not be deleted. The database manager uses these files to recover the database.
If the name of an index is specified, the database manager reorganizes the data according to the order in the index. To maximize performance, specify an index that is often used in SQL queries. If the name of an index is not specified, and if a clustering index exists, the data will be ordered according to the clustering index.
The PCTFREE value of a table determines the amount of free space designated per page. If the value has not been set, the utility will fill up as much space as possible on each page.
To complete a table space rollforward recovery following a table reorganization, both regular and large table spaces must be enabled for rollforward recovery.
If the table contains LOB columns that do not use the COMPACT option, the LOB DATA storage object can be significantly larger following table reorganization. This can be a result of the order in which the rows were reorganized, and the types of table spaces used (SMS or DMS).
Indexes over XML data may be recreated by the REORG INDEXES/TABLE command. For details, see "Recreation of indexes over XML data".
An inplace REORG operation might not be able to fully reclaim space in a table because it cannot move internal records.
You cannot create indexes on column-organized tables. However, an index is used internally to track pages within these tables, and an index might be used to enforce unique or primary key constraints. Part of space management for column-organized tables is managing the space that is consumed by the page map and unique enforcement indexes. Reorganizing the table generates pseudo-deleted keys in the page map indexes, which are cleaned up during a subsequent cleanup operation. Update and delete operations result in pseudo-deleted keys in any unique enforcement indexes. Consider running a REORG INDEX command, specifying the CLEANUP and RECLAIM EXTENTS parameters. Automatic table maintenance, if enabled, manages the cleanup and space reclamation process for indexes.
