When to Reorganize
DB2 reorganizations are expensive; they consume processing cycles, disk or tape, operator time, and so on. They also result in an outage (albeit brief due to newer online reorganization capabilities). Most DBAs don’t reorganize until they have to. The decision to reorganize can be driven by two high-level events— statistics exceeding thresholds and application performance degradation.
DB2 keeps two kinds of statistics on its objects:
• Those captured during execution of a RUNSTATS utility or an Independent Software Vendor (ISV) equivalent. RUNSTATs statistics can modify the access path DB2 chooses, so some users are reluctant to update them once access paths are set as preferred. Other users update these statistics often so they can be assured they have the most recent data on their objects.
• Real Time Statistics (RTS). RTS are dynamically captured and don’t modify access paths. Instead, they’re good indicators of change in the object since that last utility event (such as reorganization).
Some of these statistics are good indicators that the object is disorganized. In particular, pay attention to index statistics that indicate leaf page disorganization (LEAFDIST, LEAFFAR, and CLUSTERRATIO). When these values show disorganization, it may be time to reorganize the index. Many users periodically monitor these statistics and trigger a reorganization of an object when a predefined threshold is reached.
DB2 users depend on performance monitoring tools to manage application throughput. You can easily identify an application workload that’s doing excessive I/O processing because of disorganization. Workloads can be tracked and compared over time to identify degradation in performance. Performance degradation is an indicator that reorganization may be required to restore peak performance.
DB2 IFCID 199, which captures metrics with respect to I/O at the object space level, is the performance record of interest for deciding when to reorganize. These metrics can be captured and stored so performance can be measured over time.