DB2 & IMS

Database Reorganization Strategies for DB2 z/OS

5 Pages

• More than designed number of rows have been inserted or deleted.

You can identify performance degradation by these factors:

• Baseline PREFETCH pages are more than two times synchronous I/O.

• AND synchronous I/Os have increased 20 percent since baseline.

• AND GETPAGES per synchronous I/O have fallen 20 percent.

The combination of statistics and workload analysis should yield a set of reorganization candidates—those objects that are both disorganized and experiencing degraded performance over time. Reorganizing these objects will result in more efficient DB2 processing, reducing both I/O and CPU required to derive the result set for scan-oriented queries.

Conclusion

Reorganize only when you need to. Combine the analysis of object statistics with the performance metric comparison for the workload. Schedule reorganization for the object (table space and/or index) when the workload performance degrades and the statistics indicate disorganization. This will ensure the time, resource, and outage needed for reorganization is worth the effort.

To summarize:

• Activate RTS (part of DB2 9 but available for V8 with setup)

• Activate DB2 statistics Class 8 and begin recording IFCID 199 data

• Create a performance table to store data

• Set up a periodic job to summarize 199 data, collect RTS data, and populate your performance table

• Use performance and RTS data to adjust your reorganization strategy.

Running needless reorganization is a waste of time and resources and reduces DB2 application availability. Identifying those objects with workload that would benefit from reorganization can reduce the total cost of ownership for DB2 and yield more efficient application processing.

5 Pages