Reorganizing table spaces

After you commit a schema change, DB2® puts the affected table space in an advisory REORG-pending (AREO*) state. The table space stays in this state until you reorganize the table space and apply the schema changes.

Procedure

To reorganize the table space and apply the schema changes:

Run the REORG TABLESPACE utility.

DB2 uses table space versions to maximize data availability. Table space versions enable DB2 to keep track of schema changes, and simultaneously, provide users with access to data in altered table spaces. When users retrieve rows from an altered table, the data is displayed in the format that is described by the most recent schema definition, even though the data is not currently stored in this format. The most recent schema definition is associated with the current table space version.

Although data availability is maximized by the use of table space versions, performance might suffer because DB2 does not automatically reformat the data in the table space to conform to the most recent schema definition. DB2 defers any reformatting of existing data until you reorganize the table space with the REORG TABLESPACE utility. The more ALTER statements that you commit between reorganizations, the more table space versions DB2 must track, and the more performance can suffer.

Recommendation: Run the REORG TABLESPACE utility as soon as possible after a schema change to correct any performance degradation that might occur and to keep performance at its highest level.