Reorganizing a Tablespace

Purpose

Unlimited fragmentation is allowable for tables within DB2 common server. The only reason for reorganizing tables is to improve I/O performance.

A table can become fragmented as a result of large numbers of updates, deletes or inserts, causing performance to deteriorate. Generally, newly inserted rows cannot be placed in the physical sequence corresponding to the logical sequence defined by an index. This means that the database manager has to perform additional read operations to access the data, because logically sequential data may be on different physical pages that are not sequential.

Therefore, you should use the reorganization utility to rearrange the data in the tables and indexes and to remove the free space that is inherent in fragmented data. The result will be faster accessing of data and, consequently, improvements in performance.

Sometimes, you may wish to reorganize all the tables of a particular tablespace to remove fragmented data, thus increasing the free space. A utility allowing you to do this is provided by the DBA Planning Calendar (transaction DB13). For more information on the DBA Planning Calendar, see Using the DBA Planning Calendar in BC Computing Center Management System.

Prerequisites

The "Reorg Tables in Tablespace(s)" job uses the temporary tablespace PSAPTEMP to store a temporary copy of the table to be reorganized. This temporary table will be at least as large as the original table, so you must ensure that the tablespace PSAPTEMP has at least sufficient free space to hold the original table.

Process Flow

The "Reorg Tables in Tablespace(s)" job rearranges data into a physical sequence that corresponds to the primary index of the each table (if available). If the reorganization job does not complete successfully, do not delete any temporary files, tables or tablespaces. The files and tables are used by the database manager to roll back the changes or to complete the reorganization.