Default adaptive workload manager environment
By default, incoming queries are classified according to estimated runtime and placed into different subclasses of the SYSDEFAULTUSERCLASS service superclass, where they compete for resources with queries of similar duration. This prevents shorter running queries from being held up by longer running queries.
- Queries for load jobs are routed to SYSLOADSUBCLASS.
- Queries with estimated run times from 0 to 30 seconds are routed to SYSDEFAULTSUBCLASS.
- Queries with estimated run times from 30 seconds to 10 minutes are routed to SYSMEDIUMSUBCLASS.
- Queries with estimated run times greater than 10 minutes are routed to SYSCOMPLEXSUBCLASS.
- If a query's actual runtime exceeds the maximum run time for the group into which it was placed initially, it is remapped to the next group.
Classification of queries is performed by work classes in the SYSDEFAULTUSERWCS work class set.
| Work Class Name | Work Class Attributes |
|---|---|
| SYSSIMPLEDML | DML with estimated runtime 0 to 30 seconds |
| SYSMEDIUMDML | DML with estimated runtime 30 seconds to 10 minutes |
| SYSCOMPLEXDML | DML with estimated runtime greater than 10 minutes |
| SYSLOAD | LOAD utility |
Mapping of queries to service subclasses is performed by work actions on the SYSDEFAULTUSERWAS work action set.
| Work Action Name | Work Class | Work Action |
|---|---|---|
| SYSMAPSIMPLEDML | SYSSIMPLEDML | Map activity to SYSDEFAULTSUBCLASS |
| SYSMAPMEDIUMDML | SYSMEDIUMDML | Map activity to SYSMEDIUMSUBCLASS |
| SYSMAPCOMPLEXDML | SYSCOMPLEXDML | Map activity to SYSCOMPLEXSUBCLASS |
| SYSMAPLOAD | SYSLOAD | Map activity to SYSLOADSUBCLASS |
Remapping of incorrectly placed queries due to bad runtime estimates is handled by thresholds.
| Threshold Name | Service Class | Threshold Predicate | Threshold Action |
|---|---|---|---|
| SYSREMAPTOMEDIUM | SYSDEFAULTSUBCLASS | Runtime > 30 seconds | Remap query to SYSMEDIUMSUBCLASS |
| SYSMAPMEDIUMDML | SYSMEDIUMDML | Runtime > 10 minutes | Remap query to SYSCOMPLEXSUBCLASS |
The following table summarizes the default service class resource entitlements:
| Subclass Name | Resource Entitlement |
|---|---|
| SYSDEFAULTSUBCLASS | 25% |
| SYSMEDIUMSUBCLASS | 25% |
| SYSCOMPLEXSUBCLASS | 35% |
| SYSLOADSUBCLASS | 15% |
Resource entitlements indicate the target resource utilization per service class when there is competition for resources between service classes. The default resource entitlements are soft, meaning that when there are free resources available, a service class can exceed its entitlement. For example, if there is a query running in the SYSCOMPLEXSUBCLASS service class using 10% of the database resources, and no work running in the SYSLOADSUBCLASS or SYSMEDIUMSUBCLASS, the work in the SYSDEFAULTSUBCLASS can exceed its entitlement and use up to 90% of the database resources. For a description of how to adjust service class resource entitlements, see Service class resource assignment.