Example: Using a database work action set and database threshold

This example shows different approaches to using work action sets and thresholds to control the resources consumed by Db2® activities. Before creating Db2 workload management objects, you need to understand how they are used.

Assume that you have a work class set called ALLSQL, and it contains the following work classes in this order:
  1. SMALLDML, which is for all DML-type SQL statement that have an estimated cost of less than 1 000 timerons
  2. MEDDML, which is for all DML-type SQL statements that have an estimated cost between 1 000 and 20 000 timerons
  3. LARGEDML, which is for all DML-type SQL statements that have an estimated cost greater than 20 000 timerons
  4. ALLDDL, which is for all DDL-type SQL statements
  5. ALLACTIVITY, which is for all database activity

The following SQL statements create the work class set and the work classes:


CREATE WORK CLASS SET ALLSQL
  (WORK CLASS SMALLDML WORK TYPE DML FOR TIMERONCOST FROM 0 TO 1000,
   WORK CLASS MEDDML WORK TYPE DML FOR TIMERONCOST FROM 1001 TO 20000,
   WORK CLASS LARGEDML WORK TYPE DML FOR TIMERONCOST FROM 20001 TO UNBOUNDED,
   WORK CLASS ALLDDL WORK TYPE DDL,
   WORK CLASS ALLACTIVITY WORK TYPE ALL)

These work classes already have work actions, such as COUNT ACTIVITY, COLLECT, and thresholds (that are not ACTIVITYTOTALTIME thresholds) applied to them.

Assume that you want to permit large DML activities to run for no longer than 5 hours. All other SQL can take no longer than 30 minutes to run. The following two examples show possible methods for accomplishing this objective.

Method 1

One method is to set up a work action set at the database level containing work actions that specify an ACTIVITYTOTALTIME threshold for each work class, as follows:
Table 1. ACTIVITYTOTALTIME threshold specified for each work class
Work action Work class applied to Threshold type and value Actions
SMALLDMLTIMEALLOWED SMALLDML ACTIVITYTOTALTIME > 30 MINUTES
  • Stop execution
  • Collect activity data
MEDDMLTIMEALLOWED MEDDML ACTIVITYTOTALTIME > 30 MINUTES
  • Stop execution
  • Collect activity data
LARGEDMLTIMEALLOWED LARGEDML ACTIVITYTOTALTIME > 5 HOURS
  • Stop execution
  • Collect activity data
ALLDDLTIMEALLOWED ALLDDL ACTIVITYTOTALTIME > 30 minutes
  • Stop execution
  • Collect activity data
ALLACTIVITYTIMEALLOWED ALLACTIVITY ACTIVITYTOTALTIME > 30 minutes
  • Stop execution
  • Collect activity data

The SQL statements for this method are:


CREATE WORK ACTION SET WASNICK FOR DATABASE USING WORK CLASS SET WCSNICK
  (WORK ACTION SMALLDMLTIMEALLOWED ON WORK CLASS SMALLDML
     WHEN ACTIVITYTOTALTIME > 30 MINUTES COLLECT ACTIVITY DATA STOP EXECUTION,
   WORK ACTION MEDDMLTIMEALLOWED ON WORK CLASS MEDDML
      WHEN ACTIVITYTOTALTIME > 30 MINUTES COLLECT ACTIVITY DATA STOP EXECUTION,
   WORK ACTION LARGEDMLTIMEALLOWED ON WORK CLASS LARGEDML
      WHEN ACTIVITYTOTALTIME > 5 HOURS COLLECT ACTIVITY DATA STOP EXECUTION,
   WORK ACTION ALLDDLTIMETIMEALLOWED ON WORK CLASS ALLDDL
      WHEN ACTIVITYTOTALTIME > 30 MINUTES COLLECT ACTIVITY DATA STOP EXECUTION,
   WORK ACTION ALLACTIVITYTIMEALLOWED ON WORK CLASS ALLACTIVITY
      WHEN ACTIVITYTOTALTIME > 30 MINUTES COLLECT ACTIVITY DATA STOP EXECUTION)

Method 2

Another method might be to use only one work class, LARGEDML, then create a work action set for the database that has one work action, LARGEDMLTIMEALLOWED, applied to the work class.
Table 2. LARGEDMLTIMEALLOWED work action applied to the LARGEDML work class
Work action Work class applied to Threshold type and value Action
LARGEDMLTIMEALLOWED LARGEDML ACTIVITYTOTALTIME < 5 HOURS
  • Stop execution
  • Collect activity data

You would then apply an ACTIVITYTOTALTIME threshold of less than 31 MINUTES to the database. Using this method, only those activities that correspond to the LARGEDML work class have the 5 hour threshold applied to them. Other activities will have the ACTIVITYTOTALTIME database time threshold of less than 31 minutes applied to them.

The SQL statements for this method are:


CREATE WORK ACTION SET WASNICK FOR DATABASE USING WORK CLASS SET WCSNICK
  (WORK ACTION LARGEDMLTIMEALLOWED ON WORK CLASS LARGEDML
     WHEN ACTIVITYTOTALTIME > 5 HOURS COLLECT ACTIVITY DATA STOP EXECUTION)

CREATE THRESHOLD THTEST FOR DATABASE ACTIVITIES ENFORCEMENT DATABASE
   WHEN ACTIVITYTOTAL TIME > 30 MINUTES COLLECT ACTIVITY DATA STOP EXECUTION