DB2 Version 10.1 for Linux, UNIX, and Windows

Example: Using thresholds

You can use thresholds for a variety of purposes. In this scenario, thresholds are used to control the number of large jobs running in order to permit different execution times for different applications, and to control the behavior of an application that is in development.

You could use a DB2® workload management solution to divide and manage the database resources for the various departments in a company. For example, assume that the sales department runs two main reports, which consist of the monthly and yearly sales. Assume also that the human resources department runs a payroll application every other week and that the development team is working on a new type of report at the request of the management team. To define different execution environments for these departments, create service classes:

CREATE SERVICE CLASS SALES
CREATE SERVICE CLASS HUMANRESOURCES
CREATE SERVICE CLASS DEVELOPMENT

In this situation, you create a workload definition for each one of these applications to map the application to its applicable service superclass:

CREATE WORKLOAD MONTHLYSALES APPLNAME('monthlyrpt.exe') SERVICE CLASS SALES
CREATE WORKLOAD YEARLYSALES APPLNAME('yearlyrpt.exe') SERVICE CLASS SALES
CREATE WORKLOAD PAYROLL APPLNAME('payroll.exe') SERVICE CLASS HUMANRESOURCES
CREATE WORKLOAD NEWREPORT APPLNAME('dev.exe') SERVICE CLASS DEVELOPMENT

The database catalog therefore contains the following workload definitions:

MonthlySales, mapping to the service superclass Sales
YearlySales, mapping to the service superclass Sales
Payroll, mapping to the service superclass Human Resources
NewReport, mapping to the service superclass Development

Threshold on the number of large jobs

Because the YearlySales report is very large, you do not want to have more than one occurrence of this application running in the database at any time. You therefore create a threshold to set the maximum number of concurrent occurrences of this workload to 1:

CREATE THRESHOLD SINGLEYEARLYSALESRPT FOR WORKLOAD YEARLYSALES ACTIVITIES
   ENFORCEMENT MEMBER
   WHEN CONCURRENTWORKLOADOCCURRENCES > 1
   STOP EXECUTION

You can achieve a similar solution by associating the YearlySales application with a service subclass YearlySalesReports (under the Sales service superclass) and setting the maximum concurrency threshold to a value of 1 for the service subclass:

CREATE SERVICE CLASS YEARLYSALESREPORTS UNDER SALES

ALTER WORKLOAD YEARLYSALES SERVICE CLASS YEARLYSALESREPORTS UNDER SALES

CREATE THRESHOLD SINGLEYEARLYSALESREPORT FOR SERVICE CLASS YEARLYSALESREPORTS
   UNDER SALES ACTIVITIES ENFORCEMENT DATABASE
   WHEN CONCURRENTDBCOORDACTIVITIES > 1
   STOP EXECUTION

In either situation, you can set the threshold action to STOP EXECUTION to prevent more than one occurrence of the workload from executing. You can also collect activity information if you want additional information about the conditions when the threshold is violated.

Threshold on activity lifetimes

Because all applications are expected to complete in an hour or less, you create a threshold with a database domain, preventing any activity from running longer than 1 hour. The only exception to this rule is the yearly report, which can take up to 5 hours to complete. Therefore, you can associate an activity total time threshold of 5 hours with the YearlySales workload. This will override the activity total time threshold applied to the yearly sales report, relaxing the time constraints. The new value of 5 hours now applies to the YearlySales workload although the global value of 1 hour applies elsewhere in the database:

CREATE THRESHOLD MAXDBACTIVITYTIME FOR DATABASE ACTIVITIES
   ENFORCEMENT DATABASE
   WHEN ACTIVITYTOTALTIME > 1 HOUR
   STOP EXECUTION

CREATE THRESHOLD MAXYRPTACTIVITYTIME FOR WORKLOAD YEARLYSALES
   ACTIVITIES ENFORCEMENT DATABASE
   WHEN ACTIVITYTOTALTIME > 5 HOURS
   STOP EXECUTION

Threshold on the number of coordinator and nested activities

The NewReport application makes heavy use of stored procedures and user-defined functions and is not fully debugged yet, so it tends to generate large numbers of activities that impact the rest of the system. After consulting with the developer, you learn that this new report is not supposed to generate more than 20 activities in total, so you define a threshold of type workload activities on the NewReport workload and set it to 20. Initially, you set the threshold action to STOP EXECUTION and COLLECT ALL to stop any unwanted side effect of the application starting large numbers of activities and to help the developer identify any problems:

CREATE THRESHOLD MAXDEVACTIVITIES FOR SERVICE CLASS DEVELOPMENT ACTIVITIES
   ENFORCEMENT DATABASE
   WHEN CONCURRENTDBCOORDACTIVITIES > 20
   COLLECT ACTIVITY DATA WITH DETAILS AND VALUES
   STOP EXECUTION

When the application becomes more stable, it enters its optimization phase. During the phase, the developer tries to reduce the number of activities generated by the application from between 15 and 20 to 15. At this time, you alter the threshold by changing its upper boundary value to 15 and the threshold action to CONTINUE. This threshold definition helps identify and address situations in which the number of generated activities exceeds 15 but the increased stability of the application does not require that its execution be stopped.

ALTER THRESHOLD MAXDEVACTIVITIES
   WHEN CONCURRENTDBCOORDACTIVITIES > 15
   COLLECT ACTIVITY DATA ON COORDINATOR WITH DETAILS AND VALUES
   CONTINUE

Threshold to limit how long a unit of work runs

The application LongUOW issues transactions that can occasionally run longer than the desired ten minutes. This results in locks being held for too long and prevents more important applications from proceeding. In this case, you want to force the application, rather than let it hold up other work. You can restrict the runtime for this application's transactions to an administrator-defined period of time using the UOWTOTALTIME threshold.

First, create a workload for the LongUOW application:

CREATE WORKLOAD LONG_UOW APPLNAME('LONGUOW') SERVICE CLASS SYSDEFAULTUSERCLASS

Then, create a threshold for this workload that forces the LongUOW application when any of the application's transactions take more than 10 minutes to finish:

CREATE THRESHOLD FORCELONGUOW FOR WORKLOAD LONG_UOW ACTIVITIES ENFORCEMENT DATABASE 
	WHEN UOWTOTALTIME > 10 MINUTES FORCE APPLICATION

You can also apply this threshold at the service subclass level or database level.