Reorganizes a table by reconstructing the rows to eliminate fragmented data, and by compacting information.
Scope
This API affects all nodes in the nodegroup.
Authorization
One of the following:
Required Connection
Database
Version
sqlutil.h
C API Syntax
/* File: sqlutil.h */
/* API: Reorganize Table */
/* ... */
SQL_API_RC SQL_API_FN
sqlureot (
_SQLOLDCHAR * pTableName,
_SQLOLDCHAR * pIndexName,
_SQLOLDCHAR * pTablespace,
struct sqlca * pSqlca);
/* ... */
|
Generic API Syntax
/* File: sqlutil.h */
/* API: Reorganize Table */
/* ... */
SQL_API_RC SQL_API_FN
sqlgreot (
unsigned short TablespaceLen,
unsigned short IndexNameLen,
unsigned short TableNameLen,
struct sqlca * pSqlca,
_SQLOLDCHAR * pTablespace,
_SQLOLDCHAR * pIndexName,
_SQLOLDCHAR * pTableName);
/* ... */
|
API Parameters
REXX API Syntax
REORG TABLE tablename [INDEX iname] [USE tablespace_id] |
REXX API Parameters
Sample Programs
Usage Notes
This API is not supported for declared temporary tables.
Tables that have been modified so many times that data is fragmented and access performance is noticeably slow are candidates for reorganization. Use "REORGCHK" in the Command Reference to determine whether a table needs reorganizing. Be sure to complete all database operations and release all locks before calling REORGANIZE TABLE. This may be done by issuing a COMMIT after closing all cursors opened WITH HOLD, or by issuing a ROLLBACK. After reorganizing a table, use sqlustat - Runstats to update the table statistics, and sqlarbnd - Rebind to rebind the packages that use this table.
If the table is partitioned onto several nodes, and the table reorganization fails on any of the affected nodes, then only the failing nodes will have the table reorganization rolled back.
| Note: | If the reorganization is not successful, temporary files should 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 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.
REORGANIZE TABLE cannot be used on views.
REORGANIZE TABLE cannot be used on a DMS table while an online backup of a table space in which the table resides is being performed.
To complete a table space roll-forward recovery following a table reorganization, both data and LONG table spaces must be roll-forward enabled.
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 can be a result of the order in which the rows were reorganized, and the types of table spaces used (SMS/DMS).
DB2 Version 2 servers do not support down-level client requests to reorganize a table. Since pre-Version 2 servers do not support table spaces, the pTablespace parameter is treated as the Version 1 path parameter, when Version 2 clients are used with a down-level server.
If a Version 2 client requests to reorganize a table on a Version 2 server, and that request includes a path instead of a temporary table space in the pTablespace parameter (for example, an old application, specifying a temporary file path, being executed on Version 2 clients), REORG chooses a system temporary table space in which to place the work files on behalf of the user. A valid system temporary table space name containing a path separator character (/ or \) should not be specified, because it will be interpreted as a temporary path (pre-Version 2 request), and REORG will choose a system temporary table space on behalf of the user.
REORGANIZE TABLE cannot use an index that is based on an index extension.
See Also