Exercise 4: Differentiating activities by activity type
This exercise demonstrates how a work action set can be used to: Collect information about all activities of a certain type; apply a threshold to all activities of a certain type; isolate activities of a certain type by mapping them to a specific service subclass
Estimated time: 25-30 minutes
Work action sets are used to apply an action to an activity based on what the activity is doing rather than who submitted it (as is done with workloads).
Actions can be applied to either:
- All database activities of a certain type (using a database work action set).
- Only to activities of a certain type in a particular service class (using a service class work action set).
This exercise shows both methods.
Additional Information: There are other actions that can be applied, such as collecting statistics for activities of a certain type that are not covered in this exercise.
Step 1: Create a work class set
First, create a work class set containing work classes that will represent the specific types of activities you are interested in. This work class set will be used in conjunction with work action sets to perform actions on the selected types of activities. The following example creates a work class set containing work classes of all possible types, but if you were interested only in one activity type, your work class set could be created to only contain that one work class.
CREATE WORK CLASS SET all_class_types
(WORK CLASS read_wc WORK TYPE READ,
WORK CLASS write_wc WORK TYPE WRITE,
WORK CLASS ddl_wc WORK TYPE DDL,
WORK CLASS call_wc WORK TYPE CALL,
WORK CLASS load_wc WORK TYPE LOAD,
WORK CLASS all_wc WORK TYPE ALL POSITION LAST)Step 2: Enable the activities event monitor
Enable the event monitor for activities that was created in Exercise 1.
SET EVENT MONITOR DB2ACTIVITIES STATE 1Step 3: Create a database work action set
If you want to perform a particular action on all activities of a specific type (such as applying a threshold or collecting activity information), use a database work action set.
Create a work action set at the database level that contains work actions for the specific work class representing the type of activities you want isolated. For this example, we want to collect activity data for all DDL, READ and LOAD activities that run on the system and we also want to stop any large read activity from running. For this exercise, a large read activity is any select statement that has an estimated cost (in timerons) of greater than 10000.
CREATE WORK ACTION SET db_was FOR DATABASE
USING WORK CLASS SET all_class_types
(WORK ACTION collect_load_wa ON WORK CLASS load_wc
COLLECT ACTIVITY DATA WITH DETAILS AND VALUES,
WORK ACTION collect_ddl_wa ON WORK CLASS ddl_wc
COLLECT ACTIVITY DATA WITH DETAILS AND VALUES,
WORK ACTION collect_read_wa ON WORK CLASS read_wc
COLLECT ACTIVITY DATA WITH DETAILS AND VALUES,
WORK ACTION stop_large_read_wa on WORK CLASS read_wc
WHEN ESTIMATEDSQLCOST > 10000 STOP EXECUTION )Step 4: Run activities and view work action set statistics
Run the work1.db2 and work3.db2 scripts.
db2 -o -tvf work1.db2
db2 -o -tvf work3.db2You 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. Note that running the following query shows only the load_wc, read_wc and ddl_wc work classes since they are the only work classes that have an applicable work action. All the other activities are counted under the "*":
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, 10) AS TOTAL_ACTS
FROM TABLE(WLM_GET_WORK_ACTION_SET_STATS('', -2)) AS WASSTATS
ORDER BY WORK_ACTION_SET_NAME, WORK_CLASS_NAMEThe output will look something like:
WORK_ACTION_SET_NAME WORK_CLASS_NAME LAST_RESET TOTAL_ACTS
-------------------- ---------------- ------------------------- ----------
DB_WAS * 2007-08-15-19.02.47.305556 12
DB_WAS DDL_WC 2007-08-15-19.02.47.305556 12
DB_WAS LOAD_WC 2007-08-15-19.02.47.305556 1
DB_WAS READ_WC 2007-08-15-19.02.47.305556 13
4 record(s) selected.Step 5: View the activity data collected
Information about every individual DDL, READ and LOAD activities was collected by the activities event monitor, due to the specification of the COLLECT ACTIVITY DATA work action that was applied to the ddl_wc, read_wc, and the load_wc work classes in step 3. The following are a couple of examples of how you might want to look at this activity information.
To get some basic information about the activities, you can simply query the activity event monitor table with a statement such as the following:
SELECT ACTIVITY_ID,
SUBSTR(ACTIVITY_TYPE, 1, 8) AS ACTIVITY_TYPE,
VARCHAR(APPL_ID, 30) AS APPL_ID,
VARCHAR(APPL_NAME, 10) AS APPL_NAME
FROM ACTIVITY_DB2ACTIVITIESThe output will look something like:
ACTIVITY_ID ACTIVITY_TYPE APPL_ID APPL_NAME
-------------------- ------------- ------------------------------ ----------
1 READ_DML *LOCAL.karenam.070815192410 db2bp
1 READ_DML *LOCAL.karenam.070815192418 db2bp
2 READ_DML *LOCAL.karenam.070815192418 db2bp
3 READ_DML *LOCAL.karenam.070815192418 db2bp
4 READ_DML *LOCAL.karenam.070815192418 db2bp
1 DDL *LOCAL.karenam.070815192418 db2bp
2 DDL *LOCAL.karenam.070815192418 db2bp
3 DDL *LOCAL.karenam.070815192418 db2bp
2 READ_DML *LOCAL.karenam.070815192418 db2bp
1 READ_DML *LOCAL.karenam.070815192418 db2bp
2 READ_DML *LOCAL.karenam.070815192418 db2bp
3 READ_DML *LOCAL.karenam.070815192418 db2bp
4 READ_DML *LOCAL.karenam.070815192418 db2bp
6 LOAD *LOCAL.karenam.070815192418 db2bp
1 DDL *LOCAL.karenam.070815192418 db2bp
1 DDL *LOCAL.karenam.070815192418 db2bp
2 DDL *LOCAL.karenam.070815192418 db2bp
3 DDL *LOCAL.karenam.070815192418 db2bp
4 DDL *LOCAL.karenam.070815192418 db2bp
5 READ_DML *LOCAL.karenam.070815192418 db2bp
10 READ_DML *LOCAL.karenam.070815192418 db2bp
1 DDL *LOCAL.karenam.070815192418 db2bp
2 DDL *LOCAL.karenam.070815192418 db2bp
3 DDL *LOCAL.karenam.070815192418 db2bp
4 DDL *LOCAL.karenam.070815192418 db2bp
1 READ_DML *LOCAL.karenam.070815192426 db2bp
26 record(s) selected.To obtain additional information about each activity, such as activity text and what service class it ran under, you can perform a query similar to this one:
SELECT VARCHAR(A.APPL_NAME, 15) as APPL_NAME,
VARCHAR(A.TPMON_CLIENT_APP, 20) AS CLIENT_APP_NAME,
VARCHAR(A.APPL_ID, 30) as APPL_ID,
VARCHAR(A.SERVICE_SUPERCLASS_NAME, 20) as SUPER_CLASS,
VARCHAR(A.SERVICE_SUBCLASS_NAME, 20) as SUB_CLASS,
SQLCODE,
VARCHAR(S.STMT_TEXT, 300) AS STMT_TEXT
FROM ACTIVITY_DB2ACTIVITIES AS A, ACTIVITYSTMT_DB2ACTIVITIES AS S
WHERE A.APPL_ID = S.APPL_ID AND
A.ACTIVITY_ID = S.ACTIVITY_ID AND
A.UOW_ID = S.UOW_IDThe output will look something like:
APPL_NAME CLIENT_APP_NAME APPL_ID
SUPER_CLASS
SUB_CLASS SQLCODE STMT_TEXT
--------------- -------------------- ------------------------------ ---------
---
-------- -------------------- ----------- -----------------------------------
---
-----------------------------------------------------------------------------
---
-----------------------------------------------------------------------------
---
-----------------------------------------------------------------------------
---
----------------------
db2bp CLP wasdbsc.db2 *LOCAL.karenam.070815192410
SYSDEFAULTUS
ERCLASS SYSDEFAULTSUBCLASS 0 SELECT DISTINCT CURRENT SQLID FROM
SYS
IBM.SYSTABLES
db2bp CLP work1.db2 *LOCAL.karenam.070815192418
SYSDEFAULTUS
ERCLASS SYSDEFAULTSUBCLASS 0 values(current client_applname)
:
:
db2bp CLP work1.db2 *LOCAL.karenam.070815192418
SYSDEFAULTUS
ERCLASS SYSDEFAULTSUBCLASS 0 drop procedure stp2
db2bp CLP work3.db2 *LOCAL.karenam.070815192426
SYSDEFAULTUS
ERCLASS SYSDEFAULTSUBCLASS -4712 select count(*) from syscat.tables,
sy
scat.tables, syscat.tables, syscat.tables, syscat.tables, syscat.tables
:
:Note that one of the activities has an SQLCODE of -4712. This indicates execution of the activity was stopped due to a threshold violation. The threshold defined for the stop_large_read_wa work action will prevent any SELECT statement with an estimated cost of greater than 10000 from executing.
Additional information: Load activities (not including load from a cursor) do not have an entry in the activity statement event monitor table (activitystmt_db2activities table) which explains why there is no record for the single load activity that is run by the work1.db2 script in the output shown previously in the last query. The reason for this is that load activities are not SQL statements. For load from cursor activities, there is an entry for the cursor statement in the activity statement event monitor table because the cursor itself is a separate activity. There is an entry for all load activities in the activities event monitor table (activity_db2activities).
Step 6: Disable work actions
Before moving on to the service class work action set, drop the database work action set.
DROP WORK ACTION SET db_wasAdditional information: Before dropping any concurrency threshold, that threshold must first be disabled. In this case, there are no work actions that represent a concurrency threshold but if there were, the only way to disable it would be by disabling the work action. A work action threshold cannot be manipulated through THRESHOLD SQL statements; they can be manipulated only through WORK ACTION SET SQL statements. Only work actions that represent concurrency thresholds need to be disabled before dropping the subsequent work action set. For this exercise, because there are no work actions that represent a concurrency threshold, there is no need to disable any of the work actions before dropping the work action set.
If you want to apply a particular action, such as a threshold, to all the activities of a certain type running in a service super class, you should consider using a service class work action set. You can create a mapping work action to map specific types of activities to a specific service subclass and then apply a threshold to that service subclass. The following steps demonstrate how service class work action sets might be used
Step 7: Create a service class to and create a workload
Create a service subclass under the work1_sc service super class that was created in Exercise 2 Step 2.
The service super class work1_sc is the service class that the activities will be mapped to through the workloads. The service subclass work1_sc_read is the service class that the read activities will be mapped to through the work action.
CREATE SERVICE CLASS work1_sc_read UNDER work1_scCreate a workload so that all activities submitted by the work3.db2 script will be mapped to work1_sc service super class. Note that activities from work1.db2 are already being mapped to work1_sc from one of the previous exercises.
CREATE WORKLOAD work3_wl CURRENT CLIENT_APPLNAME('CLP work3.db2')
SERVICE CLASS work1_scStep 8: Create a service class work action set
Create a work action set at the service class level that contains work actions that apply to the specific work classes representing the types of activities you want isolated. For this example, we want to collect activity data for all DDL, read, and load activities that run under the work1_sc service class and we also want to map read activities to a separate service subclass so that we can treat them differently; in this case, a threshold will be applied to the service subclass to stop any large SELECT statements from running.
CREATE WORK ACTION SET sc_was FOR SERVICE CLASS work1_sc
USING WORK CLASS SET all_class_types (
WORK ACTION collect_load_wa ON WORK CLASS load_wc
COLLECT ACTIVITY DATA ON ALL MEMBERS WITH DETAILS AND VALUES,
WORK ACTION collect_ddl_wa ON WORK CLASS ddl_wc
COLLECT ACTIVITY DATA ON ALL MEMBERS WITH DETAILS AND VALUES,
WORK ACTION collect_read_wa ON WORK CLASS read_wc
COLLECT ACTIVITY DATA ON ALL MEMBERS WITH DETAILS AND VALUES,
WORK ACTION map_read_wa on WORK CLASS read_wc
MAP ACTIVITY TO work1_sc_read)Step 9: Create a service class threshold
To get an effect similar to the stop_large_read_wa work action that prevented any large SELECT statements from running, create an ESTIMATEDSQLCOST threshold and apply it to the work1_sc_read service subclass.
CREATE THRESHOLD stop_large_activities FOR SERVICE CLASS work1_sc_read
UNDER work1_sc
ACTIVITIES ENFORCEMENT DATABASE
WHEN ESTIMATEDSQLCOST >10000 STOP EXECUTIONStep 10: Clear the activity tables, reset the statistics, and run activities
Clear out all of the activity tables so that you can start afresh before running the script again. Then call the wlm_collect_stats() stored procedure to reset the statistics
DELETE FROM activity_db2activities
DELETE FROM activitystmt_db2activities
DELETE FROM activityvals_db2activities
CALL wlm_collect_stats()Now, run work1.db2 and work3.db2 scripts once.
db2 -o -tvf work1.db2
db2 -o -tvf work3.db2Note the SQL04712 error for activities that caused the threshold to be exceeded.
Step 11: View work action set statistics
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. Note that running the following query shows only the load_wc, ddl_wc, and the read_wc work classes since they are the only three work classes that have a work action applied to them. All the other activities end up being counted under "*":
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, 10) AS TOTAL_ACTS
FROM TABLE(WLM_GET_WORK_ACTION_SET_STATS('', -2)) AS WASSTATS
ORDER BY WORK_ACTION_SET_NAME, WORK_CLASS_NAME, MEMBThis time, output will look something such as the following:
WORK_ACTION_SET_NAME MEMB WORK_CLASS_NAME LAST_RESET TOTAL_ACTS
-------------------- ---- --------------- -------------------------- ----------
SC_WAS 0 * 2007-08-15-19.02.54.597999 12
SC_WAS 0 DDL_WC 2007-08-15-19.02.54.597999 12
SC_WAS 0 LOAD_WC 2007-08-15-19.02.54.597999 1
SC_WAS 0 READ_WC 2007-08-15-19.02.54.597999 12
4 record(s) selected.Step 12: View the activity data collected
Now query the activity tables again to get information about the individual activities. Note the service subclass that the activities were run under.
SELECT VARCHAR(A.APPL_NAME, 15) as APPL_NAME,
VARCHAR(A.TPMON_CLIENT_APP, 20) AS CLIENT_APP_NAME,
VARCHAR(A.APPL_ID, 30) as APPL_ID,
VARCHAR(A.SERVICE_SUPERCLASS_NAME, 20) as SUPER_CLASS,
VARCHAR(A.SERVICE_SUBCLASS_NAME, 20) as SUB_CLASS,
SQLCODE,
VARCHAR(S.STMT_TEXT, 300) AS STMT_TEXT
FROM ACTIVITY_DB2ACTIVITIES AS A, ACTIVITYSTMT_DB2ACTIVITIES AS S
WHERE A.APPL_ID = S.APPL_ID AND
A.ACTIVITY_ID = S.ACTIVITY_ID AND
A.UOW_ID = S.UOW_IDThe output will look something like:
APPL_NAME CLIENT_APP_NAME APPL_ID SUPER_CLASS
SUB_CLASS SQLCODE STMT_TEXT
--------------- -------------------- ------------------------------ ------------
-------- -------------------- ----------- --------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
----------------------
db2bp CLP work1.db2 *LOCAL.karenam.070815195555 WORK1_SC
WORK1_SC_READ 0 values(current client_applname)
db2bp CLP work1.db2 *LOCAL.karenam.070815195555 WORK1_SC
WORK1_SC_READ 0 select * from org
:
:
db2bp CLP work1.db2 *LOCAL.karenam.070815195555 WORK1_SC
SYSDEFAULTSUBCLASS 0 drop procedure stp2
db2bp CLP work3.db2 *LOCAL.karenam.070815195600 WORK1_SC
WORK1_SC_READ -4712 select count(*) from syscat.tables, sy
scat.tables, syscat.tables, syscat.tables, syscat.tables, syscat.tablesNote again, that one of the activities has a SQLCODE of -4712, this time because of the stop_large_activities service class threshold that was created in step 9 that was violated because the estimated cost for that select statement was too large. Also notice that all read activities are being run under the work1_sc_read service subclass.
Step 13: Reset for next exercise
Disable the even monitor, drop the service class threshold and drop the service class work action set.
SET EVENT MONITOR DB2ACTIVITIES STATE 0
DROP THRESHOLD STOP_LARGE_ACTIVITIES
ALTER WORK ACTION SET SC_WAS
ALTER WORK ACTION COLLECT_LOAD_WA DISABLE
ALTER WORK ACTION COLLECT_DDL_WA DISABLE
ALTER WORK ACTION COLLECT_READ_WA DISABLE
ALTER WORK ACTION MAP_READ_WA DISABLE;
DROP WORK ACTION SET SC_WASClear out all of the activity tables so that you can start afresh, before running the script again.
DELETE FROM activity_db2activities
DELETE FROM activitystmt_db2activities
DELETE from activityvals_db2activitiesDisable all of the workloads that have been created so that all activities will run under the default user workload and get mapped to the default service super class.
ALTER WORKLOAD work1_wl DISABLE
ALTER WORKLOAD work2_wl DISABLE
ALTER WORKLOAD work3_wl DISABLE
ALTER WORKLOAD workth_wl DISABLECall the wlm_collect_stats() stored procedure to reset the statistics.
CALL WLM_COLLECT_STATS()