Workload management

Workload management (WLM) is a powerful feature that enables you to monitor running SQL statements (work) in Db2® Big SQL and to manage the resources that these SQL statements consume. Db2 Big SQL WLM is enabled by default.

A workload comprises all statements that are executed by an application, a specific user, or a specific group. For more information, see CREATE WORKLOAD statement. Each unit of work is mapped to a workload definition before execution starts. The role of the workload is to direct incoming work to a specific service class. The default user workload is SYSDEFAULTUSERWORKLOAD. All incoming work is directed to the SYSDEFAULTUSERCLASS service class.

A service class has a superclass and subclasses. All SQL statements execute in a subclass of a service class. There is often a need to share characteristics or to apply identical rules across common subclasses, which can be done at the superclass level. Resource allocation is done at the service superclass level. By default, all subclasses are treated the same in terms of resource allocation; that is, no subclass has a higher priority than another subclass.

The default user service class is SYSDEFAULTUSERCLASS with SYSDEFAULTMANAGEDSUBCLASS and SYSDEFAULTSUBCLASS subclasses under it. SYSDEFAULTMANAGEDSUBCLASS is the subclass where heavy weight (resource-intensive) queries are executed, and SYSDEFAULTSUBCLASS is the subclass where light weight queries are executed.

All work that is being funneled through a database, workload, or service class is first evaluated by a work action set. The work action set uses definitions in a work class set to find work and to act upon it. The default work action set, SYSDEFAULTUSERWAS, is applied to the SYSDEFAULTUSERCLASS service class. The default work class set is SYSDEFAULTUSERWCS.

Thresholds are used to maintain stability in the system by identifying work that behaves abnormally. If defined thresholds are exceeded, specific actions, such as STOP EXECUTION, can be used to guide the system. The default CONCURRENTDBCOORDACTIVITIES threshold, SYSDEFAULTCONCURRENT, is applied to SYSDEFAULTMANAGEDSUBCLASS.

Important WLM use cases include the following items:

Monitor work executing on the cluster and the resources that it uses
Manage the impact of long running queries that require lots of resources
Prioritize work executing on the cluster
Kill queries that are taking longer than a specified time

Some sample WLM scripts are available in Db2 Big SQL. You can use these scripts as templates for customized concurrency control on a Db2 Big SQL cluster. The query-cost-estimate ranges and concurrency limits that are used in these scripts are examples only, and do not represent recommended values. You can find these sample scripts in the $BIGSQL_HOME/samples/wlm_concurrency_template directory. For more information about these scripts, see the README file in that directory.

alter_wlm_objects.sql: Use this script to alter query-cost-estimate ranges and concurrency limits.
create_wlm_objects.sql: Use this script to create database objects that define WLM rules.
drop_wlm_objects.sql: Use this script to drop database objects that define WLM rules.
queries.sql: Use this script to monitor the impact of the defined WLM concurrency controls. The script uses views that are created by the views.sql script.
views.sql: Use this script to create a set of views that can help you to monitor the impact of defined WLM concurrency controls.

For more information, see Big SQL Workload Management for Improved System Stability and Performance or Introduction to Db2 workload management concepts.