Reorganizing Tables

Purpose

When a table is reorganized, its data is rearranged into a physical sequence corresponding to the table’s primary index. As a result, SQL statements requiring rows in the indexed order can be processed more efficiently. In addition, since unused, empty space is removed, tables are stored more compactly.

Prerequisites

As the reorganization utility uses temporary table copies, the PSAPTEMP tablespace is used during reorganization. You must ensure that PSAPTEMP has at least sufficient free space to hold the table to be reorganized.

Process Flow

When the "Update Statistics" job runs, the DB2 REORGCHK utility is applied to all the tables involved. (For more information, see Update Statistics.) The utility returns information about the physical characteristics of a table and gives information about whether of not it would be beneficial to reorganize it.

The DBA Planning Calendar (transaction DB13) provides a DBA job "Reorganize flagged tables & update statistics". This job can be used to reorganize tables, particularly those tables for which reorganization is recommended. For more information about the DBA Planning Calendar, see Using the DBA Planning Calendar in BC Computing Center Management System. The job displays a maximum of 100 tables recommended for reorganization along with additional data such as delta table size and the duration of the last reorganization. You then choose which of the proposed tables to reorganize.

Result

The selected tables are reorganized and the table statistics are subsequently updated.