Sample priority aging scripts
Use the provided sample scripts to quickly create a tiered service class configuration on your data server. With a tiered configuration, you can address specific performance goals by decreasing the priority of longer-running queries over time, known as priority aging. You can also modify the scripts according to your own business priorities when adapting them to your environment.
The two sample scripts wlmtiersdefault.db2 and wlmtierstimerons.db2 are intended to demonstrate how you might use priority aging on your data server to improve overall throughput. Db2® workload manager provides you with the controls that can help with overall throughput on your data server, but to gain the full benefit of the scripts, and of priority aging in general, you will need to run your data server for an extended period of time and monitor how this work is performed, then adjust the service class and threshold settings accordingly.
The scripts are provided in the samples/admin_scripts directory under your installation directory.
The wlmtiersdefault.db2 and wlmtierstimerons.db2 scripts create three service subclasses under a common superclass with successively lower resource settings from high to low along with CPUTIMEINSC thresholds that move or remap activities in response to the consumption of processor time. The scripts differ in how activities are mapped to a service class when they first enter your data server. A third script, wlmtiersdrop.db2, drops the WLM objects created by the other two sample tiering scripts.
- wlmtiersdefault.db2
- All activities entering the data server are mapped to a high priority service subclass WLM_SHORT without differentiation between types of work. Activities will complete in the high priority service subclass whilst the highest priority is assigned to them, unless they exceed the maximum amount of processor time they are allowed to consume. Activities that consume too much processor time are first remapped by a threshold REMAP ACTIVITY action to a medium priority service subclass WLM_MEDIUM and then, if they still do not complete without exceeding the allotted processor time in that subclass, remapped to a low priority service subclass WLM_LONG, where they continue to be processed until they complete. Activities that cannot be remapped by a CPUTIMEINSC threshold are mapped directly to service subclass WLM_MEDIUM where they will remain.
- wlmtierstimerons.db2
- DML activities entering the data server are evaluated according to their estimated cost and mapped to one of the three service subclass. DML activities estimated to be short are mapped to a high priority service subclass WLM_SHORT, DML activities estimated to be of medium length are mapped to a WLM_MEDIUM service subclass that receives medium priority, and long DML activities are mapped to a WLM_LONG service subclass that receives the lowest priority. Non-DML activities enter the highest priority service subclass. As they are processed, activities that consume more processor time than assigned to a service subclass are successively remapped by a threshold REMAP ACTIVITY action to the next lowest priority service subclass, until they are remapped to the lowest priority service subclass where they continue to be processed until they complete. Activities that cannot be remapped by a CPUTIMEINSC threshold are mapped directly to service subclass WLM_MEDIUM where they will remain.
- wlmtiersdrop.db2
- This script drops all Db2 workload manager service classes, thresholds, workloads, work class sets and work action sets that are created by the scripts wlmtiersdefault.db2 and wlmtierstimerons.db2.
| Service class | CPU shares | Prefetch priority |
|---|---|---|
| WLM_SHORT (high priority) | 6000 | High |
| WLM_MEDIUM (medium priority) | 3000 | Medium |
| WLM_LONG (low priority) | 1000 | Low |
| Default system class | Default | High |
| Default maintenance class | Default | Low |
| Threshold | Maximum amount of processor time that can be used in the service class before remapping |
|---|---|
| WLM_TIERS_REMAP_SHORT_TO_MEDIUM | 30 seconds |
| WLM_TIERS_REMAP_MEDIUM_TO_LONG | 30 seconds |
The wlmtiersdefault.db2 sample script creates the following work action set and work class set, which is used to map activities that cannot be remapped by the CPUTIMEINSC threshold directly to the WLM_MEDIUM service subclass. These activities will remain in the WLM_MEDIUM service subclass for the duration of their execution.
| Work class | Work action |
|---|---|
| WLM_DML_WC | For DML activities, mapped to service class WLM_SHORT initially. These activities can be remapped by a CPUTIMEINSC threshold. |
| WLM_CALL_WC | For CALL activities, mapped to service class WLM_SHORT initially. These activities can be remapped by a CPUTIMEINSC threshold. |
| WLM_OTHER_WC | For activities that cannot be remapped by a CPUTIMEINSC threshold, mapped to service class WLM_MEDIUM. These activities will remain in the WLM_MEDIUM service subclass. |
| Work class | Estimated cost range in timerons and work action |
|---|---|
| WLM_SHORT_DML_WC | For DML activities with an estimated cost of 0 to 999 timerons, mapped to service class WLM_SHORT initially. These activities may get remapped by a CPUTIMEINSC threshold. |
| WLM_MEDIUM_DML_WC | For DML activities with an estimated cost of 1000 to 99 999 timerons, mapped to service class WLM_MEDIUM initially. These activities may get remapped by a CPUTIMEINSC threshold. |
| WLM_LONG_DML_WC | For DML activities with an estimated cost of 100 000 to infinity timerons, mapped to service class WLM_LONG. |
| WLM_CALL_WC | For CALL activities, mapped to service class WLM_SHORT initially. These activities can be remapped by a CPUTIMEINSC threshold. |
| WLM_OTHER_WC | For activities that cannot be remapped, mapped to service class WLM_MEDIUM |
Modifying the scripts for your environment
When you modify the sample scripts to adapt them to your environment, the most important setting to consider is the maximum amount of processor time that can be used in each service class. How much processor time you permit activities to consume in each service subclass depends largely on your particular environment. To find the best values, you need to monitor how activities are being processed on your data server. By default, both the wlmtiersdefault.db2 and wlmtierstimerons.db2 scripts will log event monitor records to the threshold violations event monitor, if one is active, with the option to turn on and enable the activity event monitor and to collect activity data (at the cost of incurring additional overhead). For wlmtiersdefault.db2, if the maximum amount of processor time that can be used in each service class is set too high, most activities will always start and finish in the high priority class regardless of how much actual processor time each requires. If the maximum amount of processor time is set too low, no activity will finish in the high priority service class and every activity will end up being remapped to the medium or low priority service class regardless of business priority. In either case, the script will not benefit overall throughput on your data server and activities are not treated according to their business priority effectively. The same issue is true to a lesser extent for wlmtierstimerons.db2 where activities are differentiated initially by being mapped to service subclasses according to estimated cost. If the maximum amount of processor time that can be used in each service class is set incorrectly, activities will fail to be remapped to a more appropriate service subclass if they consume too much processor time, or are remapped too quickly despite having higher business priority.
For more information about the specific Db2 workload manager objects created by the scripts and about how to run them, refer to the scripts.
Sample scenarios
Two examples have been included in the documentation that show you how you can adapt the sample tiering scripts on your data server to make use of priority aging.