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