DB2 Workload Manager (WLM) as a Monitoring Solution – Understanding WLM (Part1 of 3)

This is part one of a three part series. The full series includes:
DB2 Workload Manager (WLM) as a Monitoring Solution – Understanding WLM (Part1 of 3)
DB2 Workload Manager (WLM) as a Monitoring Solution– How to Set up WLM (Part 2 of 3)
DB2 Workload Manager (WLM) as a Monitoring Solution– Analyzing WLM Information (Part 3 of 3)

What is DB2 Workload Manager (WLM)?

Workload Manager is a feature that comes with DB2 Advanced Enterprise Server and Advanced Workgroup Server editions that can classify database work into different buckets. One work bucket can get priority of execution and priority on CPU and memory resources, while another bucket of work gets a lower share of system resources. Critical business processes may need guaranteed access to maximum system resources, while a curious TOAD user needs to be restricted from getting a monopoly on the database system resources.
DB2’s Workload Manager can not only be used for controlling resource consumption amongst different applications but it can also be used to build an application centric performance monitoring strategy. Application centric performance monitoring can produce some stunning performance reports which will be more easily understood by clients than a bunch of reports on I/O, memory, bufferpools, sortheap, locklist , etc. Although traditional technical reports can be a very interesting read to tech savvy DBAs, they may not make much sense to the application development community and clients.
Speaking the same language as other teams can help them understand database performance. That is where DB2 Workload Management can help.
Note: With DB2 Enterprise Server edition, you cannot create new workloads and service classes but you can still modify the default WLM objects for monitoring and workload management purposes.

Understanding the DB2 Workload Manager

The WLM ecosystem is composed of many parts. Before implementing WLM, it is important to understand the parts of the ecosystem

Service class

A service class defines the execution environments in which work can run. This execution environment allocates available resources (CPU, Memory, etc) to a specific threshold which determines how work is permitted to run.
Think of it this way – A service class is the environment or bucket where the assigned work runs. Different buckets or service classes can have different resource controls associated with them like prefetch priority, bufferpool priority, etc and can have thresholds associated with each.

DB2 has 3 default service superclasses:

1. SYSDEFAULTSYSTEMCLASS
2. SYSDEFAULTMAINTENANCECLASS
3. SYSDEFAULTUSERCLASS

Service Subclass

A subclass is a smaller bucket inside the service superclass buckets. Although service superclass is the highest tier of work, activities run in service subclasses. Each service superclass has a default service subclass to run activities that you do not assign to an explicitly defined subclass.
DB2 has 2 default subclasses:

1. SYSDEFAULTSUBCLASS
2. SYSDEFAULTMANAGEDSUBCLASS

You can use the SYSCAT.SERVICECLASSES catalog view to look at the default service super classes and subclasses in the system.

db2 "select serviceclassname, parentserviceclassname from syscat.serviceclasses with UR"

SERVICECLASSNAME                                              PARENTSERVICECLASSNAME

-------------------------------------------------------------------------------------------

SYSDEFAULTSUBCLASS                                            SYSDEFAULTSYSTEMCLASS

SYSDEFAULTSUBCLASS                                            SYSDEFAULTMAINTENANCECLASS

SYSDEFAULTMANAGEDSUBCLASS                                     SYSDEFAULTUSERCLASS

SYSDEFAULTSUBCLASS                                            SYSDEFAULTUSERCLASS

SYSDEFAULTMAINTENANCECLASS                                    -

SYSDEFAULTSYSTEMCLASS                                         -

SYSDEFAULTUSERCLASS                                           -

Notice that SERVICECLASSNAMES SYSDEFAULTMAINTENANCECLASS, SYSDEFAULTSYSTEMCLASS, and SYSDEFAULTUSERCLASS do not have any PARENTSERVICECLASSNAME mentioned as they are the default superclasses.

Workloads

Think about a workload as a shovel that identifies the work, picks it up, and dumps it into the service classes, or buckets, for execution. DB2 WLM allows you to identify work based on various attributes like application name, session user, client workstation name, etc.
Just like default service classes, DB2 has default workloads.

db2 "select workloadname, serviceclassname, parentserviceclassname from syscat.workloads with UR"


WORKLOADNAME                                SERVICECLASSNAME             PARENTSERVICECLASSNAME

------------------------------------------------------------------------------------------------

SYSDEFAULTUSERWORKLOAD                     SYSDEFAULTSUBCLASS           SYSDEFAULTUSERCLASS

SYSDEFAULTADMWORKLOAD                      SYSDEFAULTSUBCLASS           SYSDEFAULTUSERCLASS

As you can see from the SYSCAT.WORKLOADS catalog view, DB2 comes with 2 default workloads- SYSDEFAULTUSERWORKLOAD and SYSDEFAULTADMWORKLOAD.
By our shovel and bucket model we can conclude that DB2 dumps all work coming from SYSTEMDEFAULTUSERWORKLOAD and SYSTEMDEFAULTADMINWORKLOAD into the SYSTEMDEFAULTUSERCLASS superclass and SYSTEMDEFAULTSUBCLASS sub service classes.

Workclass Sets (and example)

Workclass sets are collections of different workloads grouped together. Once a workclass set has been created, we can create work action sets which define how a specific workclass set can be controlled. Work classes are always created as part of workclass sets and not as separate objects.
DB2 has the below default workclass and workclassset.

db2 "select workclassname, workclasssetname  from syscat.workclasses with UR"

WORKCLASSNAME                          WORKCLASSSETNAME

SYSMANAGEDQUERIES                      SYSDEFAULTUSERWCS

Below is an example taken from IBM Knowledge Center.

CREATE WORK CLASS SET LARGE_QUERIES
(WORK CLASS LARGE_ESTIMATED_COST WORK TYPE DML
FOR TIMERONCOST FROM 9999 TO UNBOUNDED,
WORK CLASS LARGE_CARDINALITY WORK TYPE DML
FOR CARDINALITY FROM 1000 TO UNBOUNDED)

This example creates a workclass set named LARGE_QUERIES representing all DML activities with an estimated cost greater than 9999 and estimated cardinality greater than 1000.
Notice the workclass set LARGE_QUERIES contains two work classes: LARGE_ESTIMATED and LARGE_CARDINALITY.

Workaction sets (and example)

Workaction sets define how to service workloads that fall under defined workclass sets.
Going back to the workclass set example, the work action set named DATABASE_ACTIONS has two work actions defined. Work action ONE_CONCURRENT_QUERY will take all work that falls under workclass set LARGE_QUERIES and if concurrentdbcoordactivites >1 (there are more than one concurrent activities) and queuedactivities >1 (there are more than one queued activities) and the work belongs to workclass LARGE_ESTIMATED_COST, it will stop the execution of the query or SQL statement!!
Work action TWO_CONCURRENT_QUERIES will take the work that falls under workclass set LARGE_QUERIES and if concurrentdbcoordactivites >2 (there are more than two concurrent activities) and queuedactivities >2 (there are more than two queued activities) and the work falls under LARGE_CARDINALITY workclass, it will collect activity data and let the DML continue to run.

CREATE WORK ACTION SET DATABASE_ACTIONS
FOR DATABASE USING WORK CLASS SET LARGE_QUERIES
(WORK ACTION ONE_CONCURRENT_QUERY ON WORK CLASS LARGE_ESTIMATED_COST
WHEN CONCURRENTDBCOORDACTIVITIES > 1 AND QUEUEDACTIVITIES > 1
STOP EXECUTION,
WORK ACTION TWO_CONCURRENT_QUERIES ON WORK CLASS LARGE_CARDINALITY
WHEN CONCURRENTDBCOORDACTIVITIES > 2 AND QUEUEDACTIVITIES > 2
COLLECT ACTIVITY DATA CONTINUE)

We should now have an understanding of service superclasses, service classes, workloads, work class sets, and work action sets. The below diagram depicts how we may divide work into service classes and subclasses in the database environment.

Abhik Roy is currently associated with Experian. He has over 10 years of consulting experience in the database technology space with strong focus of big data technologies and parallel processing architectures. His current assignments involve DB2 on distributed, Netezza and Mongo and he is passionate about anything related to databases. He also has a passion for photography and loves experimenting with his camera. He can be reached at roy.abhik@gmail.com

• Author Details

Abhik Roy

roy.abhik@gmail.com

Abhik Roy is currently associated with Experian. He has over 10 years of consulting experience in the database technology space with strong focus of big data technologies and parallel processing architectures. His current assignments involve DB2 on distributed, Netezza and Mongo and he is passionate about anything related to databases. He also has a passion for photography and loves experimenting with his camera.