Managing Tables and Indexes (DB2 UDB for UNIX and Windows) (SAP Library

Managing Tables and Indexes (DB2 UDB for UNIX and Windows)

Use

To avoid fragmentation, you must manage tables and indexes in your DB2 UDB for UNIX and Windows database. An object (that is, a table or index) is fragmented if it has, for example, overflow rows or a bad cluster ratio. Fragmented objects can reduce database performance and lead to poor application response time.

Prerequisites

Fragmentation has the following effects:

Table fragmentation

Tables are fragmented if the table has overflow rows or the ratio between allocated pages and used pages is bad. The result is bad buffer-pool quality, high IO rates, and less free space for other objects in the same table space.

Index fragmentation

Indexes are fragmented if the cluster ratio is bad. The result is bad buffer pool quality, high IO rates, and less free space for other objects in the same table space.

Procedure

Regularly monitor the objects in your database by using the Computing Center Management System:

Monitor database objects with high growth
Monitor large database objects

Monitor database objects that are flagged as a candidate for reorganization
Monitor the values of overflow rows and cluster ratio

Reorganize database objects by using Computing Center Management System:

Reorganize a complete table space

Reorganize a single table
Reorganize a single table and its indexes

Result

You avoid fragmentation and so minimize loss of performance for your DB2 UDB for UNIX and Windows database and the SAP system.