REORG TABLE command

The REORG TABLE command 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.

Scope

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

Authorization

Using the REORG TABLE command requires one of these authorities:

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

Required connection

Database

Command syntax

Table clause

classic-options

inplace-options

Table partitioning clause

Database partition clause

Partition selection clause

Command parameters

TABLE table-name

Specifies the table to reorganize. The table can be in a local or a remote database. The name or alias uses the form schema.table-name. The schema is the username 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 no nonpartitioned indexes are defined on the table, the access mode applies only to the specified partition. This restriction excludes system-generated XML path indexes. Users are allowed to read from and write to the other partitions of the table.
If nonpartitioned indexes are defined on the table, then the ALLOW NO ACCESS mode is the default, and the only supported, access mode. This mode restriction excludes system-generated XML path indexes. 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 ACCESS¹
REORG TABLE	Partitioned table	Not specified	ALLOW NO ACCESS¹
REORG TABLE (No indexes exist, or only partitioned indexes that are defined on the table.)	Partitioned table	ON DATA PARTITION	ALLOW NO ACCESS, ALLOW READ ACCESS¹
REORG TABLE (Nonpartitioned indexes are defined on the table, excluding system-generated XML path indexes.)	Partitioned table	ON DATA PARTITION	ALLOW NO ACCESS¹

Note:

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.

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 re-created 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 restricted to nonpartitioned, non-MDC, and non-ITC tables that do not have extended indexes, expression-based indexes, or indexes that are defined over XML columns in the table. For a partitioned table, INPLACE table reorganization is allowed only when no nonpartitioned indexes are defined on the table, and when ON DATA PARTITION is specified. System-generated XML path indexes are excluded. 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 INPLACE reorganization must be completed or stopped on that table. If an INPLACE reorganization is paused or running on a partitioned table, that operation must stop before START can be issued for an INPLACE reorganization on another partition.

INPLACE 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 uses the form schema.table-name. The schema is the username name under which the table was created. If you omit the schema name, the default schema is assumed.

Note: The RECLAIM EXTENTS parameter currently does not work for large object (LOB) data in columnar tables.

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 no nonpartitioned indexes are defined on the table, then only the specified partition is restricted to the ALLOW NO ACCESS mode. This restriction excludes system-generated XML path indexes. Users are allowed to read from and write to the other partitions of the table.
If nonpartitioned indexes are defined on the table, then the ALLOW NO ACCESS mode is the default, and the only supported, access mode. This restriction excludes system-generated XML path indexes. In this case, the table is placed in ALLOW NO ACCESS mode.

ALLOW READ ACCESS

Allow read-only 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 no nonpartitioned indexes are defined on the table, then the ALLOW READ ACCESS mode is the default mode. This restriction excludes system-generated XML path indexes. 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 nonpartitioned indexes are defined on the table, then the ALLOW READ ACCESS mode is not supported. This restriction excludes system-generated XML path indexes. 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.

If the page size of the system temporary table space does not match the page size of the table spaces where the table data is located, the Db2® database product tries to find a temporary table space of the correct size. The correct size matches the size of the LONG/LOB objects. This action applies to 8 KB, 16 KB, or 32 KB table 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. Enough free space must exist 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 index name in the form schema.index-name, the default schema is assumed. The schema is the username under which the index was created. The database manager uses the index to physically reorder the records the table it is reorganizing.

For an INPLACE table reorganization, if a clustering index is defined on the table and an index is specified, it must be the clustering index. If the INPLACE 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 a clustering index is defined for the table 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 might not reduce the size of the LOB object. However, running a reorganization with the LONGLOBDATA option on tables with XML columns reclaims unused space and reduces the size of the XML storage object.

This parameter is needed when you convert existing LOB data into in-lined LOB data.

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

USE longtbspace-name

This parameter 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 where they are located. 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 is compressed. This operation occurs only if the table is of a certain size (approximately 1 - 2 MB) and sufficient data exists within this table. If you explicitly state REORG RESETDICTIONARY, and at least one row exists in the table, then a dictionary is built. If the COMPRESS attribute for the table is set to NO and the table has a compression dictionary, then reorg processing preserves the dictionary. All the rows in the newly reorganized table are uncompressed. 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 2. 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 3. REORG KEEPDICTIONARY when LONGLOBDATA option is specified.
Compress	Table row data dictionary exists	XML storage object dictionary exists¹	Compression dictionary	Data compression
Y	Y	Y	Preserve dictionaries.	Existing data is compressed. New data is compressed .
Y	Y	N	Preserve table row dictionary and create an XML storage object dictionary.	Existing data is compressed. New data is compressed
Y	N	Y	Create table row dictionary and preserve the XML dictionary.	Existing data is compressed. New data is compressed .
Y	N	N	Create table row and XML dictionaries.	Existing data is compressed. New data is compressed
N	Y	Y	Preserve table row and XML dictionaries.	Table data is uncompressed. New data is not compressed.
N	Y	N	Preserve table row dictionary.	Table data is uncompressed. New data is not compressed.
N	N	Y	Preserve XML dictionary.	Table data is uncompressed. New data is not compressed.
N	N	N	No effect.	Table data is uncompressed. New data is not compressed.

Note: A compression dictionary can be created for the XML storage object of a table. This operation requires that the XML columns are added to the table in Db2 9.7 or later, or that the table is migrated by using the ADMIN_MOVE_TABLE stored procedure.

For any reinitialization or truncation of a table, if the compress attribute for the table is set to NO, any existing dictionary is discarded. A replace operation is one example of a reinitialization or truncation of a table. Conversely, if a dictionary exists and the compress attribute for the table is set to YES, then a truncation saves the dictionary and does 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 set to NO and the table has an existing compression dictionary, then reorg processing removes the dictionary. All rows in the newly reorganized table are uncompressed. It is not possible to compress some data such as LOB data that is 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 4. 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 no data exists in the table then the RESETDICTIONARY operation keeps the existing dictionary. For this operation to occur, a dictionary must exist, and the compression attribute must be enabled. The dictionary is also kept if all rows are below the internal minimum record length or none of the rows have their length reduced by compression.

The following table describes the behavior of RESETDICTIONARY syntax in REORG command when the LONGLOBDATA option is specified.

Table 5. REORG RESETDICTIONARY when LONGLOBDATA option is specified.
Compress	Table row data dictionary exists	XML storage object dictionary exists¹	Data dictionary	Data compression
Y	Y	Y	Build dictionaries² ³.	Existing data is compressed. New data is compressed
Y	Y	N	Build new table row dictionary and create a new XML dictionary³.	Existing data is compressed. New data is compressed
Y	N	Y	Create table row data dictionary and build a new XML dictionary.	Existing data is compressed. New data is compressed
Y	N	N	Create dictionaries.	Existing data is compressed. New data is compressed
N	Y	Y	Remove dictionaries. Existing and new data is not compressed.	Existing table data is uncompressed. New data is not compressed.
N	Y	N	Remove table row dictionary. All data is uncompressed.	Existing table data is uncompressed. New data is not compressed.
N	N	Y	Remove XML storage object dictionary.	Existing table data is uncompressed. New data is not compressed.
N	N	N	No effect.	Existing table data is uncompressed. New data is not compressed.

Note:

A compression dictionary can be created for the XML storage object of a table. This operation requires that the XML columns are added to the table in Db2 9.7 or later, or that the table is migrated by using an online table move.
If no data exists in the table, then the RESETDICTIONARY operation keeps the existing dictionary. For this operation to occur, a dictionary must exist, and the compression attribute must be enabled. The dictionary is also kept if all rows are below the internal minimum record length or none of the rows have their length reduced by compression.
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

Allows read-only access to the table during reorganization.

ALLOW WRITE ACCESS

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

FULL

The table is reorganized to fill pages, while the PCTFREE percentage for the table is maintained. Optionally, when the INDEX clause is specified, row data is moved within the table to re 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 username under which the index was created. The database manager uses the index to physically reorder the records in the table it is reorganizing.

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 INPLACE 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. No action is taken to reclaim space that is used by the table. Do not truncate the table after inplace reorganization. During truncation, the table is S-locked.

START

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

RESUME

Continue or resume a previously paused INPLACE table reorganization.

Note: When an INPLACE reorganization is resumed, options that were specified in the original reorg invocation are not preserved during the reorg RESUME operation. If you want these options in the resumed reorg operation, you must specify the options again within the reorg RESUME invocation. For example, if the NOTRUNCATE or ALLOW READ ACCESS options were specified in the original reorg invocation, but not in the reorg RESUME invocation, then the resumed reorg operation does not use these options.

CLEANUP OVERFLOWS

An INPLACE CLEANUP OVERFLOWS reorganization traverses the table and searches for pointer or overflow records. Any record that is 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 TABLE command to reorganize data of a specific partition.

When you use the clause with a REORG TABLE 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.

The REORG TABLE command fails and returns SQL1549N (SQLSTATE 5U047) if the partitioned table is in the reorg pending state and nonpartitioned indexes are defined on the table.

Database partition

ON DBPARTITIONNUM | ON DBPARTITIONNUMS: Runs an 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

The following example shows how to reorganize a table by using the temporary table space mytemp1:

db2 reorg table homer.employee use mytemp1

The following examples are both useful for reorganizing tables in a partition group that consists of database partitions 1, 2, 3, and 4 of a four-partition system:

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)

The following example shows how to use the system temporary table space TEMPSPACE1 as a work area for reorganizing the EMPLOYEE table:

db2 reorg table homer.employee use tempspace1

The following example shows how to start, pause, and resume an in-place reorganization of the EMPLOYEE table with the default schema HOMER. This schema is specified explicitly in previous examples:

  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 resume

The command to resume the reorg contains extra keywords to specify read-only access and to skip the truncation step, which share-locks the table.

Usage notes

The following restrictions apply to use of the REORG TABLE command:

The REORG utility does not support the use of nicknames.
The REORG TABLE command is not supported for declared temporary tables or created temporary tables.
The REORG TABLE command cannot be used on views.
Reorganization of a table is not compatible with range-clustered tables because the range area of the table always remains clustered.
The REORG TABLE command cannot be used on a partitioned table in a DMS table space while an online backup of any table space where the table exists is running. This restriction includes backups of LOBs and indexes.
The REORG TABLE command cannot use an index that is based on an index extension.
If a table is in reorg pending state, an in-place reorganization is not allowed on the table.
Concurrent table reorganization that share temporary DMS table space is not supported.
If a table has an index with an expression-based key that is defined on it, in-place table reorganization is not supported.
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, the following restrictions apply to use of the REORG TABLE command:

The table must have an ACCESS_MODE in SYSCAT.TABLES of Full Access.
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 table reorganization, some indexes or index partitions might be left invalid. The nonpartitioned indexes of the table are marked invalid if reorganization reaches or passes the replace phase for the first data partition. The index partitions for any data partition that reaches or passes the replace phase are marked invalid. Indexes are rebuilt on the next access to the table or data partition.
Issuing a REORG TABLE command with the ON DATA PARTITION clause brings only the specified data partition out of the reorg pending state. This restriction applies when a data-partitioned table with only partitioned indexes that are defined on the table is in the reorg pending state. To bring the remaining partitions of the table out of the reorg pending state, issue the REORG TABLE command on the entire table (without the ON DATA PARTITION clause). You can also issue a REORG TABLE command with the ON DATA PARTITION clause for each of the remaining partitions.

Information about the current progress of 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, run 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 memory dump is written to the history file. For an in-place table reorganization, the status is recorded as PAUSED.

If the table is clustered with respect to an index, the table can become unclustered if the index is modified too many times. This situation can adversely affect the performance of select operations that fetch rows by using an index scan. A CLASSIC or INPLACE table reorganization can be used to recluster the data. However, the implementation of smarter data prefetching in Db2 reduces the impact of such a scenario, and the need for clustered tables with respect to an index.

For the INPLACE table reorganization, if the table has two or fewer pages then the REORG operation is not run. No entry is made in the history file for the REORG operation, and any snapshot or monitoring metrics do not display REORG information.

A classic table reorganization (offline reorganization) rebuilds the indexes during the last phase of the reorganization. When you are preparing to reorganize a table, you specify a temporary table space in the REORG TABLE command. If you have another temporary table space, you can use it for sorts that accompany the table reorganization. However, the in-place table reorganization (online reorganization) does not rebuild the indexes. It is a good idea to issue a REORG INDEXES command after the completion of an in-place table reorganization. An in-place table reorganization is asynchronous, so 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 start the REORG TABLE utility after you alter the inline length of a structured type column 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 start the REORG TABLE utility. 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.

With Db2 9.7 Fix Pack 1 and later, REORG TABLE commands that use the CLASSIC option 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.
The partitioned table must have only partitioned indexes if you issue REORG TABLE commands. No nonpartitioned indexes (except system-generated XML path indexes) can be defined on the table.

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.
Running an online backup that includes the table.

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.

When a table or index reorganization fails for a table that is distributed across several 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 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.