REORG INDEXES/TABLE command
The REORG INDEXES/TABLE command reorganizes an index or a table.
You can reorganize all indexes that are defined on a table by rebuilding the index data into unfragmented, physically contiguous pages. On a data partitioned table, you can reorganize a specific nonpartitioned index on a partitioned table, or you can reorganize all the partitioned indexes on a specific data partition.
If you specify the CLEANUP option of the index clause, cleanup is performed without rebuilding the indexes. You can cleanup a specific index on a table, or you can cleanup all of the indexes on the table.
This command cannot be used against indexes on declared temporary tables or created temporary tables (SQLSTATE 42995).
SQL1419N
.Scope
This command affects all database partitions in the database partition group.
Authorization
- SYSADM
- SYSCTRL
- SYSMAINT
- DBADM
- SQLADM
- CONTROL privilege on the table.
Required connection
Database
Command syntax
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 ACCESS1REORG TABLE Partitioned table Not specified ALLOW NO ACCESS1REORG 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 ACCESS1REORG 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:- 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.
- Only the specified data partition is restricted to the access mode level. Users are allowed to
read from and write to the other partitions of the table while the partitioned indexes of a
specified partition are being reorganized.The following table lists the access modes that are supported and the concurrent access that is allowed on other partitions of the table when the ON DATA PARTITION clause is specified:
Table 2. Access modes supported and concurrent access allowed when the ON DATA PARTITION clause is specified with REORG INDEXES ALL Access mode Concurrent access that is allowed on the specified partition Concurrent access that is allowed on other partitions ALLOW NO ACCESS No access Read and write access ALLOW READ ACCESS Read on the partition up until index is updated Read and write access ALLOW WRITE ACCESS Read and write access on the partition up until index is updated Read and write access - Only the partitioned indexes for the specified partition are reorganized. The nonpartitioned
indexes on the partitioned table are not reorganized.
If there are any nonpartitioned indexes on the table marked "invalid" or "for rebuild", these indexes are rebuilt before reorganization. If not, only the partitioned indexes on the specified partition are reorganized or rebuilt if the index object is marked "invalid" or "for rebuild".
- Only partitioned indexes for the specified partition are cleaned when the CLEANUP or RECLAIM EXTENTS option is also specified.
Command | Table type | Table partitioning clause | Additional parameters that are specified for index clause | Supported access mode |
---|---|---|---|---|
REORG INDEXES | Nonpartitioned table | Not applicable | Any |
ALLOW NO ACCESS,
ALLOW READ ACCESS1, ALLOW WRITE ACCESS2 |
REORG INDEX | Nonpartitioned table | Not applicable | CLEANUP ALL |
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 INDEX | Partitioned table | With or without the ON DATA PARTITION clause | CLEANUP ALL |
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 |
- Default mode when an access clause is not specified.
- ALLOW WRITE ACCESS is not supported for column-organized tables if you specify the REBUILD parameter.
Use the ALLOW READ ACCESS or ALLOW WRITE ACCESS option to allow other transactions either read-only or read-write access to the table while the indexes are being reorganized. No access to the table is allowed when you rebuild an index during the period in which the reorganized copies of the indexes are made available.
- REBUILD
- The REBUILD option is the default and represents the same functionality that is provided by index reorganization in previous releases when the CLEANUP and CONVERT clauses were not specified. The REBUILD option of index reorganization rebuilds the index data into physically contiguous pages. The default access mode depends on the table type.
Space-reclaim-options
- CLEANUP
- When CLEANUP is requested, a cleanup rather than a REBUILD is done. The indexes are not rebuilt and any pages that are freed up are available for reuse by indexes that are defined on this table only.
- ALL
- Specifies that indexes must be cleaned up by removing committed pseudo deleted keys and
committed pseudo empty pages.
The CLEANUP ALL option frees committed pseudo empty pages, as well as remove committed pseudo deleted keys from pages that are not pseudo empty. This option also attempts to merge adjacent leaf pages if it results in a merged leaf page that has at least PCTFREE free space on the merged leaf page. PCTFREE is the percent free space that is defined for the index at index creation time. The default PCTFREE is 10 percent. If two pages can be merged, one of the pages are freed. The number of pseudo deleted keys in an index, except for the keys on pseudo empty pages, can be determined by running RUNSTATS and then selecting the NUMRIDS DELETED from SYSCAT.INDEXES. The ALL option cleans the NUMRIDS DELETED and the NUM EMPTY LEAFS if they are determined to be committed.
- PAGES
- Specifies that committed pseudo empty pages must be removed from the index tree. This step does
not clean up pseudo deleted keys on pages that are not pseudo empty. Since it is checking only the
pseudo empty leaf pages, it is considerably faster than using the ALL option in
most cases.
The CLEANUP PAGES option searches for and free committed pseudo empty pages. A committed pseudo empty page is one where all the keys on the page are marked as deleted and all these deletions are known to be committed. The number of pseudo empty pages in an index can be determined by running RUNSTATS and looking at the NUM EMPTY LEAFS column in SYSCAT.INDEXES. The PAGES option cleans up the NUM EMPTY LEAFS if they are determined to be committed.
- RECLAIM EXTENTS
- Specifies the index to reorganize and reclaim extents that are not being used. This action moves index pages around within the index object to create empty extents. Then, this step 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.
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 isYES
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 theCOMPRESS
attribute isYES
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 theCOMPRESS
attribute for the table isNO
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:- 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 isYES
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 theCOMPRESS
attribute for the table isNO
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:- 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.
- 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.
- 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.
Examples
mytemp1
, enter the following command:
db2 reorg table homer.employee use mytemp1
db2 reorg table employee index empid on dbpartitionnum (1,3,4)
db2 reorg table homer.employee index homer.empid on all
dbpartitionnums except dbpartitionnum (2)
db2 reorg indexes all for table homer.employee allow write
access cleanup
db2 reorg indexes all for table homer.employee allow write
access cleanup pages
db2 reorg table homer.employee use tempspace1
db2 reorg table employee index empid inplace start
db2 reorg table employee inplace pause
db2 reorg table homer.employee inplace allow read access
notruncate table resume
The command to resume the reorg contains extra keywords to specify read access only and to skip the
truncation step, which share-locks the table. db2 reorg index EMPID on table HOMER.EMPLOYEE
cleanup all on data partition PART1
Usage notes
- 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 in-place reorg 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 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 is 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 is 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 is left invalid. For MDC tables with nonpartitioned block indexes, one or more of the block indexes might be left invalid if an error occurs. For MDC or ITC tables with partitioned indexes, only the index object on the data partition that is reorganized is left invalid. Any indexes marked invalid will be rebuilt on the next access to the table or data partition.
- When a data partitioned table with only partitioned indexes that are 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). 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 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.
- 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.
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
- 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 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.