Reorganizes a table by reconstructing the rows to eliminate fragmented data, and by compacting information.
Scope
This command affects all nodes in the nodegroup.
Authorization
One of the following:
Required Connection
Database
Command Syntax
>>-REORG TABLE--table-name----+--------------------+------------>
'-INDEX--index-name--'
>-----+-----------------------+--------------------------------><
'-USE--tablespace-name--'
Command Parameters
For an 8KB table object, the page size of any temporary table space explicitly specified by the user must match the page size of the table space(s) in which the table data (including any LONG or LOB column data) resides.
Examples
To reorganize the EMPLOYEE table using the temporary table space TEMPSPACE1 as a work area, enter:
db2 reorg table homer.employee using tempspace1
Tables that have been modified so many times that data is fragmented and access performance is noticeably slow are candidates for reorganization. Use REORGCHK to determine whether a table needs reorganizing. Be sure to complete all database operations and release all locks before invoking 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 RUNSTATS to update the table statistics, and 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, 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.
This utility does not support the use of nicknames.
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 tablespace-name 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 tablespace-name parameter (for example, an old application, specifying a temporary file path, being executed on Version 2 clients), REORG chooses a temporary table space in which to place the work files on behalf of the user. A valid 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 temporary table space on behalf of the user.
See Also