Command Reference

REORGANIZE TABLE

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:

sysadm
sysctrl
sysmaint
dbadm
CONTROL privilege on the table.

Required Connection

Database

Command Syntax

>>-REORG TABLE--table-name----+--------------------+------------>
                              '-INDEX--index-name--'
 
>-----+-----------------------+--------------------------------><
      '-USE--tablespace-name--'

Command Parameters

TABLE table-name

Specifies the table to reorganize. The table can be in a local or a remote database. The fully qualified name or alias in the form: schema.table-name must be used. The schema is the user name under which the table was created.

Note:

For typed tables, the specified table name must be the name of the hierarchy's root table.

INDEX index-name

Specifies the index to use when reorganizing the table. The fully qualified name in the form: schema.index-name must be used. The schema is the user name under which the index was created. The database manager uses the index to physically reorder the records in the table it is reorganizing. If the name of an index is not provided, the records are reorganized without regard to order.

USE tablespace-name

Specifies the name of a system temporary table space where the database manager can temporarily store the table being reconstructed. If a table space name is not entered, the database manager stores a working copy of the table in the table space(s) in which the table being reorganized resides.

For an 8KB, 16KB, or 32KB table object, the page size of any system 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 system temporary table space TEMPSPACE1 as a work area, enter:

   db2 reorg table homer.employee using tempspace1

Usage Notes

This command 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 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. The reorganize utility will implicitly close all the cursors.

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

REORGANIZE TABLE cannot use an index that is based on an index extension.

See Also

REBIND

REORGCHK

RUNSTATS.

[ Top of Page | Previous Page | Next Page ]