Thresholds permit you to maintain stability in the system. You create threshold objects in order to catch work that behaves abnormally, either predictively before the work begins running based on the projected impact, or reactively as it is running and consuming resources.
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. |
Threshold | Description |
---|---|
UOWTOTALTIME | Controls the amount of time that a unit of work takes to execute. |
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 database partition 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 database partition 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. |
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 database partition 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 database partition 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 partition. 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.
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 partition 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). |
TOTALDBPARTITIONCONNECTIONS | Controls the number of database connections to a given partition that can be established at the same time. Use to prevent a given partition from becoming overloaded. |
TOTALSCPARTITIONCONNECTIONS | Controls the number of database connections to a given partition for work executing within a given service class at the same time. Similar to the total database partition 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, both activity and concurrency thresholds are applied to the stored procedure itself and its child activities. If the execution of the stored procedure is queued, none of its child activities can proceed. However, when a stored procedure starts running, child activities might be queued.