Control of work with thresholds

An example of work that can be controlled with thresholds is a query that consumes large amounts of processor time at the expense of all other work running on the system. Such a query can be controlled either before it even begins executing, based on estimated cost, or after it has begun executing and is consuming more than the permitted amount of resources.

Types of thresholds

Connection thresholds

If you want to limit how long a connection can sit idle, use a connection threshold. These thresholds can be used to detect connections that sit idle for too long.

Table 1. Connection thresholds

Threshold	Description
CONNECTIONIDLETIME	Controls the amount of time that a connection sits idle and is not working on behalf of user requests. Use this threshold to detect inefficient use of data server resources and application wait conditions.

Unit of work thresholds

If you want to limit how long a unit of work executes, use a unit of work threshold. These thresholds limit the maximum amount of time that a unit of work may spend in the DB2® engine, and can be used to detect units of work that take too long to complete.

Table 2. Unit of work thresholds

Threshold	Description
UOWTOTALTIME	Controls the amount of time that a unit of work takes to execute.

Activity thresholds

If you want to limit the impact that specific activities can have on how the data server is running, activity thresholds provide you with one of the means you can use. Excess execution time, abnormally high volumes of data returned, or abnormally high amounts of resources consumed are all examples of warning flags that potentially troublesome activities could be consuming excessive resources, which you can control with activity thresholds.

Table 3. Activity thresholds

Threshold	Description
ACTIVITYTOTALTIME	Controls the amount of time that any given activity can spend from submission to completion, for both execution and queue time. Use this threshold to detect jobs that are taking an abnormally long time to complete.
CPUTIME	Controls the maximum amount of combined user and system processor time that an activity may consume on a particular member during the execution of the activity. Use this threshold to detect and control activities which are consuming excessive processor resources.
CPUTIMEINSC	Controls the maximum amount of combined user and system processor time that an activity may consume on a particular member while executing in a specific service subclass. Use this threshold to detect and control activities which are consuming excessive processor resources within the current service class.
DATATAGINSC	Controls the data that can be touched or not touched by an activity while running a particular service subclass.
ESTIMATEDSQLCOST	Controls DML activities that the query optimizer determines to have a large estimated cost. Use this threshold to predict potentially resource-heavy SQL before it starts executing on the system and identifying poorly written SQL.
SQLROWSREAD	Controls the maximum number of rows which can be read on any member by an activity. Use this threshold to detect and control activities which are reading an excessive number of rows.
SQLROWSREADINSC	Controls the maximum number of rows which can be read by an activity on a particular member while executing in a specific service subclass. Use this threshold to detect and control activities which are reading an excessive number of rows within the current service class.
SQLROWSRETURNED	Controls the number of rows returned when executing SQL. Use this threshold to identify when the amount of data exceeds a reasonable volume.
SQLTEMPSPACE	Controls the amount of temporary table space a given activity can consume on a member. Use this threshold to prevent certain SQL statements from using up a disproportionate amount of temporary space, impeding the progress of other work.

The data server considers requests from utilities such as REORGCHK, IMPORT, and EXPORT to be user logic, and thus these requests are subject to any defined thresholds.

Aggregate thresholds

If you want to control the collective impact that certain activities, workloads, or connections can have on your data server, you can define aggregate thresholds. Aggregate thresholds often, but not always, enforce concurrency control in cases where you need to limit the number of certain activities running at the same time. Some aggregate thresholds have a built-in queue and are known as queuing thresholds.

Table 4. Aggregate thresholds

Threshold	Description
AGGSQLTEMPSPACE	Controls the maximum amount of system temporary table space that can be consumed in total across all activities in the service subclass. Use this threshold to detect and control activities that belong to a service subclass whose activities are consuming too much system temporary table space in aggregate across all of the activities in the service subclass.
CONCURRENTWORKLOADOCCURRENCES	Controls the number of active occurrences of a workload that can run on a coordinator member at the same time. Use to control the spread of connections from a specific source.
CONCURRENTWORKLOADACTIVITIES	Controls the number of individual activities that can run within a workload occurrence. Use to limit work within an individual workload occurrence.
CONCURRENTDBCOORDACTIVITIES	Controls the number of concurrent activities in the domain that the threshold is associated with (database, work action, service superclass, or service subclass).
TOTALMEMBERCONNECTIONS	Controls the number of database connections to a given member that can be established at the same time. Use to prevent a given member from becoming overloaded.
TOTALSCMEMBERCONNECTIONS	Controls the number of database connections to a given member for work executing within a given service class at the same time. Similar to the total member connections but more granular because the connection is linked to a service class.

For those aggregate thresholds that support it, concurrency control is provided through a system of execution 'tickets.' Each incoming activity must claim a ticket from the applicable concurrency threshold before it can begin executing. Once all tickets are consumed, additional activities are queued until a ticket becomes available or an error is returned, depending on how you defined the threshold. If the concurrency threshold has queuing enabled, then a ticket passes from an activity that has finished execution to another activity that is in the queue. This activity can then leave the queue and begin execution. How many tickets are available per concurrency threshold depends on how you defined the threshold. For example, if you defined a CONCURRENTDBCOORDACTIVITIES threshold to limit the number of concurrently running database activities to 10, then there are 10 execution tickets available.

For stored procedures, activity thresholds are applied to the stored procedure and to its child activities. Concurrency thresholds are only applied to the child activities of the stored procedure and not to the CALL statement itself.

Taking action when thresholds are violated