Administration Getting Started

Reorganizing Tables in a Database

A table can become fragmented due to many updates, causing performance to deteriorate. If you collected statistics and did not notice a visible performance improvement, reorganizing table data may help. When you reorganize table data you are rearranging the data of a table into a physical sequence according to a specified index, and removing the free space that is inherent in fragmented data. This can provide faster access to the data and thereby improve performance.

Before you reorganize tables, we recommend you collect the statistics of the data to allow the reorgchk command to see up-to-date statistics. This is described in "Step 4. Collecting Statistics". The results of the reorgchk command will help you determine whether a reorganization of table data should be performed. The reorgchk command has an option to cause statistics to be collected as part of its processing. See the Command Reference for information on this command.

To reorganize table data:

1. Open the Reorganize window:

   1. From the Control Center, expand the object tree until you find the Tables folder.

   2. Click on the Tables folder. Any existing tables are displayed in the contents pane.

   3. Click mouse button 2 on the table you want in the contents pane, and select Reorganize from the pop-up menu. The Reorganize window opens.

2. Optional: In the Using temporary table space field, change the name of the temporary table space where the table being reorganized can be temporarily stored.

   The value is initially <none>, specifying that the temporary copy should be stored in the table space where the table currently resides.

3. Optional: In the Using index field, specify an index to use to reorganize the table rows.

   If you specify <none> (the default), the table rows are reorganized without regard to order.

4. Click on Reorganize Now to reorganize the table immediately. You can also schedule the reorganization to occur at regular intervals by clicking on Schedule. You might find it useful to schedule this activity because reorganization can be time consuming and users will not be able to access the table being reorganized.

5. Collect the statistics again. This is described in "Step 4. Collecting Statistics". This provides the most up-to-date statistics for the data so that you can get the fastest access to the data based on the new organization of the data and indexes.

   You should re-bind application programs that use static SQL after collecting statistics, because the SQL optimizer may choose a different access plan given the new statistics. In particular, you should re-bind those programs that reference tables for which new statistics are available. See the Quick Beginnings for your platform for instructions on binding application programs.

The Administration Guide provides more details about reorganizing table data and collecting statistics.

[ DB2 List of Books | Search the DB2 Books ]