db2Reorg API - Reorganize an index or a table

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

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.

DB2REORG_OBJ_TABLE_INPLACE

Reorganize the table inplace.

INPLACE (online) reorganization is not supported in DB2® pureScale® environments. Any attempts to perform an inplace reorganization in DB2 pureScale environments will fail with SQL1419N.

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

db2ReorgTable data structure parameters

db2ReorgIndexesAll data structure parameters

db2gReorgTable data structure specific parameters

db2gReorgIndexesAll data structure specific parameters

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. 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_NONE1
  DB2REORG_OBJ_TABLE_INPLACE		DB2REORG_ALLOW_WRITE	N/A
  DB2REORG_RECLAIM_EXTENTS		DB2REORG_ALLOW_WRITE	DB2REORG_ALLOW_WRITE
  DB2REORG_OBJ_INDEXESALL2		DB2REORG_ALLOW_READ	DB2REORG_ALLOW_NONE1
  DB2REORG_OBJ_INDEXESALL	DB2REORG_CLEANUP_ALL DB2REORG_CLEANUP_PAGES DB2REORG_INDEX_RECLAIM_EXTENTS	DB2REORG_ALLOW_READ	DB2REORG_ALLOW_READ
  DB2REORG_OBJ_INDEX2	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.