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:
- 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.
- 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) 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.
- DB2REORG_PAUSE_ONLINE
- Pause an existing online reorganization, used when DB2REORG_OBJ_TABLE_INPLACE is
specified as the reorgType.
- DB2REORG_STOP_ONLINE
- Stop an existing online reorganization, used when DB2REORG_OBJ_TABLE_INPLACE is
specified as the reorgType.
- DB2REORG_RESUME_ONLINE
- Resume a paused online reorganization, used when DB2REORG_OBJ_TABLE_INPLACE is
specified as the reorgType.
- DB2REORG_NOTRUNCATE_ONLINE
- Do not perform table truncation, used when DB2REORG_OBJ_TABLE_INPLACE is
specified as the reorgType.
- 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.
- DB2REORG_ALLOW_READ
- Allow only read access to the table.
- DB2REORG_CLEANUP_NONE
- No clean up is required, used when DB2REORG_OBJ_INDEXESALL or DB2REORG_OBJ_INDEX are
specified as the reorgType.
- 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.
- 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.
- DB2REORG_CONVERT_NONE
- No conversion is required, used when DB2REORG_OBJ_INDEXESALL or DB2REORG_OBJ_INDEX are
specified as the reorgType.
- DB2REORG_CONVERT
- Convert to type 2 index, used when DB2REORG_OBJ_INDEXESALL is
specified as the reorgType. Type-1 indexes are
no longer supported and are converted to type-2 indexes when the table
is next accessed. As a result, this value is deprecated and might
be removed in a future release.
- 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.
- 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 (i.e.
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. This
parameter is only supported for the DB2REORG_OBJ_TABLE_OFFLINE reorgType.
- 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. 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 and applicable flags which can affect the default
table access |
Access mode chosen
for each table type |
reorgType |
reorgFlags (if
applicable) |
Non-partitioned table |
Partitioned table |
DB2REORG_OBJ_TABLE_OFFLINE |
|
DB2REORG_ALLOW_READ |
DB2REORG_ALLOW_NONE1 |
DB2REORG_OBJ_TABLE_INPLACE |
|
DB2REORG_ALLOW_WRITE |
N/A |
DB2REORG_OBJ_INDEXESALL |
|
DB2REORG_ALLOW_READ |
DB2REORG_ALLOW_NONE1 |
DB2REORG_OBJ_INDEXESALL |
DB2REORG_CLEANUP_ALL, DB2REORG_CLEANUP_PAGES |
DB2REORG_ALLOW_READ |
DB2REORG_ALLOW_READ |
DB2REORG_OBJ_INDEX |
|
N/A |
DB2REORG_ALLOW_READ |
DB2REORG_OBJ_INDEX |
DB2REORG_CLEANUP_ALL, DB2REORG_CLEANUP_PAGES |
N/A |
DB2REORG_ALLOW_READ |
Note: 1: The pPartitionName does
not specify a data partition name. For information about access modes
when pPartitionName specifies a partition name,
see the REORG INDEXES/TABLE command.
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 least restrictive mode chosen for the default will not go
beyond DB2REORG_ALLOW_READ when the reorgType is
not DB2REORG_OBJ_TABLE_INPLACE. 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 fails because the indexes need to be rebuilt, the
reorganization will switch to allow no access and then continue. A
message will be written to both the administration notification log
and the diagnostics log about the change in the access mode. When DB2REORG_OBJ_INDEX is
specified for a partitioned table, indexes that need to be rebuilt
are rebuilt offline, then the specified index is reorganized (assuming
that it was not rebuilt). This reorganization uses the specified access
mode (that is, the access mode will not change during processing).
A message will be written to the administration notification log and
the diagnostics log about the indexes being rebuilt offline.
- For non-inplace table reorganization, if neither DB2REORG_RESET_DICTIONARY or DB2REORG_KEEP_DICTIONARY is specified, the
default is DB2REORG_KEEP_DICTIONARY.
- If an index reorganization with no access fails, some or all indexes
will not be available and will be 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.