REORG INDEXES/TABLE command using the ADMIN_CMD procedure

Scope

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

Authorization

Required connection

Database

Command syntax

Index clause

space-reclaim-options

Table clause

classic-options

inplace-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.

TABLE table-name

Specifies the table to reorganize. The table can be in a local or a remote database. The name or alias in the form: schema.table-name can be used. The schema is the user name under which the table was created. If you omit the schema name, the default schema is assumed.

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. Reorganization of tables cannot be used in place for MDC or ITC tables.

When the ON DATA PARTITION clause is specified for a table reorganization of a data partitioned table, only the specified data partition is reorganized:

• If there are no nonpartitioned indexes (except system-generated XML path indexes) defined on the table, the access mode applies only to the specified partition. Users are allowed to read from and write to the other partitions of the table.
• If there are nonpartitioned indexes that are defined on the table (excluding system-generated XML path indexes), the ALLOW NO ACCESS mode is the default and only supported access mode. In this case, the table is placed in ALLOW NO ACCESS mode. If ALLOW READ ACCESS is specified, SQL1548N is returned (SQLSTATE 5U047).

Table 1. Supported access mode for CLASSIC table reorganization on nonpartitioned and partitioned table

Command	Table type	Table partitioning clause	Supported access mode
REORG TABLE	Nonpartitioned table	Not applicable	ALLOW NO ACCESS, ALLOW READ ACCESS1
REORG TABLE	Partitioned table	Not specified	ALLOW NO ACCESS1
REORG TABLE (There are no indexes or only partitioned indexes that are defined on the table)	Partitioned table	ON DATA PARTITION	ALLOW NO ACCESS, ALLOW READ ACCESS1
REORG TABLE (there are nonpartitioned indexes that are defined on the table, excluding system-generated XML path indexes)	Partitioned table	ON DATA PARTITION	ALLOW NO ACCESS1

Note:

1. Default mode when an access clause is not specified.

For a data partitioned table, a table reorganization rebuilds the nonpartitioned indexes and partitioned indexes on the table after the table is reorganized. 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.

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.

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 frees 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 clause

CLASSIC

Reorganizes the table by creating a new copy of the table and then replaces the original table with the reorganized copy. Any indexes on the table are recreated after the replacement. The original table might be available for queries until the replace operation starts, depending on the access clause, described next.

INPLACE

Reorganizes the table while it permits user access.

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. Additionally, for a partitioned table, INPLACE table reorganization is allowed only if there are no nonpartitioned indexes (except system-generated XML path indexes) defined on the table and ON DATA PARTITION is specified. Only one data partition can be reorganized at a time. 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. For a partitioned table, if there is a paused or running in place reorganization on some partition, then before START can be issued for an INPLACE reorganization on another partition, the paused reorganization must be completed or stopped on that table.

In place table reorganization takes place asynchronously, and might not be effective immediately.

STOP

Stop the in place REORG processing at its current point.

PAUSE

Suspend or pause in place REORG at its current point.

RECLAIM EXTENTS

Specifies the table to reorganize and reclaim extents that are not being used. The table-name variable must specify a multidimensional clustering (MDC), insert time clustering (ITC) table, or column-organized tables. The name or alias in the form: schema.table-name can be used. The schema is the user name under which the table was created. If you omit the schema name, the default schema is assumed.

For REORG TABLE RECLAIM EXTENTS when the ON DATA PARTITION clause is specified, the access clause applies only 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.

ALLOW NO ACCESS

For REORG TABLE RECLAIM EXTENTS, specifies that no other users can access the table while the extents are being reclaimed.

ALLOW READ ACCESS

For REORG TABLE RECLAIM EXTENTS, specifies that other users can have read-only access to the table while the extents are being reclaimed.

ALLOW WRITE ACCESS

For REORG TABLE RECLAIM EXTENTS, specifies that other users can read from and write to the table while the extents are being reclaimed. This value is the default option.

classic options

ALLOW NO ACCESS

Specifies that no other users can access the table while the table is being reorganized.

The ALLOW NO ACCESS mode is the default and only supported access mode when you reorganize a partitioned table without the ON DATA PARTITION clause.

If the ON DATA PARTITION clause is specified for a data partitioned table, only the specified data partition is reorganized:

• If there are no nonpartitioned indexes that are defined on the table (except system-generated XML path indexes), only the specified partition is restricted to the ALLOW NO ACCESS mode. Users are allowed to read from and write to the other partitions of the table.
• If there are nonpartitioned indexes that are defined on the table (except system-generated XML path indexes), the ALLOW NO ACCESS mode is the default and only supported access mode. In this case, the table is placed in ALLOW NO ACCESS mode.

ALLOW READ ACCESS

Allow only read access to the table during reorganization.

The ALLOW READ ACCESS mode is the default mode for a nonpartitioned table.

If the ON DATA PARTITION clause is specified for a data partitioned table, only the specified data partition is reorganized:

• If there are no nonpartitioned indexes that are defined on the table (except system-generated XML path indexes), the ALLOW READ ACCESS mode is the default mode. Only the specified partition is restricted to the access mode level. Users are allowed to read from and write to the other partitions of the table.
• If there are nonpartitioned indexes that are defined on the table (except system-generated XML path indexes), the ALLOW READ ACCESS mode is not supported. If ALLOW READ ACCESS is specified in this case, SQL1548N is returned (SQLSTATE 5U047).

USE tbspace-name

Specifies the name of a system temporary table space in which to store a temporary copy of the table that is reorganized. If you do not provide a table space name, the database manager stores a working copy of the table in the table spaces that contain the table that is reorganized.

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.

INDEX index-name

Specifies the index to use when you reorganize the table. If you do not specify the fully qualified name in the form: schema.index-name, the default schema is assumed. The schema is the user name under which the index was created. The database manager uses the index to physically reorder the records in the table it is reorganizing.

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 that is specified is used. If you do not specify the name of an index, the records are reorganized without regard to order. However, if the table has a clustering index defined 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 by using the index index-name.

INDEXSCAN

For a clustering REORG, an index scan is used to reorder table records. Reorganize table rows by accessing the table through an index. The default method is to scan the table and sort the result to reorganize the table by using temporary table spaces as necessary. Even though the index keys are in sort order, scanning and sorting is typically faster than fetching rows by first reading the row identifier from an index.

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.

LONGLOBDATA

Long field and LOB data are to be reorganized.

The default is to avoid reorganizing these objects because it is time consuming and does not improve clustering. If compact is not specified on the long or lob columns, LONGLOBDATA may not reduce the size of the LOB object. However, running a reorganization with the LONGLOBDATA option on tables with XML columns will reclaim unused space and reduce the size of the XML storage object.

This parameter is required when you convert existing LOB data into inlined LOB data.

For tables with extended row size, the first offline REORG after a table is altered enforces LONGLOBDATA.

USE longtbspace-name

This parameter is optional and can be used to specify the name of a temporary table space to be used for rebuilding long data. If no temporary table space is specified for either the table object or for the long objects, the objects are constructed in the table space they currently reside. If a temporary table space is specified for the table but this parameter is not specified, then the table space that is used for base reorg data is used, However, if the page sizes differ, the Db2 database system attempts to choose a temporary container of the appropriate page size to create the long objects in.

If USE longtbspace-name is specified, USE tbspace-name must also be specified. If it is not, the longtbspace-name argument is ignored.

KEEPDICTIONARY

If the COMPRESS attribute for the table is YES and the table has a compression dictionary then no new dictionary is built. All the rows that are processed during reorganization are subject to compression by using the existing dictionary. If the COMPRESS attribute is YES and a compression dictionary does not exist for the table, a dictionary is created (and the table compressed) in this scenario only if the table is of a certain size (approximately 1 - 2 MB) and sufficient data exists within this table. If, instead, you explicitly state REORG RESETDICTIONARY, then a dictionary is built as long as there is at least one row in the table. If the COMPRESS attribute for the table is NO and the table has a compression dictionary, then reorg processing preserves the dictionary. All the rows in the newly reorganized table are in non-compressed format. It is not possible to compress some data such as LOB data not stored in the base table row.

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.

Table 4. REORG KEEPDICTIONARY

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.

Table 5. REORG KEEPDICTIONARY when 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.

Note:

1. A compression dictionary can be created for the XML storage object of a table only if the XML columns are added to the table in Db2 9.7 or later, or if the table is migrated by using the ADMIN_MOVE_TABLE stored procedure.

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 saves 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).

RESETDICTIONARY

If the COMPRESS attribute for the table is YES then a new row compression dictionary is built. All the rows that are processed during reorganization are subject to compression by using this new dictionary. This dictionary replaces any previous dictionary. If the COMPRESS attribute for the table is NO and the table does have an existing compression dictionary then reorg processing removes the dictionary and all rows in the newly reorganized table will be in noncompressed format. It is not possible to compress some data such as LOB data not stored in the base table row.

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.

Table 6. REORG RESETDICTIONARY

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 keeps the existing dictionary. Rows that are smaller in size than the internal minimum record length and rows that 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.

Table 7. REORG RESETDICTIONARY when 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.

Note:

1. A compression dictionary can be created for the XML storage object of a table only if the XML columns are added to the table in Db2 9.7 or later, or if the table is migrated by using an online table move.
2. If a dictionary exists and the compression attribute is enabled but there currently isn't any data in the table, the RESETDICTIONARY operation keeps the existing dictionary. Rows that are smaller in size than the internal minimum record length and rows that do not demonstrate a savings in record length when an attempt is made to compress them are considered insufficient in this case.
3. If DATA CAPTURE CHANGES option is specified on the CREATE TABLE or ALTER TABLE statements, the current data dictionary is kept (referred to as the historical compression dictionary).

inplace-options

ALLOW READ ACCESS

Allow only read access to the table during reorganization.

ALLOW WRITE ACCESS

Allow write access to the table during reorganization. This is the default behavior.

FULL

The table is reorganized to fill pages while respecting PCTFREE for the table. Optionally, when the INDEX clause is specified, row data is moved within the table to recluster the data. Overflow records are also converted to normal records as part of this process. This behavior is the default behavior.

INDEX index-name

Specifies the index to use when you reorganize the table. If you do not specify the fully qualified name in the form: schema.index-name, the default schema is assumed. The schema is the user name under which the index was created. The database manager uses the index to physically reorder the records in the table it is reorganizing.

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 that is specified is used. If you do not specify the name of an index, the records are reorganized without regard to order. However, if the table has a clustering index defined 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 by using the index index-name.

TRUNCATE TABLE

Reclaim all extents that are empty at the end of the table and return them to the table space. Space reclamation is run at the end of the in place reorganization. During truncation, the table is S-locked, preventing updates to the table. Updates are not possible even if ALLOW WRITE ACCESS is specified. This behavior is the default behavior.

NOTRUNCATE TABLE

This operation allows for write access through the entire reorganization when ALLOW WRITE ACCESS is specified. There is no attempt to reclaim space that is used by the table. Do not truncate the table after in place reorganization. During truncation, the table is S-locked.

START

Start the in place REORG processing. Because this option is the default, this keyword is optional.

RESUME

Continue or resume a previously paused in place table reorganization. When an online reorganization is resumed and you want the same options as when the reorganization was paused, you must specify those options again when you resume.

CLEANUP OVERFLOWS

An INPLACE CLEANUP OVERFLOWS reorganization traverses the table and searches for pointer or overflow records. Any record found is converted to a normal record by the operation. This operation improves performance for tables that have a significant number of pointer or overflow records. The operation does not result in a reduction of the size of the table.

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.

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 that are defined on the table.

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.

Example

CALL SYSPROC.ADMIN_CMD ('REORG TABLE employee 
   INDEX empid ON DBPARTITIONNUM (1,3,4)')

Usage notes

Information 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 in-place table reorganization, the status is recorded as PAUSED.

For the INPLACE table reorganization, if the table has two or fewer pages then the REORG operation is not performed. There will also be no entry in the history file for the REORG operation, and any snapshot or monitoring metrics will not display REORG information.

When you modify an index table too 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 that use 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 removes any fragmentation and restore physical clustering to the leaf pages. Use the REORGCHK command to help determine whether an index needs reorganizing. Be sure to complete all database operations and release all locks before you invoke index reorganization. This step can be done by issuing a COMMIT after you close 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 can be utilized for additional sorts that can accompany the table reorg process. However, the in-place table reorganization (online reorganization) does not rebuild the indexes. It is recommended that you issue a REORG INDEXES command after the completion of an in-place table reorganization. An in-place table reorganization is asynchronous. Therefore, care must be taken to ensure that the in-place table reorganization is complete before you issue the REORG INDEXES command. Issuing the REORG INDEXES command before the in-place 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 are modified so many times that data is fragmented and access performance is noticeably slow are candidates for the REORG TABLE command. You must also invoke this utility after you alter 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 you invoke REORG TABLE. This step can be done by issuing a COMMIT after you close all cursors opened WITH HOLD, or by issuing a ROLLBACK. After you reorganize a table, use RUNSTATS to update the table statistics, and REBIND to rebind the packages that use this table. The reorganize utility implicitly closes 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 P3

If the table contains mixed row format because the table value compression is 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 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.

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 is ordered according to the clustering index.

The PCTFREE value of a table determines the amount of free space that is designated per page. If the value is not set, the utility fills up as much space as possible on each page.

To complete a table space rollforward recovery after 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 step 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 might be recreated by the REORG INDEXES/TABLE command. For more information, see Recreation of indexes over XML data.

An in-place REORG operation might not be able to fully reclaim space in a table because it cannot move internal records.

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.