Usage scenarios for comparing workloads

Comparing workloads is useful if you have Db2 Query Monitor data stored for a workload (either in a Db2 offload database or in the interval data sets) that serves as a useful reference, or baseline, against which other workload data can be compared.

Some example scenarios are:

Investigate performance degradation

To identify the causes of performance degradations, you must keep data long enough to provide a baseline for a comparison. For the purposes of this discussion, consider a scenario in which you keep two weeks of data (possibly the older data will have been offloaded to Db2). In this case, you are trying to determine why performance is slower this week than it was last week.

You choose the source, target, and intervals that define a representative sample of last week's performance metrics. For example, if it is 2:00 pm on Wednesday when the slowness is apparent, they might choose the interval from 2:00 pm the preceding Wednesday, when performance was acceptable. You set this as a baseline.

Then you navigate to the source, target, and intervals of the time when performance seemed slow. You can sort by elapsed or average elapsed and then identify the plans, programs, SQL statements that have slowed down the most since last week. You can then investigate the metrics in more detail. It might turn out that an index was dropped since last week, and this can be determined by looking at the objects used by the queries whose performance has degraded the most.

Upgrading Db2

If you plan to upgrade their Db2 and wish to see how the performance characteristics are changed by that upgrade, you will capture data from a "typical workload run" in Db2 Query Monitor (and probably offload it to a Db2 database for long-term storage) as a "pre-upgrade baseline"

After the Db2 upgrade, you would capture data for a "typical run of the same workload" in Query Monitor.

You choose the source, target, and intervals that hold the data from the "pre-upgrade baseline" and set that as a baseline. You then choose the source, target, and intervals that holds the current data for the "post upgrade" run of the workload.

You can then sort by different columns to investigate changes to the performance profile. If the baseline and current data tend to have different numbers of executions, the "Averages columns" will be most useful

Other scenarios

The same kinds of investigation techniques can also be used to demonstrate performance improvements that have been made by tuning queries, adding indexes, or offloading some queries to an accelerator. Only in these cases would you sort by elapsed or average elapsed in ascending order