Customizing the adaptive workload manager environment

If the default adaptive workload manager environment does not meet your needs, you can customize it.

For most workloads, the default adaptive workload manager environment ensures database stability while balancing the resources used to process queries of various sizes. However, you might elect to adjust the default environment. For example:

  • If queries are being queued due to insufficient threading resources, and historical monitoring of CPU usage indicates that there is spare CPU available (that is, that the workload is CPU bound rather than memory bound), consider increasing the value of the wlm_agent_load_trgt database configuration parameter to increase the number of threading resources, thereby admitting more work to the database.
  • If queries are being queued due to insufficient sort memory, determine whether any queries are using an excessive amount of sort memory (using more than 50% is usually considered excessive) and, if possible, rewrite those queries to reduce their sort memory requirements. Queries with large sort memory requirements usually have a large impact on admission of other work. If rewriting is not possible, consider other approaches such as reducing the configured sortheap to lower the overall memory usage per query, or using a SORTSHRHEAPUTIL (shared sort memory utilization threshold) to terminate any queries that use an excessive amount of sort memory and cause queuing.

In the default adaptive workload manager environment, the service class SYSDEFAULTUSERCLASS has access to 100% of the configured database resources, and resources are divided among queries using soft entitlements. This ensures that database resources remain fully utilized as long as there is enough work to keep them busy. However, you might prefer to modify service class resource assignments, or to create your own, additional service classes. For example, you might create custom service classes to protect resources for certain applications, or to ensure an even distribution of resources across applications.