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

  1. 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
  1. 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.