Show TOC

Procedure documentationManaging Tables and Indexes (DB2 for Linux, UNIX, and Windows) Locate this document in the navigation structure

 

To avoid fragmentation, you must manage tables and indexes in your DB2 for Linux, 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 suboptimal. The result is poor 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 DBA Cockpit:

    • 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

  2. Reorganize database objects by using the DBA Cockpit:

    • 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 for Linux, UNIX, and Windows database and the SAP system.

More Information

Database Administration Using the DBA Cockpit: IBM DB2 for Linux, UNIX, and Windows at:

  https://service.sap.com/instguidesnw   <Your SAP NetWeaver Release>   Operations   Database-Specific Guides