How Db2 Query Monitor uses metrics to make a reorganization recommendation

To make a reorganization recommendation, Db2 Query Monitor compares the performance of SQL statements that were run on the object during the baseline performance window against the performance of SQL statements that were run on the object during the comparison performance window.

The default metrics that are compared are elapsed time and Db2 CPU time. Using the baseline metrics, Db2 Query Monitor calculates the elapsed and Db2 CPU time per SQL call that would be expected in the comparison performance window if SQL performance has not degraded. Db2 Query Monitor compares it to actual elapsed and Db2 CPU times per SQL call that were recorded during the comparison performance window. If the difference is significant (greater than 10% for CPU time or greater than 20% for elapsed time), a REORG is recommended.

Since many statements are typically run on an object, for its calculations Db2 Query Monitor uses statements that have been run in both the baseline and comparison performance windows. If there are not enough statements that have been run in both windows to make a reliable recommendation (default value of low threshold % is 60% of the same statements that have been run in both performance windows), Db2 Query Monitor returns a message that not enough information is available.

The metrics that Db2 Query Monitor uses to make a reorganization recommendation can be customized in Db2 Query Monitor. For information, see Customizing metrics and thresholds.