Setting up workload management
Create workload management configurations by creating queues for them.
• The following statements create a 'day' configuration:
alter wlm queue openroad add with config=day, priority=high, ratio=100, active_limit=100, maxwait=60
alter wlm queue reports add with config=day, priority=med, ratio=50, active_limit=3, readwrite=R
alter wlm queue batch add with config=day, priority=low, ratio=10, active_limit=1
alter wlm queue daydef add with config=day, priority=low, active_limit=10, default
• The following statements create a 'night' configuration:
alter wlm queue openroad add with config=night, priority=high, ratio=100, active_limit=10
alter wlm queue reports add with config=night, priority=med, ratio=50, maxwait=300, active_limit=20, readwrite=R
alter wlm queue batch add with config=night, priority=low, ratio=10, active_limit=20
Note: The same queue can exist in different configurations with different settings.
• Allow users or groups or roles to use each queue by adding them to each queue.
alter wlm queue openroad add group dev1 with priority=high
alter wlm queue reports add group dev1 with priority=high
alter wlm queue reports add group qa with priority=med
alter wlm queue batch add group qa with priority=high
alter wlm queue batch add user supervisor1 with priority=critical
• Add superusers to the $superuser queue for superuser privileges.
alter wlm queue '$superuser' add user boss1 with priority=high;
• Set an active configuration.
alter wlm config set config=day
• Enable workload management if not already enabled.
alter wlm enable
It is possible to create or modify or delete the workload management setup by directly modifying the workload management system catalogs. This allows you to easily backup or restore the current setup. Using the ALTER WLM statement guarantees correct values for each column in the tables and allows more consistency checks to be run.
The workload management feature includes:
• Workload queues
• Configurations
• Session level workload management
• Real time monitoring of workloads
• Auditing of workloads
Use cases include:
• Preventing machine overload by limiting the number of queries users can run concurrently.
• Allowing system administrators to run high priority queries quickly even on a fully loaded system.
• Sharing resources appropriately across users, groups, and applications, which can prevent a few resource-hungry sessions from monopolizing machine resources.
Last modified date: 08/29/2024