Exercise 8: Discovering what types of activities are running on your system

Estimated time: 15-20 minutes

You might want to know the number of large activities or load utilities that are being run concurrently on your system, for example. Understanding the types of work being run on the system is important as different types of work will have different resource requirements and impacts on system performance.

Step 1: Determining the number of activities of each type that are running on your system

Before starting, you might want to show the number of activities of a certain type that are currently running by using the WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES table function:

CONNECT TO SAMPLE

SELECT ACTIVITY_TYPE,
     COUNT(*) AS NUMBER_RUNNING
     FROM TABLE (
     WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES(CAST(NULL AS BIGINT), -2)) AS T
GROUP BY ACTIVITY_TYPE

The output from this query will look something like:

ACTIVITY_TYPE                    NUMBER_RUNNING
-------------------------------- ------------------------
READ_DML                          1

To get information about the different types of activities that have run on your system over a given period of time, you can use work class sets and work actions.  

Step 2: Create a database work action set with count activity work actions

To count the number of times an activity of a specific type has been run over a period of time, a work action set needs to be created. In this example, because we are interested in the activities that are run on the entire system, the work action set will be created at the database level and is associated with the all_class_types work class set that was created in Exercise 4 Step 1. This work class set contains work classes for all types of recognized activities. If we were only interested in the activities being run in a specific service class, we would create a work action set at the service class level. For this example, we are also interested in the information for all types of activities so that the work action set contains a COUNT ACTIVITY work action for each work class in the all_class_types work class set.

CREATE WORK ACTION SET work1_was FOR DATABASE
   USING WORK CLASS SET all_class_types
    (WORK ACTION count_read_wa ON WORK CLASS read_wc COUNT ACTIVITY,
    WORK ACTION count_write_wa ON WORK CLASS write_wc COUNT ACTIVITY,
    WORK ACTION count_ddl_wa ON WORK CLASS ddl_wc COUNT ACTIVITY,
    WORK ACTION count_call_wa ON WORK CLASS call_wc COUNT ACTIVITY,
    WORK ACTION count_load_wa ON WORK CLASS load_wc COUNT ACTIVITY,
    WORK ACTION count_all_wa on WORK CLASS all_wc COUNT ACTIVITY)

Additional information: Each time an activity corresponding to a work class has one or more work actions applied to it, a counter for the work class is incremented by one. The COUNT ACTIVITY work action provides an efficient way to ensure that the counter is updated. If you do not want to perform any other action on an activity other than counting the number of activities of that type that have been run, the COUNT ACTIVITY work action is the best approach.

Step 3: Run some activities

Run the work1.db2 script once.

db2 -tvf work1.db2

Step 4: View work action set statistics

You can use the WLM_GET_WORK_ACTION_SET_STATS table function to access the work action set statistics in memory to get the number of times specific activity types have been run. For example, the following query will tell you the number of activities that were assigned to each of the work classes in the work class set that has a work action associated with it:

CONNECT TO SAMPLE

SELECT SUBSTR(WORK_ACTION_SET_NAME, 1, 12) AS WORK_ACTION_SET_NAME,
                SUBSTR(WORK_CLASS_NAME, 1, 12) AS WORK_CLASS_NAME,
                LAST_RESET,
                SUBSTR(CHAR(ACT_TOTAL), 1, 12) AS TOTAL_ACTS
FROM TABLE(WLM_GET_WORK_ACTION_SET_STATS('', -2)) AS WASSTATS
ORDER BY WORK_ACTION_SET_NAME, WORK_CLASS_NAME, LAST_RESET

Additional Information: The blank included with the statement means that result is not to be restricted by the argument (in this example, we want the information for all of the work action sets). The value of the last argument, member, is the wildcard character -2, which means that data from all database members is to be returned.

The output from this query will look something like the following where "*" represents all activities that do not fall into any of the defined work classes or that fall into work classes with no work actions.

WORK_ACTION_SET_NAME WORK_CLASS_NAME LAST_RESET
TOTAL_ACTS
-------------------- --------------- -------------------------- ---------
-
WORK1_WAS            *               2007-08-14-13.55.30.725886 0
WORK1_WAS            ALL_WC          2007-08-14-13.55.30.725886 2
WORK1_WAS            CALL_WC         2007-08-14-13.55.30.725886 4
WORK1_WAS            DDL_WC          2007-08-14-13.55.30.725886 12
WORK1_WAS            LOAD_WC         2007-08-14-13.55.30.725886 1
WORK1_WAS            READ_WC         2007-08-14-13.55.30.725886 12
WORK1_WAS            WRITE_WC        2007-08-14-13.55.30.725886 6

  7 record(s) selected.

Step 5: Differentiate activities by more than their type and other attributes

You can separate out activities by more than just their types. For example, you might want to know how many large queries are being run.

Alter the work class set to add a new read work class that will represent large queries. For this example, a large query is any query that has a cardinality greater than 40.

ALTER WORK CLASS SET all_class_types
   ADD WORK CLASS large_wc WORK TYPE READ FOR CARDINALITY FROM 41 POSITION AT 1

Additional Information: Note that we positioned this work class at position 1. If the POSITION AT clause is not specified, the work class is positioned at the bottom of the work class set. When deciding which work class an activity belongs to, the work classes are checked in the order they are positioned and the first work class whose attributes match the activities attributes is the class that the activity gets assigned to. In this case, if the large_wc were positioned at the end of the list, the large activities would have been assigned to the read_wc since it was positioned ahead of large_wc.

Alter the work action set to add a COUNT ACTIVITY work action and apply it to the new work class.

ALTER WORK ACTION SET work1_was
    ADD WORK ACTION count_large_reads ON WORK CLASS large_wc COUNT ACTIVITY

Step 6: Reset the statistics and run some activities

Call the WLM_COLLECT_STATS stored procedure to reset the statistics that are stored in memory so that you are starting fresh and when you chose to query that workload management statistical information that is stored in memory, it will contain information for the activities that have been run from this point on.

CALL WLM_COLLECT_STATS()

Run the work1.db2 script once.

db2 -tvf work1.db2

Step 7: View work action set statistics

Use the WLM_GET_WORK_ACTION_SET_STATS table function again to access the work action set statistics in memory to get the number of times specific activity types have been run.

CONNECT TO SAMPLE

SELECT SUBSTR(WORK_ACTION_SET_NAME, 1, 12) AS WORK_ACTION_SET_NAME,
       SUBSTR(CHAR(MEMBER), 1, 4) AS MEMB,
       SUBSTR(WORK_CLASS_NAME, 1, 12) AS WORK_CLASS_NAME,
       LAST_RESET,
       SUBSTR(CHAR(ACT_TOTAL), 1, 12) AS TOTAL_ACTS
FROM TABLE(WLM_GET_WORK_ACTION_SET_STATS('', -2)) AS WASSTATS
ORDER BY WORK_ACTION_SET_NAME, WORK_CLASS_NAME, MEMB

The output will look something such as the following:

WORK_ACTION_SET_NAME MEMB WORK_CLASS_NAME LAST_RESET
TOTAL_ACTS
-------------------- ---- --------------- -------------------------- ----
------
WORK1_WAS            0    *               2007-08-14-13.55.35.650685 0
WORK1_WAS            0    ALL_WC          2007-08-14-13.55.35.650685 2
WORK1_WAS            0    CALL_WC         2007-08-14-13.55.35.650685 4
WORK1_WAS            0    DDL_WC          2007-08-14-13.55.35.650685 12
WORK1_WAS            0    LARGE_WC        2007-08-14-13.55.35.650685 4
WORK1_WAS            0    LOAD_WC         2007-08-14-13.55.35.650685 1
WORK1_WAS            0    READ_WC         2007-08-14-13.55.35.650685 8
WORK1_WAS            0    WRITE_WC        2007-08-14-13.55.35.650685 6

  8 record(s) selected.

Note that this time four of the activities from the script are considered large activities.

Step 8: Reset for the next exercise

Drop the work action set:

ALTER WORK ACTION SET WORK1_WAS
	ALTER WORK ACTION COUNT_READ_WA DISABLE
	ALTER WORK ACTION COUNT_WRITE_WA DISABLE
	ALTER WORK ACTION COUNT_DDL_WA DISABLE
	ALTER WORK ACTION COUNT_CALL_WA DISABLE
	ALTER WORK ACTION COUNT_LOAD_WA DISABLE
	ALTER WORK ACTION COUNT_ALL_WA DISABLE
	ALTER WORK ACTION COUNT_LARGE_READS DISABLE;
ALTER WORK ACTION SET WORK1_WAS DISABLE;
DROP WORK ACTION SET WORK1_WAS;