DB2 10.5 for Linux, UNIX, and Windows

REORG INDEXES/TABLE command

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.

Scope

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

Authorization

One of the following authorities:
  • SYSADM
  • SYSCTRL
  • SYSMAINT
  • DBADM
  • SQLADM
  • CONTROL privilege on the table.

Required connection

Database

Command syntax

Read syntax diagramSkip visual syntax diagram
>>-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-'        

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 on a data partitioned table. Reorganization of individual indexes are only supported for nonpartitioned indexes on a partitioned table. This parameter is not supported for block indexes.
FOR TABLE table-name
Specifies the name of the table on which the nonpartitioned 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. In place reorganization of tables cannot be used 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 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 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 defined on the table.) Partitioned table ON DATA PARTITION

ALLOW NO ACCESS,
ALLOW READ ACCESS1

REORG TABLE (there are nonpartitioned indexes 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 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.

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

The following items apply for a data partitioned table when the ON DATA PARTITION clause is specified with the REORG INDEXES ALL command:
  • Only the specified data partition is restricted to the access mode level. Users are allowed to read from and write to the other partitions of the table while the partitioned indexes of a specified partition are being reorganized.
    The following table lists the access modes supported and the concurrent access allowed on other partitions of the table when the ON DATA PARTITION clause is specified:
    Table 2. Access modes supported and concurrent access allowed when the ON DATA PARTITION clause is specified with REORG INDEXES ALL
    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
  • Only the partitioned indexes for the specified partition are reorganized. The nonpartitioned indexes on the partitioned table are not reorganized.

    If there are any nonpartitioned indexes on the table marked "invalid" or "for rebuild", these indexes are rebuilt before reorganization. If not, only the partitioned indexes on the specified partition are reorganized or rebuilt if the index object is marked "invalid" or "for rebuild".

  • Only partitioned indexes for the specified partition are cleaned when the CLEANUP or RECLAIM EXTENTS option is also specified.
The following table lists the supported access modes for index reorganization of partitioned and nonpartitioned tables:
Table 3. Supported access modes for index reorganization on partitioned and nonpartitioned table
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,
ALLOW READ ACCESS1,
ALLOW WRITE ACCESS

REORG INDEX Partitioned table Not applicable Any

ALLOW NO ACCESS,
ALLOW READ ACCESS1,
ALLOW WRITE 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,
ALLOW READ ACCESS1,
ALLOW WRITE ACCESS

REORG INDEXES Partitioned table With or without the ON DATA PARTITION clause CLEANUP or RECLAIM EXTENTS specified

ALLOW NO ACCESS,
ALLOW READ ACCESS1,
ALLOW WRITE ACCESS

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

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.

INDEX index-name REBUILD
The REBUILD option is the default and represents the same functionality 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 is dependent 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 freed up are available for reuse by indexes defined on this table only.
ALL
Specifies that indexes should be cleaned up by removing committed pseudo deleted keys and committed pseudo empty pages.

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.

PAGES
Specifies that committed pseudo empty pages should be removed from the index tree. This will not clean up pseudo deleted keys on pages that are not pseudo empty. Since it is only checking the pseudo empty leaf pages, it is considerably faster than using the ALL option in most cases.

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.

INDEX index-name 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, and then free these empty extents from exclusive use by the index object and makes the space available for use by other database objects within the table space. Extents are reclaimed from the index object back to the table space. ALLOW READ ACCESS is the default, however 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 may be available for queries until the replace operation starts, depending on the access clause, described next.
INPLACE
Reorganizes the table while permitting 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. 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.

STOP
Stop the in place REORG processing at its current point.
PAUSE
Suspend or pause in place REORG for the time being.
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 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.

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 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 reorganizing 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 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 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 defined on the table (except system-generated XML path indexes), the ALLOW READ ACCESS mode is the default mode and 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 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 being 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 being 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 reorganizing 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 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.

INDEXSCAN
For a clustering REORG an index scan will be used to re-order 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, 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.

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.

USE longtbspace-name
This is an optional parameter, which 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 will be 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 used for base reorg data will be used, unless the page sizes differ. In this situation, the DB2 database system will attempt 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 processed during reorganization are subject to compression using the existing dictionary. If the COMPRESS attribute is YES and a compression dictionary doesn't exist for the table, a dictionary will only be created (and the table compressed) in this scenario if the table is of a certain size (approximately 1 to 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 1 row in the table. If the COMPRESS attribute for the table is NO and the table has a compression dictionary, then reorg processing will preserve the dictionary and all the 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.

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 V9.7 or later, or if the table is migrated 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 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).

RESETDICTIONARY
If the COMPRESS attribute for the table is YES then a new row compression dictionary is built. All the rows processed during reorganization are subject to compression 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 will remove 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 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.

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 V9.7 or later, or if the table is migrated 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 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.
  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 reorganizing 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 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.

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 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 while resuming.
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 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.

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 specified in the db2nodes.cfg file. This is the default if a database partition clause is not specified.
EXCEPT
Specifies that operation is to be done on all database partitions specified in the db2nodes.cfg file, except those specified in the database partition list.

Partition selection clause

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

Examples

To reorganize a table to reclaim space and use the temporary table space mytemp1, enter the following command:
db2 reorg table homer.employee use mytemp1
To reorganize tables in a partition group consisting of database partitions 1, 2, 3, and 4 of a four-partition system, you can enter either of the following commands:
  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)
To clean up the pseudo deleted keys and pseudo empty pages in all the indexes on the EMPLOYEE table while allowing other transactions to read and update the table, enter:
   db2 reorg indexes all for table homer.employee allow write 
    access cleanup only
To clean up the pseudo empty pages in all the indexes on the EMPLOYEE table while allowing other transactions to read and update the table, enter:
   db2 reorg indexes all for table homer.employee allow write 
    access cleanup only pages
To reorganize the EMPLOYEE table using the system temporary table space TEMPSPACE1 as a work area, enter:
   db2 reorg table homer.employee use tempspace1
To start, pause, and resume an in place reorg of the EMPLOYEE table with the default schema HOMER, which is specified explicitly in previous examples, enter the following commands:
  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 additional keywords to specify read access only and to skip the truncation step, which share-locks the table.

Usage notes

Restrictions:
  • 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.
  • REORG TABLE cannot be used on a partitioned table in a DMS table space while an online backup of ANY table space in which the table resides, including LOBs and indexes, is being performed.
  • REORG TABLE cannot use an index that is based on an index extension.
  • If a table is in reorg pending state, an inplace reorg is not allowed on the table.
  • Concurrent table reorganization sharing the same temporary DMS table space is not supported.
  • If a table has an index with an expression-based key defined on it, inplace table reorganization is not supported.
  • Before running 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 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 will be marked invalid if the reorganization has reached or passed the replace phase for the first data partition. The index partitions for any data partition that has already reached or passed the replace phase will be marked invalid. Indexes will be rebuilt on the next access to the table or data partition.
    • If an error occurs during index reorganization when the ALLOW NO ACCESS mode is used, some indexes on the table might be left invalid. For nonpartitioned RID indexes on the table, only the index that is being reorganized at the time of the failure will be left invalid. For MDC tables with nonpartitioned block indexes, one or more of the block indexes might be left invalid if an error occurs. For MDC or ITC tables with partitioned indexes, only the index object on the data partition being reorganized will be left invalid. Any indexes marked invalid will be rebuilt on the next access to the table or data partition.
    • When a data partitioned table with only partitioned indexes defined on the table is in the reorg pending state, issuing a REORG TABLE command with the ON DATA PARTITION clause brings only the specified data partition out of the reorg pending state. To bring the remaining partitions of the table out of the reorg pending state, either issue REORG TABLE command on the entire table (without the ON DATA PARTITION clause), or issue a REORG TABLE command with the ON DATA PARTITION clause for each of the remaining partitions.

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

With DB2 V9.7 Fix Pack 1 and later, REORG TABLE commands and REORG INDEXES ALL commands can be issued on a data partitioned table to concurrently reorganize different data partitions or partitioned indexes on a partition. When concurrently reorganizing data partitions or the partitioned indexes on a partition, users can access the unaffected partitions but cannot access the affected partitions. All the following criteria must be met to issue REORG commands that operate concurrently on the same table:
  • Each REORG command must specify a different partition with the ON DATA PARTITION clause.
  • Each REORG command must use the ALLOW NO ACCESS mode restrict access to the data partitions.
  • The partitioned table must have only partitioned indexes if issuing 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 using concurrent REORG commands:
  • Using a REORG command without the ON DATA PARTITION clause on the table.
  • Using an ALTER TABLE statement on the table to add, attach, or detach a data partition.
  • Loading data into the table.
  • Performing an online backup that includes the table.

If the table 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.