CREATE THRESHOLD statement

The CREATE THRESHOLD statement defines a threshold.

Invocation

This statement can be embedded in an application program or issued through the use of dynamic SQL statements. It is an executable statement that can be dynamically prepared only if DYNAMICRULES run behavior is in effect for the package (SQLSTATE 42509).

Authorization

The privileges held by the authorization ID of the statement must include WLMADM or DBADM authority.

Description

threshold-nameNames the threshold. This is a one-part name. It is an SQL identifier (either ordinary or delimited). The threshold-name must not identify a threshold that already exists at the current server (SQLSTATE 42710). The name must not begin with the characters 'SYS' (SQLSTATE 42939).

FOR threshold-domain ACTIVITIES

Specifies the definition domain of the threshold.

DATABASE

This threshold applies to any activity in the database.

SERVICE CLASS service-class-name

This threshold applies to activities executing in service class service-class-name. If UNDER is not specified, service-class-name must identify an existing service superclass (SQLSTATE 42704). If UNDER is specified, service-class-name must identify an existing service subclass of the service superclass specified after the UNDER keyword (SQLSTATE 42704). The service-class-name cannot be the SYSDEFAULTSYSTEMCLASS service class or the SYSDEFAULTMAINTENANCECLASS service class (SQLSTATE 5U032).

UNDER service-class-name: Specifies a service superclass. The service-class-name must identify an existing service superclass (SQLSTATE 42704).

STATEMENT

This threshold applies to activities for a specific SQL statement. You identify the statement to use for the threshold by specifying the statement text or the statement's executable ID .

TEXT statement-text

This threshold applies to statements matching the text specified in statement-text. Both static and dynamic SQL statements are considered when the condition for the threshold is evaluated. At run time, the text specified for statement-text must be an exact match of the text of a statement in the package cache for the threshold to be violated. Differences in letter case or use of white space prevent a match from occurring between statement-text and any running SQL statement. The text for statement-text must be specified as a string constant. As such, the maximum length for the text of a statement for a statement threshold is 32 672 bytes, and not the usual 2 MB upper limit for statements.

Access plan differences do not affect statement matching. It is possible for multiple cached statements with same text but different access plans to match the threshold text defined by statement-text.

If a statement that otherwise matches the statement supplied for statement-text is altered or transformed during compilation in such a way that it differs from statement-text, the statements will not match. For example, if the statement concentrator is enabled, literal values might be replaced by parameter markers. No such transformation is applied to text supplied for the statement-text in the CREATE THRESHOLD statement. The text supplied to CREATE THRESHOLD must match exactly the transformed text of any statement of interest. You can determine the exact text of statements as they are executed using monitoring table functions such as MON_GET_PKG_CACHE_STMT and MON_GET_ACTIVITY_DETAILS.

The following predicates can be used with a statement threshold:

ACTIVITYTOTALRUNTIME
ACTIVITYTOTALTIME
CPUTIME
ESTIMATEDSQLCOST
SQLROWSREAD
SQLROWSRETURNED
SQLTEMPSPACE

REFERENCE executable-id

This threshold applies to statements with text that matches the text of the statement with the specified executable ID. The database manager uses the executable ID to locate text of the statement from its section in the package cache. The text of the statement that is used for the threshold is that which was cached for the section at the time the threshold was created. For dynamic SQL, the statement referenced by the executable ID must be in the package cache. For static SQL, if the statement is not in the cache, the database manager retrieves it from the system catalogs.

Once the statement text is retrieved from the package cache, there is no direct relationship between the threshold and the specified executable ID; the cached section can even be evicted from the cache without impact on any threshold that was derived from it. Once the text associated with the executable ID is determined, the threshold created by this clause behaves in exactly the same way as one created by the STATEMENT TEXT clause.

WORKLOAD workload-name

This threshold applies to the specified workload. The workload-name must identify an existing workload (SQLSTATE 42704).

ENFORCEMENT enforcement-scope

The enforcement scope of the threshold.

DEFAULT: The default enforcement scope of the threshold will be used.
DATABASE: The threshold is enforced across all members within the definition domain; that is, all members of the database, and all members of the service class.
MEMBER: The threshold is enforced on a per member basis. There is no coordination across all members to enforce the threshold.
WORKLOAD OCCURRENCE: The threshold is enforced only within a workload occurrence. Two workload occurrences running concurrently on the same member will each have their own running count for this threshold.

ENABLE or DISABLE

Specifies whether or not the threshold is enabled for use by the database manager.

ENABLE: The threshold is used by the database manager to restrict the execution of database activities.
DISABLE: The threshold is not used by the database manager to restrict the execution of database activities.

WHEN threshold-predicate

Specifies the condition of the threshold.

TOTALMEMBERCONNECTIONS > integer-value

This condition defines an upper bound on the number of coordinator connections that can run concurrently on a member. This value can be zero or any positive integer (SQLSTATE 42820). A value of zero means that any new coordinator connection will be prevented from connecting. All currently running or queued connections will continue. The definition domain for this condition must be DATABASE, and the enforcement scope must be MEMBER (SQLSTATE 5U037). This threshold is not enforced for users with DBADM or WLMADM authority.

TOTALSCMEMBERCONNECTIONS > integer-value

This condition defines an upper bound on the number of coordinator connections that can run concurrently on a member in a specific service superclass. This value can be zero or any positive integer (SQLSTATE 42820). A value of zero means that any new connection will be prevented from joining the service class. All currently running or queued connections will continue. The definition domain for this condition must be SERVICE SUPERCLASS, and the enforcement scope must be MEMBER (SQLSTATE 5U037).

AND QUEUEDCONNECTIONS > integer-value or AND QUEUEDCONNECTIONS UNBOUNDED: Specifies a queue size for when the maximum number of coordinator connections is exceeded. This value can be any positive integer, including zero (SQLSTATE 42820). A value of zero means that no coordinator connections are queued. Specifying UNBOUNDED will queue every connection that exceeds the specified maximum number of coordinator connections, and the threshold-exceeded-actions will never be executed. The default is zero.

This condition defines an upper bound for the amount of time the database manager will allow a connection to remain idle. This value can be any positive integer (not zero) (SQLSTATE 42820). Use a valid duration keyword to specify an appropriate unit of time for integer-value. The definition domain for this condition must be DATABASE or SERVICE SUPERCLASS, and the enforcement scope must be DATABASE (SQLSTATE 5U037). This condition is enforced at the coordinator member.

If you specify the STOP EXECUTION action with CONNECTIONIDLETIME thresholds, the connection for the application is dropped when the threshold is exceeded. Any subsequent attempt by the application to access the data server will receive SQLSTATE 5U026.

The maximum value for this threshold is 2 147 483 640 seconds. Any value specified that has a seconds equivalent larger than 2 147 483 640 seconds will be set to this number of seconds.

CONCURRENTWORKLOADOCCURRENCES > integer-value

This condition defines an upper bound on the number of concurrent occurrences for the workload on each member. This value can be any positive integer (not zero) (SQLSTATE 42820). The definition domain for this condition must be WORKLOAD and the enforcement scope must be MEMBER (SQLSTATE 5U037).

CONCURRENTWORKLOADACTIVITIES > integer-value

This condition defines an upper bound on the number of concurrent coordinator activities and nested activities for the workload on each member. This value can be any positive integer (not zero) (SQLSTATE 42820). The definition domain for this condition must be WORKLOAD and the enforcement scope for this condition must be WORKLOAD OCCURRENCE (SQLSTATE 5U037).

Each nested activity must satisfy the following conditions:

It must be a recognized coordinator activity. Any nested coordinator activity that does not fall within the recognized types of activities will not be counted. Similarly, nested subagent activities, such as remote node requests, are not counted.
It must be directly invoked from user logic, such as a user-written procedure issuing SQL statements.

Internal SQL activities, such as those initiated by the setting of a constraint or the refreshing of a materialized query table, are also not counted by this threshold, because they are initiated by the database manager and not directly invoked by user logic.

CONCURRENTDBCOORDACTIVITIES > integer-value

This condition defines an upper bound on the number of recognized database coordinator activities that can run concurrently on all members in the specified domain. This value can be zero or any positive integer (SQLSTATE 42820). A value of zero means that any new database coordinator activities will be prevented from executing. All currently running or queued database coordinator activities will continue. The definition domain for this condition must be DATABASE, work action (a threshold for a work action definition domain is created using a CREATE WORK ACTION SET or ALTER WORK ACTION SET statement, and the work action set must be applied to a workload or a database), SERVICE SUPERCLASS, or SERVICE SUBCLASS. Also, the enforcement scope must be DATABASE (SQLSTATE 5U037) in environments other than Db2® pureScale®, where the condition is enforced across the entire database, and MEMBER (SQLSTATE 5U037) in Db2 pureScale where the condition is enforced at each coordinator member. All activities are tracked by this condition, except for the following items:

CALL statements are not controlled by this threshold, but all nested child activities started within the called routine are under this threshold's control. Anonymous blocks and autonomous routines are classified as CALL statements.
User-defined functions are controlled by this threshold, but child activities nested in a user-defined function are not controlled. If an autonomous routine is called from within a user defined function, neither the autonomous routine nor any child activities of the autonomous routine are under threshold control.
Trigger actions that invoke CALL statements and the child activities of these CALL statements are not controlled by this threshold. INSERT, UPDATE, or DELETE statements that can cause a trigger to activate continue to be under threshold control.
To manage concurrency with a CALL statement, you may be able to use the TOTALSCPARTITIONCONNECTIONS threshold. The TOTALSCPARTITIONCONNECTIONS threshold is effective for controlling concurrency of CALL statements when your workload consists of transient connections. Transient connections are connections that are established only during the procedure invocation. The TOTALSCPARTITIONCONNECTIONS threshold is not appropriate if your workload consists of long-lived connections.

When a threshold is defined as part of a work action set, the enforcement scope is determined automatically based on the current environment (MEMBER, if the current environment is Db2 pureScale; DATABASE, if it is otherwise).

Important: Before using CONCURRENTDBCOORDACTIVITIES thresholds, be sure to become familiar with the effects that they can have on the database system.

For more information, refer to CONCURRENTDBCOORDACTIVITIES threshold .

AND QUEUEDACTIVITIES > integer-value or AND QUEUEDACTIVITIES UNBOUNDED: Specifies a queue size for when the maximum number of database coordinator activities is exceeded. This value can be zero or any positive integer (SQLSTATE 42820). A value of zero means that no database coordinator activities are queued. Specifying UNBOUNDED will queue every database coordinator activity that exceeds the specified maximum number of database coordinator activities, and the threshold-exceeded-actions will never be executed. The default is zero.
Note: If a threshold action of CONTINUE is specified for a queuing threshold, it effectively makes the size of the queue unbounded, regardless of any hard value specified for the queue size.

ESTIMATEDSQLCOST > bigint-value

This condition defines an upper bound for the optimizer-assigned cost (in timerons) of an activity. This value can be any positive big integer (not zero) (SQLSTATE 42820). The definition domain for this condition must be DATABASE, work action (a threshold for a work action definition domain is created using a CREATE WORK ACTION SET or ALTER WORK ACTION SET statement, and the work action set must be applied to a workload or a database), SERVICE SUPERCLASS, SERVICE SUBCLASS, or WORKLOAD, and the enforcement scope must be DATABASE (SQLSTATE 5U037). This condition is enforced at the coordinator member. Activities tracked by this condition are:

Coordinator activities of type data manipulation language (DML).
Nested DML activities that are invoked from user logic. Consequently, DML activities that can be initiated by the database manager through internal SQL are not tracked by this condition (unless their cost is included in the parent's estimate, in which case they are indirectly tracked).

SQLROWSRETURNED > integer-value

This condition defines an upper bound for the number of rows returned to a client application from the application server. This value can be any positive integer (not zero) (SQLSTATE 42820). The definition domain for this condition must be DATABASE, work action (a threshold for a work action definition domain is created using a CREATE WORK ACTION SET or ALTER WORK ACTION SET statement, and the work action set must be applied to a workload or a database), SERVICE SUPERCLASS, SERVICE SUBCLASS, or WORKLOAD, and the enforcement scope must be DATABASE (SQLSTATE 5U037). This condition is enforced at the coordinator member. Activities tracked by this condition are:

Coordinator activities of type DML.
Nested DML activities that are derived from user logic. Activities that are initiated by the database manager through internal SQL are not affected by this condition.

Result sets returned from within a procedure are treated separately as individual activities. There is no aggregation of the rows that are returned by the procedure itself.

This condition defines an upper bound for the amount of time the database manager will allow an activity to execute, including the time the activity was queued. The definition domain for this condition must be DATABASE, work action (a threshold for a work action definition domain is created using a CREATE WORK ACTION SET or ALTER WORK ACTION SET statement, and the work action set must be applied to a workload or a database), SERVICE SUPERCLASS, SERVICE SUBCLASS, or WORKLOAD, and the enforcement scope must be DATABASE (SQLSTATE 5U037). This condition is logically enforced at the coordinator member.

The specified integer-value must be an integer that is greater than zero (SQLSTATE 42820). Use a valid duration keyword to specify an appropriate unit of time for integer-value. If the specified time unit is SECONDS, the value must be a multiple of 10 (SQLSTATE 42615). The maximum value that can be specified for this threshold is 2 147 483 640 seconds. If any value (using the DAY, HOUR, MINUTE, or SECONDS time unit) has a seconds equivalent larger than the maximum value, an error is returned (SQLSTATE 42615).

This condition defines an upper bound for the amount of time the database manager will allow a unit of work to execute. This value can be any non-zero positive integer (SQLSTATE 42820). Use a valid duration keyword to specify an appropriate unit of time for integer-value. If the specified time unit is SECONDS, the value must be a multiple of 10 (SQLSTATE 42615). The definition domain for this condition must be DATABASE, SERVICE SUPERCLASS, or WORKLOAD, and the enforcement scope must be DATABASE (SQLSTATE 5U037). This condition is enforced at the coordinator member.

The maximum value that can be specified for this threshold is 2 147 483 640 seconds. If any value (using the DAY, HOUR, MINUTE, or SECONDS time unit) has a seconds equivalent larger than the maximum value, an error is returned (SQLSTATE 42615).

SQLTEMPSPACE > integer-value K | M | G

This condition defines the maximum amount of system temporary space that can be consumed by an SQL statement on a member. This value can be any positive integer (not zero) (SQLSTATE 42820).

If integer-value K (in either upper- or lowercase) is specified, the maximum size is 1024 times integer-value. If integer-value M is specified, the maximum size is 1 048 576 times integer-value. If integer-value G is specified, the maximum size is 1 073 741 824 times integer-value.

The definition domain for this condition must be DATABASE, work action (a threshold for a work action definition domain is created using a CREATE WORK ACTION SET or ALTER WORK ACTION SET statement, and the work action set must be applied to a workload or a database), SERVICE SUPERCLASS, SERVICE SUBCLASS, or WORKLOAD, and the enforcement scope must be MEMBER (SQLSTATE 5U037). Activities tracked by this condition are:

Coordinator activities of type DML and corresponding subagent work (subsection execution).
Nested DML activities that are derived from user logic and their corresponding subagent work (subsection execution). Activities that are initiated by the database manager through an internal SQL are not affected by this condition.

AGGSQLTEMPSPACE > integer-value K | M | G

This condition defines the maximum amount of system temporary space that can be consumed by a set of statements in a service class on a member. This value can be any positive integer (not zero) (SQLSTATE 42820).

If integer-value K (in either upper- or lowercase) is specified, the maximum size is 1024 times integer-value. If integer-value M is specified, the maximum size is 1 048 576 times integer-value. If integer-value G is specified, the maximum size is 1 073 741 824 times integer-value.

The definition domain for this condition must be SERVICE SUBCLASS and the enforcement scope must be MEMBER (SQLSTATE 5U037).

Activities contributing to the aggregate that is tracked by this condition are:

Coordinator activities of type DML and corresponding subagent work like subsection execution.
Nested DML activities that are derived from user logic and their corresponding subagent work like subsection execution. Activities initiated by the database manager through an internal SQL statement are not affected by this condition.

SQLROWSREAD > bigint-value

This condition defines an upper bound on the number of rows that may be read by an activity during its lifetime on a particular member. This value can be any positive big integer (not zero) (SQLSTATE 42820). Note that the number of rows read is different from the number of rows returned, which is controlled by the SQLROWSRETURNED condition.

The definition domain for this condition must be DATABASE, SERVICE CLASS, a service subclass (SERVICE CLASS specifying the UNDER clause), WORKLOAD or a work action (a threshold for a work action definition domain is created using a CREATE WORK ACTION SET or ALTER WORK ACTION SET statement, and the work action set must be applied to a workload or a database), and the enforcement scope must be MEMBER (SQLSTATE 5U037). This condition is enforced independently at each member.

Activities tracked by this condition are:

Coordinator activities of type DML and corresponding subagent work (like subsection execution).
Internal SQL activities like those initiated by the setting of a constraint, or the refreshing of a materialized query table, are also not tracked by this threshold, because they are initiated by the database manager and not directly invoked by user logic.

CHECKING EVERY integer-value SECOND | SECONDS: Specifies how frequently the threshold condition is checked for an activity. The threshold is checked on the interval defined by the CHECKING clause. The CHECKING clause defines an upper bound on how long a threshold violation may go undetected. The default is 60 seconds. The value can be any positive integer (not zero) with a maximum value of 86400 seconds (SQLSTATE 42820). Setting a low value may impact system performance negatively.

SQLROWSREADINSC > bigint-value

This condition defines an upper bound on the number of rows that may be read by an activity on a particular member while it is executing in a service subclass. Rows read before executing in the service subclass specified are not counted. This value can be any positive big integer (not zero) (SQLSTATE 42820). Note that the number of rows read is different from the number of rows returned, which is controlled by the SQLROWSRETURNED condition.

The definition domain for this condition must be a service subclass (SERVICE CLASS specifying the UNDER clause) and the enforcement scope must be MEMBER (SQLSTATE 5U037). This condition is enforced independently at each member.

Activities tracked by this condition are:

Coordinator activities of type DML and corresponding subagent work (like subsection execution).
Internal SQL activities like those initiated by the setting of a constraint, or the refreshing of a materialized query table, are also not tracked by this threshold, because they are initiated by the database manager and not directly invoked by user logic.

CHECKING EVERY integer-value SECOND | SECONDS: Specifies how frequently the threshold condition is checked for an activity. The threshold is checked on the interval defined by the CHECKING clause. The CHECKING clause defines an upper bound on how long a threshold violation may go undetected. The default is 60 seconds. The value can be any positive integer (not zero) with a maximum value of 86400 seconds (SQLSTATE 42820).Setting a low value may impact system performance negatively.

This condition defines an upper bound for the amount of processor time that an activity may consume during its lifetime on a particular member. The processor time tracked by this threshold is measured from the time that the activity starts executing. This value can be any positive integer (not zero) (SQLSTATE 42820).

The definition domain for this condition must be DATABASE, a service superclass (SERVICE CLASS), a service subclass (SERVICE CLASS specifying the UNDER clause), WORKLOAD or work action (a threshold for a work action definition domain is created using a CREATE WORK ACTION SET or ALTER WORK ACTION SET statement, and the work action set must be applied to a workload or a database), and the enforcement scope must be MEMBER (SQLSTATE 5U037). This condition is enforced independently at each member.

Activities tracked by this condition are:

Coordinator activities of type DML and corresponding subagent work (like subsection execution).
Internal SQL activities, like those initiated by the setting of a constraint or the refreshing of a materialized query table, are also not tracked by this threshold, because they are initiated by the database manager and not directly invoked by user logic.
Activities of type CALL. For CALL activities, the processor time tracked for the procedure does not include the processor time used by any child activities or by any fenced mode processes. The threshold condition will be checked only upon return from user logic to the database engine. For example: During the execution of a trusted routine, the threshold condition will be checked only when the routine issues a request to the database engine).

CHECKING EVERY integer-value SECOND | SECONDS: Specifies how frequently the threshold condition is checked for an activity. The granularity of the CPUTIME threshold is approximately this number multiplied by the degree of parallelism for the activity. For example: If the threshold is checked every 60 seconds and the degree of parallelism is 2, the activity might use an extra 2 minutes of processor time instead of 1 minute before the threshold violation is detected. The default is 60 seconds. The value can be any positive integer (not zero) with a maximum value of 86400 seconds (SQLSTATE 42820). Setting a low value may impact system performance negatively.

This condition defines an upper bound for the amount of processor time that an activity may consume on a particular member while it is executing in a particular service subclass. The processor time tracked by this threshold is measured from the time that the activity starts executing in the service subclass identified in the threshold domain. Any processor time used before that point is not counted toward the limit imposed by this threshold. This value can be any positive integer (not zero) (SQLSTATE 42820).

The definition domain for this condition must be a service subclass (SERVICE CLASS specifying the UNDER clause), and the enforcement scope must be MEMBER (SQLSTATE 5U037). This condition is enforced independently at each member.

Activities tracked by this condition are:

Coordinator activities of type DML and corresponding subagent work (like subsection execution).
Internal SQL activities, like those initiated by the setting of a constraint or the refreshing of a materialized query table, are also not tracked by this threshold, because they are initiated by the database manager and not directly invoked by user logic.
Activities of type CALL. For CALL activities, the processor time tracked for the procedure does not include the processor time used by any child activities or by any fenced mode processes. The threshold condition will be checked only upon return from user logic to the database engine. For example: During the execution of a trusted routine, the threshold condition will be checked only when the routine issues a request to the database engine).

CHECKING EVERY integer-value SECOND | SECONDS: Specifies how frequently the threshold condition is checked for an activity. The granularity of the CPUTIMEINSC threshold is approximately this number multiplied by the degree of parallelism for the activity. For example: If the threshold is checked every 60 seconds and the degree of parallelism is 2, the activity might use an extra 2 minutes of processor time instead of 1 minute before the threshold violation is detected. The default is 60 seconds. The value can be any positive integer (not zero) with a maximum value of 86400 seconds (SQLSTATE 42820). Setting a low value may impact system performance negatively.

This condition is used to define an upper bound for the amount of time the database manager allows an activity to run. The amount of time does not include the time that the activity was queued by a WLM concurrency threshold. The definition domain for this condition must be one of the following thresholds (SQLSTATE 5U037):

Database
Service superclass
Service subclass
Statement
Workload
Work action ¹
1. A threshold for a work action definition domain is created by using a CREATE WORK ACTION SET or ALTER WORK ACTION SET statement. The work action set must be applied to a workload or a database.

The enforcement scope must be DATABASE (SQLSTATE 5U037).

The specified integer-value must be an integer that is greater than zero (SQLSTATE 42820). Use a valid duration keyword to specify an appropriate unit of time for integer-value. If the specified time unit is SECONDS, the value must be a multiple of 10 (SQLSTATE 42615). The maximum value that can be specified for this threshold is 2 147 483 640 seconds. If any value for the DAY, HOUR, MINUTE, or SECONDS time unit has a seconds equivalent larger than the maximum value, an error is returned (SQLSTATE 42615).

This condition is used to define an upper bound for the amount of time the database manager allows an activity to run. The amount of time does not include the time that the activity was queued by a WLM concurrency threshold. The execution time that is tracked by this threshold is measured from the time that the activity starts running.

The definition domain for this condition must be service subclass (SERVICE CLASS specifying the UNDER clause), and the enforcement scope must be DATABASE (SQLSTATE 5U037).

The specified integer-value must be an integer that is greater than zero (SQLSTATE 42820). Use a valid duration keyword to specify an appropriate unit of time for integer-value. If the specified time unit is SECONDS, the value must be a multiple of 10 (SQLSTATE 42615). The maximum value that can be specified for this threshold is 2 147 483 640 seconds. If any value for the DAY, HOUR, MINUTE, or SECONDS time unit has a seconds equivalent larger than the maximum value, an error is returned (SQLSTATE 42615).

SORTSHRHEAPUTIL > integer-value PERCENT

Attention: This feature is available in Db2 Version 11.5 Mod Pack 2 and later versions.

This condition defines the maximum shared sort memory that may be requested by a query as a percentage of the total database shared sort memory (sheapthres_shr). When the adaptive workload manager is enabled, the threshold considers both estimated and actual memory requirements for a query. Any positive integer between 1 to 100 can be specified as a percent value. The execution time that is tracked by this threshold is measured from the time that the activity starts running.

The definition domain for this condition must be DATABASE, work action (a threshold for a work action definition domain is created using a CREATE WORK ACTION SET or ALTER WORK ACTION SET statement, and the work action set must be applied to a workload or a database), SERVICE SUPERCLASS, SERVICE SUBCLASS, STATEMENT or WORKLOAD, and the enforcement scope must be MEMBER (SQLSTATE 5U037).

Activities tracked by this condition are:

Coordinator activities of type data manipulation language (DML).
Nested DML activities that are directly invoked from user logic.

AND BLOCKING ADMISSION FOR integer-value: Specifies that action will only be taken if the sort memory requirements are exceeded, work is currently queued behind the violating activity, the queued work is blocking on sort memory and WLM admission control has not admitted any requests for the specified amount of time. For work inside the WLM admission queue, this condition will only be evaluated once a request reaches the front of the admission queue. Every time a request is allowed by admission control, the queue time will be resset. If multiple requests violate this threshold a cascading effect will be observed until something that doesn't violate this threshold is found or the last request is reached (as in, no other requests behind).; The maximum value for this threshold is 2147483640 seconds. Any value specified that has a seconds equivalent larger than 2147483640 seconds will be set to this number of seconds. The time specified has a minimum accuracy of 10 seconds, so any value specified is subject to accuracy of this amount. A value of zero is equivalent to not specifying a BLOCKING ADMISSION FOR clause.

DATATAGINSC IN integer-constant, )

This condition defines one or more data tag values specified on a table space that the activity touches. The data tag on a table space, or its underlying storage group (where applicable), can be either not be set or set to a value from 1 to 9. If the activity touches a table space that has no data tag set (at either the table space or the storage group level), this threshold will not have any affect on that activity. The definition domain for this condition must be a service subclass (SERVICE CLASS specifying the UNDER clause), and the enforcement scope must be DATABASE PARTITION (SQLSTATE 5U037). This condition is enforced independently at each database partition.

Activities tracked by this condition are:

Coordinator activities of type data manipulation language (DML).
Nested DML activities that are directly invoked from user logic.

DML activities that can be initiated by the database manager through internal SQL are not tracked by this condition.

This threshold is checked only when a scan is opened on a table or when an insert is performed into a table. Fetching data from a table after a scan has been opened will not violate the threshold.

DATATAGINSC NOT IN integer-constant, )

This condition defines one or more data tag values not specified on a table space that the activity touches. The data tag on a table space, or its underlying storage group (where applicable), can be either not be set or set to a value from 1 to 9. If the activity touches a table space that has no data tag set (either at the table space or the storage group level), this threshold will not have any affect on that activity. The definition domain for this condition must be a service subclass (SERVICE CLASS specifying the UNDER clause) and the enforcement scope must be DATABASE PARTITION (SQLSTATE 5U037). This condition is enforced independently at each database partition.

Activities tracked by this condition are:

Coordinator activities of type data manipulation language (DML).
Nested DML activities that are directly invoked from user logic.

DML activities that can be initiated by the database manager through internal SQL are not tracked by this condition.

This threshold is checked only when a scan is opened on a table or when an insert is performed into a table. Fetching data from a table after a scan has been opened will not violate the threshold.

threshold-exceeded-actions

Specifies what action is to be taken when a condition is exceeded. Each time that a condition is exceeded, an event is recorded in the threshold violations event monitor, if one is active.

COLLECT ACTIVITY DATA

Specifies that data about each activity that exceeded the threshold is to be sent to any active activities event monitor, when the activity completes. The default is COLLECT ACTIVITY DATA NONE. If COLLECT ACTIVITY DATA is specified, the default is WITHOUT DETAILS. The COLLECT ACTIVITY DATA setting does not apply to non-activity thresholds, such as the following: CONNECTIONIDLETIME, TOTALDBPARTITIONCONNECTIONS, TOTALSCPARTITIONCONNECTIONS, CONCURRENTWORKLOADOCCURRENCES, UOWTOTALTIME.

NONE

Specifies that activity data should not be collected for each activity that exceeds the threshold.

ON COORDINATOR MEMBER

Specifies that the activity data is to be collected only at the coordinator member of the activity.

ON ALL MEMBERS

Specifies that the activity data is to be collected at all members on which the activity is processed. On remote members, a record for the activity may be captured multiple times as the activity comes and goes on those members. For predictive thresholds, activity information is collected at all members only if you also specify the CONTINUE action for exceeded thresholds. For reactive thresholds, the ON ALL MEMBERS clause has no effect and activity information is always collected only at the coordinator member. For both predictive and reactive thresholds, any activity details, section information, or values will be collected only at the coordinator member.

WITHOUT DETAILS

Specifies that data about each activity associated with the work class for which this work action is defined is to be sent to any active activities event monitor, when the activity completes execution. Details about statement, compilation environment, and section environment data are not sent.

WITH

DETAILS: Specifies that statement and compilation environment data is to be sent to any active activities event monitor, for those activities that have them. Section environment data is not sent.

SECTION: Specifies that statement, compilation environment, section environment data, and section actuals are to be sent to any active activities event monitor for those activities that have them. DETAILS must be specified if SECTION is specified. For predictive thresholds, section actuals will be collected on any member where the activity data is collected. For reactive thresholds, section actuals will be collected only on the coordinator member.
AND VALUES: Specifies that input data values are to be sent to any active activities event monitor, for those activities that have them. This data does not include SQL statements that are compiled by using the REOPT ALWAYS bind option.

STOP EXECUTION

The execution of the activity is stopped and an error is returned (SQLSTATE 5U026). In the case of the UOWTOTALTIME threshold, the unit of work is rolled back.

CONTINUE

The execution of the activity is not stopped.

FORCE APPLICATION

The application is forced off the system (SQLSTATE 55032). This action can only be specified for the UOWTOTALTIME threshold.

remap-activity-action

REMAP ACTIVITY TO service-subclass-name: The activity is mapped to service-subclass-name. The execution of the activity is not stopped. This action is valid only for in-service-class and in-all-service-class thresholds like CPUTIMEINSC, SQLROWSREADINSC, DATATAGINSC IN and DATATAGINSC NOT IN and ACTIVITYTOTALRUNTIMEINALLSC thresholds (SQLSTATE 5U037). The service-subclass-name must identify an existing service subclass under the same superclass associated with the threshold (SQLSTATE 5U037). The service-subclass-name cannot be the same as the associated service subclass of the threshold (SQLSTATE 5U037).
NO EVENT MONITOR RECORD: Specifies that no threshold violation record will be written.
LOG EVENT MONITOR RECORD: Specifies that if a THRESHOLD VIOLATIONS event monitor exists and is active, a threshold violation record is written to it.

Notes

Thresholds can be defined on different aspects of database behavior to monitor and control that behavior. When a threshold is defined on activities, unless otherwise specified, it will be enforced only during the actual execution of SQL statements, not including compilation time, and the load utility.
The CONCURRENTWORKLOADOCCURRENCES threshold and the CONCURRENTWORKLOADACTIVITIES threshold differ in scope. CONCURRENTWORKLOADOCCURRENCES controls how many connections can map to a workload definition simultaneously, and CONCURRENTWORKLOADACTIVITIES controls how many activities each connection that is mapped to the workload definition can submit concurrently.
Changes are written to the system catalog, but do not take effect until after a COMMIT statement, even for the connection that issues the statement.
Only one uncommitted WLM-exclusive SQL statement at a time is allowed across all partitions. If an uncommitted WLM-exclusive SQL statement is executing, subsequent WLM-exclusive SQL statements will wait until the current WLM-exclusive SQL statement commits or rolls back.
Threshold exceeded action of CONTINUE and event monitor data: Event monitor data is collected only once per member when a threshold condition has been exceeded. If the threshold exceeded action is CONTINUE, the activity continues executing and no further event monitor data is collected for that threshold at the affected member. For example, consider a time threshold of 10 minutes with an action of CONTINUE. After an activity exceeds the 10-minute upper bound, event monitor data is collected for the threshold at the affected member.
Quiescing a service class: The TOTALSCPARTITIONCONNECTIONS threshold condition can be used to simulate quiescing service classes that cannot normally be quiesced (for example, the default user class, or the default system class). This is useful, because thresholds do not apply to users with DBADM authority running in the SYSDEFAULTADMWORKLOAD, whereas a quiesced service class is not available to anyone. Consequently, default service classes cannot be quiesced directly but only through a threshold that allows users with DBADM authority to join them when connected to the database using the SYSDEFAULTADMWORKLOAD.
Syntax alternatives: The following parameters are supported for compatibility with Db2 11.1 and earlier, and with other database products.
Attention:
- These alternatives are non-standard and should not be used.
- The DB2_ENFORCE_MEMBER_SYNTAX registry variable is intended for Db2 pureScale environments only.
- DATABASE PARTITION can be specified in place of MEMBER, except when the DB2_ENFORCE_MEMBER_SYNTAX registry variable is set to ON.
- DATABASE PARTITIONS can be specified in place of MEMBERS, except when the DB2_ENFORCE_MEMBER_SYNTAX registry variable is set to ON.
- TOTALDBPARTITIONCONNECTIONS can be specified in place of TOTALMEMBERCONNECTIONS, except when the DB2_ENFORCE_MEMBER_SYNTAX registry variable is set to ON.
- TOTALSCPARTITIONCONNECTIONS can be specified in place of TOTALSCMEMBERCONNECTIONS, except when the DB2_ENFORCE_MEMBER_SYNTAX registry variable is set to ON.

Examples

Example 1: Create a threshold that enforces a maximum temporary table space usage of 50M (per database partition) to any activity in the database. Any activity that violates this threshold is to be stopped.
```
   CREATE THRESHOLD DBMAX50MEGTEMPSPACE
     FOR DATABASE ACTIVITIES
     ENFORCEMENT MEMBER
     WHEN SQLTEMPSPACE > 50 M
     STOP EXECUTION
```
Example 2: Create a second threshold to limit the default runtime of any activity in the database to a maximum of 1 hour. Any activity that violates this threshold is to be stopped.
```
   CREATE THRESHOLD DBMAX1HOURRUNTIME
     FOR DATABASE
     WHEN ACTIVITYTOTALTIME > 1 HOUR
     STOP EXECUTION
```

Example 3: Assume that a service superclass named BIGQUERIES was created to host queries using more temporary space than average and running longer than 1 hour. The thresholds defined inside this service class will override the values that were set in the previous example at the database level. Note how activities violating the thresholds inside this superclass are allowed to continue executing, but detailed information is collected for further analysis.

   CREATE THRESHOLD BIGQUERIESMAX500MEGTEMPSPACE
     FOR SERVICE CLASS BIGQUERIES ACTIVITIES
     ENFORCEMENT DATABASE MEMBER
     WHEN SQLTEMPSPACE  > 500 M
     COLLECT ACTIVITY DATA WITH DETAILS AND VALUES
     CONTINUE

   CREATE THRESHOLD BIGQUERIESLONGRUNNINGTIME
     FOR SERVICE CLASS BIGQUERIES ACTIVITIES
     ENFORCEMENT DATABASE
     WHEN ACTIVITYTOTALTIME   > 10 HOURS
     COLLECT ACTIVITY DATA WITH DETAILS AND VALUES
     CONTINUE

Example 4: Assuming the existence of a workload named PAYROLL, create a threshold that enforces the maximum number of activities within the workload to be less than or equal to 10.

   CREATE THRESHOLD MAXACTIVITIESINPAYROLL
     FOR WORKLOAD PAYROLL ACTIVITIES
     ENFORCEMENT WORKLOAD OCCURRENCE
     WHEN CONCURRENTWORKLOADACTIVITIES > 10
     STOP EXECUTION

Example 5: Create a threshold that enforces a maximum concurrency of 2 activities in the service class BIGQUERIES.

   CREATE THRESHOLD MAXBIGQUERIESCONCURRENCY
     FOR SERVICE CLASS BIGQUERIES  ACTIVITIES
     ENFORCEMENT DATABASE
     WHEN CONCURRENTDBCOORDACTIVITIES > 2
     STOP EXECUTION

Example 6: Create a threshold that captures activity information for a specific statement that runs for longer than one minute, but do not cease statement execution.

   CREATE THRESHOLD TH1 
     FOR STATEMENT 
     TEXT 'SELECT DISTINCT PARTS_BIN FROM STOCK WHERE PART_NUMBER = ?'
     ACTIVITIES ENFORCEMENT DATABASE   
     WHEN ACTIVITYTOTALTIME > 1 MINUTE   
     COLLECT ACTIVITY DATA WITH DETAILS, SECTION AND VALUES 
     CONTINUE

CREATE THRESHOLD statement

Invocation

Authorization

Syntax

Description

Notes

Examples