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
- 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.
CREATE WORKLOAD LONG_UOW APPLNAME('LONGUOW') SERVICE CLASS SYSDEFAULTUSERCLASS
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.