DB2用work action设置数据库SQL执行超时后终止

数据库管理 >  DB2 workload manager >  Activities management >  Apply controls to types of activities with work action sets

 DB2 Version 9.7 for Linux, UNIX, and Windows

---

Example: Using a 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 manager 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 with the ACTIVITYTOTALTIME threshold specified 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 < 31 MINUTES	Stop execution Collect activity data
MEDDMLTIMEALLOWED	MEDDML	ACTIVITYTOTALTIME < 31 MINUTES	Stop execution Collect activity data
LARGEDMLTIMEALLOWED	LARGEDML	ACTIVITYTOTALTIME < 5 HOURS	Stop execution Collect activity data
ALLDDLTIMEALLOWED	ALLDDL	ACTIVITYTOTALTIME < 31 minutes	Stop execution Collect activity data
ALLACTIVITYTIMEALLOWED	ALLACTIVITY	ACTIVITYTOTALTIME < 31 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