Exercise 2: Isolating activities using service classes and workloads

This exercise demonstrates how to create service classes and how to send activities to a service class using a workload. It also demonstrates how to use some of the WLM monitoring features to determine the workload that activities are being mapped to and to get information about activities being run in a service class and under a workload.

Estimated time: 20-25 minutes

Service classes are the primary point of resource control for database activities. They are also useful for monitoring. For example, you can collect statistics for activities in a particular service class to determine whether the performance goals for that service class are being met. By default, three default service classes (SYSDEFAULTSYSTEMCLASS, SYSDEFAULTMAINTENANCECLASS, and SYSDEFAULTUSERCLASS) are created for each database. If no user defined service classes are created, user activities are run under the default user service class (SYSDEFAULTUSERCLASS).

A workload is an entity that groups one or more units of work based on criteria such as system user ID, session user ID, etc. Workloads provide a means of assigning work to a service class so that the work can later be managed. A default user workload (SYSDEFAULTUSERWORKLOAD) and a default administration workload (SYSDEFAULTADMWORKLOAD) are created for each database. If no user defined workloads are created, all user activities are associated with the default user workload.

There are four separate features that are demonstrated in this exercise:

How to create a service class.
How to create a workload.
How to examine basic workload statistics.
How to collect activity information for activities run under an individual workload.

Step 1: Examine where activities are run with no user-defined service classes and workloads

First examine where activities are executed if there is no user defined service class or workload. All Db2® activities are assigned to a workload and run in a service class. If no user defined service classes are created, activities run in the default subclass (SYSDEFAULTSUBCLASS) under the default user service class (SYSDEFAULTUSERCLASS) and if no user defined workloads are created, activities run under the default user workload (SYSDEFAULTUSERWORKLOAD).

Run the work1.db2 and work2.db2 scripts and then examine the statistics for the SYSDEFAULTSUBCLASS of SYSDEFAULTUSERCLASS using the WLM_GET_SERVICE_SUBCLASS_STATS .

db2 -o -tvf work1.db2
db2 -o -tvf work2.db2

CONNECT TO SAMPLE

SELECT VARCHAR( SERVICE_SUPERCLASS_NAME, 30) SUPERCLASS,
       VARCHAR( SERVICE_SUBCLASS_NAME, 30) SUBCLASS,   
       COORD_ACT_COMPLETED_TOTAL
FROM TABLE(WLM_GET_SERVICE_SUBCLASS_STATS('','',-1)) AS T

You will see output such as the following:

SUPERCLASS                     SUBCLASS                       COORD_ACT_COMPLETE
D_TOTAL
------------------------------ ------------------------------ ------------------
-------
SYSDEFAULTSYSTEMCLASS          SYSDEFAULTSUBCLASS
      0
SYSDEFAULTMAINTENANCECLASS     SYSDEFAULTSUBCLASS
      0
SYSDEFAULTUSERCLASS            SYSDEFAULTSUBCLASS
     75

  3 record(s) selected.

Note all the activities are run in the SYSDEFAULTUSERCLASS service super class.

Additional Information: There are 2 other service classes as well, SYSDEFAULTSYSTEMCLASS and SYSDEFAULTMAINTENANCECLASS. These service classes are used for internal maintenance and system level tasks. User activities will not run in these service classes. You may notice nonzero activity counts in these service classes as well if the Db2 data server has issued any internal activities.

Use the WLM_GET_WORKLOAD_STATS table function to view workload statistics to determine which workload the applications are being associated with.

SELECT SUBSTR(WORKLOAD_NAME, 1, 22) AS WL_DEF_NAME,
       WLO_COMPLETED_TOTAL,
       CONCURRENT_WLO_ACT_TOP FROM
       TABLE(WLM_GET_WORKLOAD_STATS(CAST(NULL AS VARCHAR(128)), -2))
     AS WLSTATS

The output will look something such as the following:

WL_DEF_NAME           WLO_COMPLETED_TOTAL  CONCURRENT_WLO_ACT_TOP
---------------------- -------------------- ----------------------
SYSDEFAULTUSERWORKLOAD                    3                      5
SYSDEFAULTADMWORKLOAD                     0                      0

  2 record(s) selected.

Note there is one workload occurrence completed for both of the scripts (work1.db2 and work2.db2) as well as a workload occurrence for the connection used to execute the previous command.

Step 2: Create a service class and workload

Create a service class and then create a workload such that all activities run from the work1.db2 script get mapped to the newly created service class. When CLP executes a script, the CURRENT CLIENT_APPLNAME special register value is set to "CLP script name".

CREATE SERVICE CLASS work1_sc

CREATE WORKLOAD work1_wl CURRENT CLIENT_APPLNAME('CLP work1.db2')
       SERVICE CLASS work1_sc

Additional Information: There are a number of attributes that can be specified when creating a workload or a service class. For example, when creating a workload, you can identify the connection based on application name, session user, etc. For more information, refer the CREATE WORKLOAD and the CREATE SERVICE CLASS documentation.

Step 3: Grant usage on workload

Grant usage on the workload (requires ACCESSCTRL or SECADM authority).

GRANT USAGE ON WORKLOAD work1_wl TO PUBLIC

Additional information: A connection can be associated with a workload only if the session user has USAGE privilege on the workload. This is necessary to prevent users from changing connection attributes of their application in an attempt to run their work in a higher priority service class. Some connection attributes can be changed programmatically (using the sqleseti API, for example). In this exercise, we just grant USAGE privilege to PUBLIC. You would want to be more discriminating on a real system. Since the sample will be run as DBADM, this step could be skipped altogether.

Step 4: Reset statistics

Reset the statistics using the WLM_COLLECT_STATS function, to clear the statistics collected.

CALL SYSPROC.WLM_COLLECT_STATS()

Step 5: Run some activities

Run both the work1.db2 and the work2.db2 scripts.

db2 -o -tvf work1.db2
db2 -o -tvf work2.db2

Step 6: View workload and service class statistics

Use the WLM_GET_WORKLOAD_STATS table function to view workload statistics to determine which workload the applications are being associated with.

CONNECT TO SAMPLE

SELECT SUBSTR(WORKLOAD_NAME, 1, 22) AS WL_DEF_NAME,
       WLO_COMPLETED_TOTAL,
       CONCURRENT_WLO_ACT_TOP
       FROM TABLE(WLM_GET_WORKLOAD_STATS(CAST(NULL AS VARCHAR(128)), -2))
     AS WLSTATS

The output will look something such as the following:

WL_DEF_NAME            WLO_COMPLETED_TOTAL  CONCURRENT_WLO_ACT_TOP
---------------------- -------------------- ----------------------
WORK1_WL                                  1                      5
SYSDEFAULTUSERWORKLOAD                    1                      5
SYSDEFAULTADMWORKLOAD                     0                      0

Note that one workload occurrence completed under WORK1_WL which is the work1.db2 script. One workload occurrence completed under SYSDEFAULTUSERWORKLOAD which is the work2.db2 script.

You may see a 2^nd workload occurrence completed for the SYSDEFAULTUSER WORKLOAD which is the connection that was used to call the WLM_COLLECT_STATS procedure. WLM_COLLECT_STATS is an asynchronous procedure which might be completed before the statistics are actually collected and therefore might be included.

You can also use the WLM_GET_SERVICE_SUBCLASS_STATS table function to show which service class the activities are being run under as a result of creating the new workload.

SELECT VARCHAR( SERVICE_SUPERCLASS_NAME, 30) SUPERCLASS,
       VARCHAR( SERVICE_SUBCLASS_NAME, 23) SUBCLASS,
       COORD_ACT_COMPLETED_TOTAL COORDACTCOMP
       FROM TABLE(WLM_GET_SERVICE_SUBCLASS_STATS('','',-1)) AS T

The results looking something such as the following:

SUPERCLASS                     SUBCLASS                COORDACTCOMP
------------------------------ ----------------------- --------------------
SYSDEFAULTSYSTEMCLASS          SYSDEFAULTSUBCLASS                         0
SYSDEFAULTMAINTENANCECLASS     SYSDEFAULTSUBCLASS                         0
SYSDEFAULTUSERCLASS            SYSDEFAULTSUBCLASS                        37
WORK1_SC                       SYSDEFAULTSUBCLASS                        37

Note the activities that completed under the WORK1_SC due to the WORK1_WL workload mapping.

Step 7: Create another service class and workload

Create a second service class and then create a workload such that all activities run from the work2.db2 application get mapped to the newly created service class. In addition, set up the workload so that it will collect some activity data. For this example, we just collect activity data without any additional details or values.

CREATE SERVICE CLASS work2_sc

CREATE WORKLOAD work2_wl
              CURRENT CLIENT_APPLNAME('CLP work2.db2')
              SERVICE CLASS work2_sc
              COLLECT ACTIVITY DATA

Additional information: When the COLLECT ACTIVITY DATA clause is specified for a workload, information about any activity submitted by an occurrence of that workload will be sent to the active ACTIVITIES event monitor when the activity completes. The COLLECT ACTIVITY DATA clause permits you to specify how much information should be collected by applying one of the following options:

WITHOUT DETAILS: Collect activity information without statement and compilation environment (the default)
WITH DETAILS: Collect activity information including statement and compilation environment.
WITH DETAILS AND VALUES: Collect activity information including statement and compilation environment, and input data values.

Step 8: Enable the activities event monitor

Enable the event monitors for activities.

The activity event monitor was created in Exercise 1.

SET EVENT MONITOR DB2ACTIVITIES STATE 1

Step 9: Reset statistics and run some activities

Use the WLM_COLLECT_STATS stored procedure to reset the statistics again and run the work1.db2 and work2.db2 scripts again.

CALL SYSPROC.WLM_COLLECT_STATS()

db2 -o -tvf work1.db2
db2 -o -tvf work2.db2

Step 10: View workload and service class statistics

Use the WLM_GET_WORKLOAD_STATS table function again to determine which workload the applications are being associated with

CONNECT TO SAMPLE

SELECT SUBSTR(WORKLOAD_NAME, 1, 22) AS WL_DEF_NAME,
       WLO_COMPLETED_TOTAL,
       CONCURRENT_WLO_ACT_TOP
FROM TABLE(WLM_GET_WORKLOAD_STATS(CAST(NULL AS VARCHAR(128)), -2))
     AS WLSTATS

The output will look something such as the following:

WL_DEF_NAME            WLO_COMPLETED_TOTAL  CONCURRENT_WLO_ACT_TOP
---------------------- -------------------- ----------------------
WORK1_WL                                  1                      5
WORK2_WL                                  1                      5
SYSDEFAULTUSERWORKLOAD                    0                      0
SYSDEFAULTADMWORKLOAD                     0                      0

Note this time both workload definitions have a workload occurrence run, once for each script.

You may or may not see a workload occurrence completed for the SYSDEFAULTUSERWORKLOAD depending on whether workload occurrence over which the call to the WLM_COLLECT_STATS procedure was submitted is closed before the statistics are collected.

Use WLM_GET_SERVICE_SUBCLASS_STATS again to show which service class the activities are being run under as a result of creating the new workload.

SELECT VARCHAR( SERVICE_SUPERCLASS_NAME, 30) SUPERCLASS,
       VARCHAR( SERVICE_SUBCLASS_NAME, 23) SUBCLASS,
       COORD_ACT_COMPLETED_TOTAL COORDACTCOMP
FROM TABLE(WLM_GET_SERVICE_SUBCLASS_STATS('','',-1)) AS T

With the results looking something like:

SUPERCLASS                     SUBCLASS                COORDACTCOMP
------------------------------ ----------------------- --------------------
SYSDEFAULTSYSTEMCLASS          SYSDEFAULTSUBCLASS                         0
SYSDEFAULTMAINTENANCECLASS     SYSDEFAULTSUBCLASS                         0
SYSDEFAULTUSERCLASS            SYSDEFAULTSUBCLASS                         1
WORK1_SC                       SYSDEFAULTSUBCLASS                        37
WORK2_SC                       SYSDEFAULTSUBCLASS                        37

Note this time service super class work2_sc has some activities run under it due to the WORK2_WL mapping. The one activity under SYSDEFAULTUSERCLASS is the query previously run on WLM_GET_WORKLOAD_STATS.

Step 11: View the activity data collected

Query the activity table for information on the activities that have been run. Note that only the activities from the work2.db2 script have been collected because only the work2_wl workload definition has the COLLECT ACTIVITY DATA attribute specified.

SELECT SUBSTR(WORKLOADNAME, 1, 20) WL_DEF_NAME,
       SUBSTR(APPL_NAME, 1, 20) APPL_NAME,
       SUBSTR(ACTIVITY_TYPE, 1, 10) ACT_TYPE
FROM SYSCAT.WORKLOADS, ACTIVITY_DB2ACTIVITIES
WHERE WORKLOADID = WORKLOAD_ID

The results look something like:

WL_DEF_NAME          APPL_NAME            ACT_TYPE
-------------------- -------------------- ----------
WORK2_WL             db2bp                READ_DML
WORK2_WL             db2bp                READ_DML
WORK2_WL             db2bp                READ_DML
WORK2_WL             db2bp                READ_DML
WORK2_WL             db2bp                WRITE_DML
WORK2_WL             db2bp                WRITE_DML
WORK2_WL             db2bp                WRITE_DML
WORK2_WL             db2bp                WRITE_DML
WORK2_WL             db2bp                WRITE_DML
WORK2_WL             db2bp                WRITE_DML
WORK2_WL             db2bp                DDL
WORK2_WL             db2bp                DDL
WORK2_WL             db2bp                DDL
WORK2_WL             db2bp                OTHER
WORK2_WL             db2bp                READ_DML
WORK2_WL             db2bp                READ_DML
WORK2_WL             db2bp                READ_DML
WORK2_WL             db2bp                READ_DML
WORK2_WL             db2bp                READ_DML
WORK2_WL             db2bp                READ_DML
WORK2_WL             db2bp                OTHER
WORK2_WL             db2bp                DDL
WORK2_WL             db2bp                LOAD
WORK2_WL             db2bp                DDL
WORK2_WL             db2bp                DDL
WORK2_WL             db2bp                DDL
WORK2_WL             db2bp                DDL
WORK2_WL             db2bp                DDL
WORK2_WL             db2bp                READ_DML
WORK2_WL             db2bp                CALL
WORK2_WL             db2bp                READ_DML
WORK2_WL             db2bp                CALL
WORK2_WL             db2bp                CALL
WORK2_WL             db2bp                CALL
WORK2_WL             db2bp                DDL
WORK2_WL             db2bp                DDL
WORK2_WL             db2bp                DDL
WORK2_WL             db2bp                DDL
:
:

Step 12: Assign resources to service classes

Now that you have isolated the activities that are generated by these two scripts into separate service classes, you can assign resources to the service classes or monitor the activities that run in those service classes. If the work performed by the script work2.db2 is more important than the work performed by the script work1.db2, you could use the ALTER SERVICE CLASS statement to decrease the amount of CPU that is given to work running in the WORK1_SC service class by setting the CPU limit for WORK1_SC, as shown in the following example. You must first enable the Db2 workload management dispatcher by setting the value of the wlm_dispatcher database manager configuration parameter to YES. The workload management dispatcher manages CPU resources that are allocated to Db2 user and maintenance service classes.

UPDATE DBM CFG USING WLM_DISPATCHER YES

ALTER SERVICE CLASS WORK1_SC CPU LIMIT 20

If you want to capture details about every individual activity that executes in the WORK2_SC service class, use the ALTER SERVICE CLASS statement to enable activity collection for that service class, as shown in the following example:

ALTER SERVICE CLASS SYSDEFAULTSUBCLASS UNDER WORK2_SC
   COLLECT ACTIVITY DATA ON COORDINATOR WITH DETAILS

Step 13: Reset for next exercise

Update workload work2_wl so that no activity data is collected, disable the event monitor and clean up the event monitor table, and call WLM_COLLECT_STATS() to reset the statistics.

ALTER WORKLOAD work2_wl
      COLLECT ACTIVITY DATA NONE

SET EVENT MONITOR DB2ACTIVITIES STATE 0

DELETE from ACTIVITY_DB2ACTIVITIES

CALL WLM_COLLECT_STATS()