db2Reorg API - Reorganize an index or a table

Reorganizes a table or all indexes defined on a table by compacting the information and reconstructing the rows or index data to eliminate fragmented data.

Authorization

One of the following authorities:

SYSADM
SYSCTRL
SYSMAINT
DBADM
SQLADM
CONTROL privilege on the table

Required connection

Database

API include file

db2ApiDf.h

API and data structure syntax

SQL_API_RC SQL_API_FN
  db2Reorg (
             db2Uint32 versionNumber,
             void * pReorgStruct,
             struct sqlca * pSqlca);

typedef SQL_STRUCTURE db2ReorgStruct
{
  db2Uint32 reorgType;
  db2Uint32 reorgFlags;
  db2int32 nodeListFlag;
  db2Uint32 numNodes;
  SQL_PDB_NODE_TYPE *pNodeList;
  union db2ReorgObject        reorgObject;
} db2ReorgStruct;

union db2ReorgObject
{
  struct db2ReorgTable             tableStruct;
  struct db2ReorgIndexesAll        indexesAllStruct;
};

typedef SQL_STRUCTURE db2ReorgTable
{
  char *pTableName;
  char *pOrderByIndex;
  char *pSysTempSpace;
  char *pLongTempSpace;
  char *pPartitionName;
} db2ReorgTable;

typedef SQL_STRUCTURE db2ReorgIndexesAll
{
  char *pTableName;
  char *pIndexName;
  char *pPartitionName;
} db2ReorgIndexesAll;

SQL_API_RC SQL_API_FN
  db2gReorg (
              db2Uint32 versionNumber,
              void * pReorgStruct,
              struct sqlca * pSqlca);

typedef SQL_STRUCTURE db2gReorgStruct
{
  db2Uint32 reorgType;
  db2Uint32 reorgFlags;
  db2int32 nodeListFlag;
  db2Uint32 numNodes;
  SQL_PDB_NODE_TYPE *pNodeList;
  union db2gReorgObject       reorgObject;
} db2gReorgStruct;

typedef SQL_STRUCTURE db2gReorgNodes
{
  SQL_PDB_NODE_TYPE nodeNum[SQL_PDB_MAX_NUM_NODE];
} db2gReorgNodes;

union db2gReorgObject
{
  struct db2gReorgTable            tableStruct;
  struct db2gReorgIndexesAll       indexesAllStruct;
};

typedef SQL_STRUCTURE db2gReorgTable
{
  db2Uint32 tableNameLen;
  char *pTableName;
  db2Uint32 orderByIndexLen;
  char *pOrderByIndex;
  db2Uint32 sysTempSpaceLen;
  char *pSysTempSpace;
  db2Uint32 longTempSpaceLen;
  char *pLongTempSpace;
  db2Uint32 partitionNameLen;
  char *pPartitionName;
} db2gReorgTable;

typedef SQL_STRUCTURE db2gReorgIndexesAll
{
  db2Uint32 tableNameLen;
  char *pTableName;
  db2Uint32 indexNameLen;
  char *pIndexName;
  db2Uint32  partitionNameLen;
  char *pPartitionName;
} db2gReorgIndexesAll;

db2Reorg API parameters

versionNumber: Input. Specifies the version and release level of the structure passed as the second parameter, pReorgStruct.
pReorgStruct: Input. A pointer to the db2ReorgStruct structure.
pSqlca: Output. A pointer to the sqlca structure.

db2ReorgStruct data structure parameters

reorgType

Input. Specifies the type of reorganization. Valid values (defined in db2ApiDf header file, located in the include directory) are:

DB2REORG_OBJ_TABLE_OFFLINE: Reorganize the table offline. This value is equivalent to the CLASSIC table clause of the REORG command.
DB2REORG_OBJ_TABLE_INPLACE: Reorganize the table inplace.
DB2REORG_OBJ_INDEXESALL: Reorganize all indexes.
DB2REORG_OBJ_INDEX: Reorganize one index.
DB2REORG_RECLAIM_EXTENTS: Reorganize a multidimensional clustering (MDC) or insert time clustering (ITC) table to reclaim empty extents for the table space.

reorgFlags

Input. Reorganization options. Valid values (defined in db2ApiDf header file, located in the include directory) are:

DB2REORG_OPTION_NONE: Default action.
DB2REORG_LONGLOB: Reorganize long fields and lobs, used when DB2REORG_OBJ_TABLE_OFFLINE is specified as the reorgType. If DB2REORG_RESETDICTIONARY or DB2REORG_KEEPDICTIONARY option is also specified, the options apply to the XML storage object of the table in addition to the table object.
DB2REORG_INDEXSCAN: Recluster utilizing index scan, used when DB2REORG_OBJ_TABLE_OFFLINE is specified as the reorgType.
DB2REORG_START_ONLINE: Start online reorganization, used when DB2REORG_OBJ_TABLE_INPLACE is specified as the reorgType. This parameter is not supported in Db2® pureScale® environments.
DB2REORG_PAUSE_ONLINE: Pause an existing online reorganization, used when DB2REORG_OBJ_TABLE_INPLACE is specified as the reorgType. This parameter is not supported in Db2 pureScale environments.
DB2REORG_STOP_ONLINE: Stop an existing online reorganization, used when DB2REORG_OBJ_TABLE_INPLACE is specified as the reorgType. This parameter is not supported in Db2 pureScale environments.
DB2REORG_RESUME_ONLINE: Resume a paused online reorganization, used when DB2REORG_OBJ_TABLE_INPLACE is specified as the reorgType. This parameter is not supported in Db2 pureScale environments.
DB2REORG_NOTRUNCATE_ONLINE: Do not perform table truncation, used when DB2REORG_OBJ_TABLE_INPLACE is specified as the reorgType. This parameter is not supported in Db2 pureScale environments.
DB2REORG_ALLOW_NONE: No read or write access to the table. This parameter is not supported when DB2REORG_OBJ_TABLE_INPLACE is specified as the reorgType.
DB2REORG_ALLOW_WRITE: Allow read and write access to the table. This parameter is not supported when DB2REORG_OBJ_TABLE_OFFLINE is specified as the reorgType. This parameter is supported in Db2 pureScale environments only when the DB2REORG_INDEX_RECLAIM_EXTENTS, DB2REORG_CLEANUP_ALL or the DB2REORG_CLEANUP_PAGES option is also specified.
DB2REORG_ALLOW_READ: Allow only read access to the table. This parameter is supported in Db2 pureScale environments only when the DB2REORG_INDEX_RECLAIM_EXTENTS, DB2REORG_CLEANUP_ALL or the DB2REORG_CLEANUP_PAGES option is also specified.
DB2REORG_CLEANUP_NONE: This value has been deprecated in version 10.1. It indicates that no clean up is required when the reorgType is set to DB2REORG_OBJ_INDEXESALL or DB2REORG_OBJ_INDEX. Not specifying this value has the same effect, therefore, specifying the value is redundant.
DB2REORG_CLEANUP_ALL: Clean up the committed pseudo deleted keys and committed pseudo empty pages, used when DB2REORG_OBJ_INDEXESALL or DB2REORG_OBJ_INDEX are specified as the reorgType.; If DB2REORG_CLEANUP_ALL is specified with DB2REORG_INDEX_REBUILD an error, SQL2218N, is returned.
DB2REORG_CLEANUP_PAGES: Clean up committed pseudo empty pages only, but do not clean up pseudo deleted keys on pages that are not pseudo empty, used when DB2REORG_OBJ_INDEXESALL or DB2REORG_OBJ_INDEX are specified as the reorgType.; If DB2REORG_CLEANUP_PAGES is specified with DB2REORG_INDEX_REBUILD an error, SQL2218N, is returned.
DB2REORG_CLN_OVFL_ONLINE: Perform a reorganization that converts overflow records into normal records. This conversion is done when DB2_REORG_OBJ_INPLACE is specified as the reorganization type.
DB2REORG_CONVERT_NONE: This value has been deprecated in version 10.1. In earlier releases, this value indicated that no index conversion was required when the reorgType is set to DB2REORG_OBJ_INDEXESALL or DB2REORG_OBJ_INDEX. This value has become obsolete because type-1 indexes were discontinued since Version 9.7.
DB2REORG_RESET_DICTIONARY: If the DB2REORG_LONGLOB option is also specified, DB2REORG_RESETDICTIONARY applies to the XML storage object of the table also. If the COMPRESS attribute for the table is YES then a new compression dictionary is built. All the rows processed during reorganization are subject to compression using this new dictionary. This dictionary replaces any previous dictionary in the object. If the COMPRESS attribute for the table is NO and the table object or the XML storage object does have an existing compression dictionary then reorg processing will remove the dictionary and all rows in the newly reorganized table will be in non-compressed format. This parameter is only supported for the DB2REORG_OBJ_TABLE_OFFLINE reorgType.
DB2REORG_KEEP_DICTIONARY: If DB2REORG_LONGLOB keyword is also specified, DB2REORG_KEEPDICTIONARY applies to the table object and the XML storage object of the table. If DB2REORG_LONGLOB is not specified, the following applies only to the table object.; If the COMPRESS attribute for the table is YES and a dictionary exists, it is kept. If the COMPRESS attribute for the table is YES and a dictionary does not exist, one is built, as the option defaults to DB2REORG_RESET_DICTIONARY in that case. All rows processed by reorganization are subject to compression. If the COMPRESS attribute for the table is NO, the dictionary will be retained (if one existed), and all rows in the newly reorganized table will be in non-compressed format. This parameter is only supported for the DB2REORG_OBJ_TABLE_OFFLINE reorgType.
DB2REORG_INDEX_RECLAIM_EXTENTS: Reclaim extents from the index object back to the table space, used when DB2REORG_OBJ_INDEXESALL or DB2REORG_OBJ_INDEX is specified as the reorgType.; If DB2REORG_INDEX_RECLAIM_EXTENTS is specified with DB2REORG_INDEX_REBUILD an error, SQL2218N, is returned.
DB2REORG_INDEX_REBUILD: Rebuild the index data, used when DB2REORG_OBJ_INDEXESALL or DB2REORG_OBJ_INDEX is specified as the reorgType.; If DB2REORG_INDEX_REBUILD is specified with DB2REORG_INDEX_RECLAIM_EXTENTS, DB2REORG_CLEANUP_ALL, or DB2REORG_CLEANUP_PAGES an error, SQL2218N, is returned.

nodeListFlag

Input. Specifies which nodes to reorganize. Valid values (defined in db2ApiDf header file, located in the include directory) are:

DB2REORG_NODE_LIST: Submit to all nodes in the nodelist array.
DB2REORG_ALL_NODES: Submit to all nodes in the database partition group.
DB2REORG_ALL_EXCEPT: Submit to all nodes except the ones specified by the nodelist parameter.

numNodes

Input. Number of nodes in the nodelist array.

pNodeList

A pointer to the array of node numbers.

reorgObject

Input. Specifies the type of object to be reorganized.

db2ReorgObject union parameters

tableStruct: Specifies the options for a table reorganization.
indexesAllStruct: Specifies the options for an index reorganization.

db2ReorgTable data structure parameters

pTableName: Input. Specifies the name of the table to reorganize.
pOrderByIndex: Input. Specifies the index to order the table by.
pSysTempSpace: Input. Specifies the system temporary table space where temporary objects are created. The REORG command may expand rows in cases where a column is added to a table (such as from ALTER TABLE ADD COLUMN) and the rows were inserted before the column was added. For a nonpartitioned table, this parameter must specify a table space with enough room to create the new table object. A partitioned table is reorganized a single data partition at a time. In this case, there must be enough free space in the table space to hold the largest data partition of the table. When the pPartitionName parameter is specified, the temporary table space must be able to hold the specified partition.
If this parameter is not specified for a nonpartitioned table the table space the table resides in is used. If this parameter is not specified 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.
pLongTempSpace: Input. Specifies the temporary table space to create long objects (LONG VARCHAR and LOB columns) in during table reorganization. If the pSysTempSpace parameter is not specified, this parameter is ignored. If this parameter is not specified, but the pSysTempSpace parameter is specified, then Db2 will create the long data objects in the table space specified by the pSysTempSpace parameter, unless the page sizes differ.
When page sizes differ, if pSysTempSpace is specified, but this parameter is not, Db2 will attempt to find an existing table space with a matching page size to create the long objects in.
pPartitionName: Input. Specifies the name of the data partition to reorganize.

db2ReorgIndexesAll data structure parameters

pTableName: Input. Specifies the name of the table for index reorganization. If DB2REORG_OBJ_INDEX is specified as the reorgType, the pTableName parameter is not required and can be NULL. However, if the pTableName parameter is specified, it must be the table on which the index is defined.
pIndexName: Input. Specifies the name of the index to reorganize. This parameter is used only when the reorgType parameter is set to a value of DB2REORG_OBJ_INDEX otherwise set pIndexName parameter to NULL.
pPartitionName: Input. Specifies the name of the data partition whose indexes are to be reorganized.

db2gReorgTable data structure specific parameters

tableNameLen: Input. Specifies the length in bytes of pTableName.
orderByIndexLen: Input. Specifies the length in byte of pOrderByIndex.
sysTempSpaceLen: Input. Specifies the length in bytes of pSysTempSpace.
longTempSpaceLen: Input. Specifies the length of the name stored in the pLongTempSpace
partitionNameLen: Input. Specifies the length, in bytes, of pPartitionName.
pPartitionName: Input. Specifies the name of the data partition to reorganize.

db2gReorgIndexesAll data structure specific parameters

tableNameLen: Input. Specifies the length in bytes of pTableName.
indexNameLen: Input. Specifies the length in bytes of the pIndexName parameter.
partitionNameLen: Input. Specifies the length, in bytes, of pPartitionName.
pPartitionName: Input. Specifies the name of the data partition for the index.

Usage notes

Performance of table access, index scans, and the effectiveness of index page prefetching can be adversely affected when the table data has been modified many times, becoming fragmented and unclustered. Use REORGCHK to determine whether a table or its indexes are candidates for reorganizing. If the objective is to reclaim space, the RECLAIMABLE_SPACE output of the ADMIN_GET_INDEX_INFO and ADMIN_GET_TAB_INFO functions show how much space is reclaimable, in kilobytes. You can then use the RECLAIM_EXTENTS option of reorgType or reorgFlags to reclaim space in your tables and indexes. The RECLAIM_EXTENTS option consolidates sparse extents implicitly. This consolidation leads to more space reclamation, but a longer duration for utility execution when compared to Db2 version 10.1. All work will be committed and all open cursors will be closed during reorg processing. After reorganizing a table or its indexes, use db2Runstats to update the statistics and sqlarbnd to rebind the packages that use this table.
If the table data is distributed onto several nodes and the reorganization fails on any of the affected nodes, then only the failing nodes will have the reorganization rolled back. If table reorganization is not successful, temporary files should not be deleted. The database manager uses these files to recover the database.
For table reorganization, 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 data and LONG table spaces must be rollforward enabled.
If the table contains LOB columns not defined with 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/DMS).

The following table illustrates the default table access chosen based on the type of reorg and table:

Table 1. Default table access chosen based on the type of reorg and table
Type of reorg that can affect the default table access: reorgType	Type of applicable flags that can affect the default table access: reorgFlags	Access mode chosen for Non-partitioned table	Access mode chosen for Partitioned table
DB2REORG_OBJ_TABLE_OFFLINE		DB2REORG_ALLOW_READ	DB2REORG_ALLOW_NONE¹
DB2REORG_OBJ_TABLE_INPLACE		DB2REORG_ALLOW_WRITE	N/A
DB2REORG_RECLAIM_EXTENTS		DB2REORG_ALLOW_WRITE	DB2REORG_ALLOW_WRITE
DB2REORG_OBJ_INDEXESALL²		DB2REORG_ALLOW_READ	DB2REORG_ALLOW_NONE¹
DB2REORG_OBJ_INDEXESALL	DB2REORG_CLEANUP_ALL DB2REORG_CLEANUP_PAGES DB2REORG_INDEX_RECLAIM_EXTENTS	DB2REORG_ALLOW_READ	DB2REORG_ALLOW_READ
DB2REORG_OBJ_INDEX²	DB2REORG_CLEANUP_ALL DB2REORG_CLEANUP_PAGES DB2REORG_INDEX_REBUILD DB2REORG_INDEX_RECLAIM_EXTENTS	N/A	DB2REORG_ALLOW_READ

Note:

1: If pPartitionName is not specified, DB2REORG_ALLOW_NONE is the default. For information about access modes when pPartitionName specifies a partition name, see the REORG INDEXES/TABLE command.

2: Unless the cleanup or reclaim reorgFlags are specified, the default is DB2REORG_INDEX_REBUILD.

N/A: Not applicable at this time since it is not supported.

Some access modes may not be supported on certain types of tables or indexes. In these cases and where possible, the least restrictive access mode is used. (The most restrictive access mode being DB2REORG_ALLOW_NONE, followed by DB2REORG_ALLOW_READ, and then DB2REORG_ALLOW_WRITE, which is the least restrictive). As support for existing table or index types change, or new table or index types are provided, the default can change from a more restrictive access mode to a less restrictive mode. The default access mode is chosen when none of the DB2REORG_ALLOW_NONE, DB2REORG_ALLOW_READ, or DB2REORG_ALLOW_WRITE flags are specified.

When reorganizing indexes, use the access option to allow other transactions either read-only or read-write access to the table.
If an index reorganization with allow read or allow write access is attempted on a nonpartitioned table when the indexes require rebuilding, the table is taken offline while the indexes are rebuilt. A message is written to both the administration notification log and the diagnostics log about the change. For a nonpartitioned table there is nothing to reorganize after the indexes are rebuilt. When DB2REORG_OBJ_INDEX is specified for a partitioned table, indexes on the table that require a rebuild are rebuilt offline. Assuming that it was not already rebuilt, the specified index is reorganized. This reorganization uses the specified access mode, and the access mode does not change during processing. A message is written to the administration notification log and the diagnostics log about the indexes being rebuilt offline.
For classic table reorganization, if neither DB2REORG_RESET_DICTIONARY or DB2REORG_KEEP_DICTIONARY is specified, the default is DB2REORG_KEEP_DICTIONARY.
If an index reorganization rebuild with no access fails, some or all indexes are not available and are rebuilt on the next table access.
This API cannot be used with:
- Views or an index that is based on an index extension.
- Declared temporary tables.
- Created temporary tables.
With Db2 Version 9.7 Fix Pack 1 and later releases, pPartitionName can specify a data partition name to reorganize a specific data partition of a data partitioned table or the partitioned indexes on a specific data partition of a partitioned table.
The following items apply for a data partitioned table when using pPartitionName to reorganize the partitioned indexes on a specific data partition of a partitioned table:
- 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.
- 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", all indexes marked "invalid" or "for rebuild" are rebuilt before reorganization. Otherwise, only 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 cleaning up indexes.
When using pPartitionName to perform a table reorganization on a data partition of a data partitioned table, nonpartitioned indexes affect access to the table:
- If there are no nonpartitioned indexes (except system-generated XML path indexes) defined on the table, only the specified partition is reorganized. 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 NONE mode is the default and only supported access mode. In this case, the table is placed in ALLOW NONE mode. If ALLOW READ ACCESS is specified, SQL1548N is returned (SQLSTATE 5U047).
- For a data partitioned table, a table reorganization rebuilds the nonpartitioned indexes and partitioned indexes on the table after reorganizing the table. If pPartitionName 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.
When using pPartitionName to perform a table or index reorganization, the following conditions return an error:
- If the data partition name does not exist on the given table when performing a table reorganization of a specific data partition, the reorganization fails and returns SQL2222N with reason code 1.
- If a data partition name is specified when performing an index reorganization a specific nonpartitioned index defined on a partitioned table, the reorganization fails and returns SQL2222N with reason code 2”
- If the data partition name specified is still in attached or detached state when performing a table reorganization of a data partition, the reorganization fails and returns SQL2222N with error code 3.
- If the data partition name specified is still in attached or detached state when performing an index reorganization on the partitioned indexes of a data partition, the reorganization fails and returns SQL2222N with error code 3.
If DB2REORG_OBJ_INDEXESALL or DB2REORG_OBJ_INDEX is specified as the reorgType and none of the following reorgFlags are specified:
- DB2REORG_CLEANUP_ALL
- DB2REORG_CLEANUP_PAGES
- DB2REORG_INDEX_RECLAIM_EXTENTS
- DB2REORG_INDEX_REBUILD
DB2REORG_INDEX_REBUILD is taken as the default reorgFlag and indexes are rebuilt.