Effective Reorganization Strategy
The most effective reorganization strategy combines analysis of statistics with analysis of workload performance over time. Look at RTS and IFCID 199 information together to make informed decisions regarding reorganization scheduling. Reorganize only those objects that would benefit from the process.
If statistics indicate that space soon may be exhausted, either enlarge the object or reorganize it to reclaim any free space. DB2 9 makes it almost impossible to run out of space—a universal table space can be up to 128TB! Universal table spaces provide for up to 4,096 partitions and they no longer require a partitioning range key. Partitions can be allocated as a specified size; when a partition gets full, another partition is automatically created and data begins to be inserted into it. Performance needs will drive most future reorganization decisions.
Workload analysis should focus on identifying the type of workload causing I/O. If the workload is largely a query scan-related workload (asynchronous I/O), then the object will be sensitive to cluster sequence and should be reorganized when performance degrades. If the workload is largely random (synchronous I/O), then the object will be somewhat impervious to the benefits of reorganization.
The aforementioned analysis assumes data is available to analyze. Periodically capturing IFCID 199 and RTS information and storing it for future analysis will take some setup and processing. To create a performance database to be used for workload comparison, you could use the table definition in Figure 1. This data can be periodically captured (as often as daily) and stored in the table. The data in the performance table along with data in the DB2 catalog can then be analyzed to spot degradation trends and disorganization.
The following indicators could be triggers for reorganization:
• Table space has more than 10 percent rows relocated FAR from original (as indicated in the TABLE_FARINDREF column in the performance table).
• CLUSTERRATIO less than 90 percent
• Index with more than 10 percent LEAF pages FAR from original (as indicated in the INDEX_ REORGLEAFFAR column in the performance table)