DB2 10.5 for Linux, UNIX, and Windows

ALTER THRESHOLD statement

The ALTER THRESHOLD statement alters the definition of 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 at least one of the following authorities:
  • SQLADM authority, only if every alteration clause is a COLLECT clause
  • WLMADM authority
  • DBADM authority

Syntax

Read syntax diagramSkip visual syntax diagram
>>-ALTER THRESHOLD--threshold-name------------------------------>

   .-------------------------------------------------------------------------------------------.   
   V  (1)                                                                                      |   
>--------+-WHEN--+-| alter-threshold-predicate |--+-PERFORM ACTION-----------------------+-+-+-+-><
         |       |                                '-| alter-threshold-exceeded-actions |-' | |     
         |       '-EXCEEDED--| alter-threshold-exceeded-actions |--------------------------' |     
         '-+-ENABLE--+-----------------------------------------------------------------------'     
           '-DISABLE-'                                                                             

alter-threshold-predicate

|--+-TOTALMEMBERCONNECTIONS-->--integer-value---------------------------------------------------+--|
   +-TOTALSCMEMBERCONNECTIONS-->--integer-value--+-----------------------------------------+----+   
   |                                             +-AND QUEUEDCONNECTIONS-->--integer-value-+    |   
   |                                             '-AND QUEUEDCONNECTIONS UNBOUNDED---------'    |   
   +-CONNECTIONIDLETIME-->--integer-value--+-DAY-----+------------------------------------------+   
   |                                       +-DAYS----+                                          |   
   |                                       +-HOUR----+                                          |   
   |                                       +-HOURS---+                                          |   
   |                                       +-MINUTE--+                                          |   
   |                                       '-MINUTES-'                                          |   
   +-CONCURRENTWORKLOADOCCURRENCES-->--integer-value--------------------------------------------+   
   +-CONCURRENTWORKLOADACTIVITIES-->--integer-value---------------------------------------------+   
   +-CONCURRENTDBCOORDACTIVITIES-->--integer-value--+----------------------------------------+--+   
   |                                                +-AND QUEUEDACTIVITIES-->--integer-value-+  |   
   |                                                '-AND QUEUEDACTIVITIES UNBOUNDED---------'  |   
   +-ESTIMATEDSQLCOST-->--bigint-value----------------------------------------------------------+   
   +-SQLROWSRETURNED-->--integer-value----------------------------------------------------------+   
   +-ACTIVITYTOTALTIME-->--integer-value--+-DAY-----+-------------------------------------------+   
   |                                      +-DAYS----+                                           |   
   |                                      +-HOUR----+                                           |   
   |                                      +-HOURS---+                                           |   
   |                                      +-MINUTE--+                                           |   
   |                                      +-MINUTES-+                                           |   
   |                                      '-SECONDS-'                                           |   
   +-UOWTOTALTIME-->--integer-value--+-DAY-----+------------------------------------------------+   
   |                                 +-DAYS----+                                                |   
   |                                 +-HOUR----+                                                |   
   |                                 +-HOURS---+                                                |   
   |                                 +-MINUTE--+                                                |   
   |                                 +-MINUTES-+                                                |   
   |                                 '-SECONDS-'                                                |   
   +-SQLTEMPSPACE-->--integer-value--+-K-+------------------------------------------------------+   
   |                                 +-M-+                                                      |   
   |                                 '-G-'                                                      |   
   +-AGGSQLTEMPSPACE-->--integer-value--+-K-+---------------------------------------------------+   
   |                                    +-M-+                                                   |   
   |                                    '-G-'                                                   |   
   +-SQLROWSREAD-->--bigint-value--+--------------------------------------------+---------------+   
   |                               '-CHECKING EVERY--integer-value--+-SECOND--+-'               |   
   |                                                                '-SECONDS-'                 |   
   +-SQLROWSREADINSC-->--bigint-value--+--------------------------------------------+-----------+   
   |                                   '-CHECKING EVERY--integer-value--+-SECOND--+-'           |   
   |                                                                    '-SECONDS-'             |   
   +-CPUTIME-->--integer-value--+-HOUR----+--+--------------------------------------------+-----+   
   |                            +-HOURS---+  '-CHECKING EVERY--integer-value--+-SECOND--+-'     |   
   |                            +-MINUTE--+                                   '-SECONDS-'       |   
   |                            +-MINUTES-+                                                     |   
   |                            +-SECOND--+                                                     |   
   |                            '-SECONDS-'                                                     |   
   +-CPUTIMEINSC-->--integer-value--+-HOUR----+--+--------------------------------------------+-+   
   |                                +-HOURS---+  '-CHECKING EVERY--integer-value--+-SECOND--+-' |   
   |                                +-MINUTE--+                                   '-SECONDS-'   |   
   |                                +-MINUTES-+                                                 |   
   |                                +-SECOND--+                                                 |   
   |                                '-SECONDS-'                                                 |   
   |                                   .-,----------------.                                     |   
   |  (2)                              V                  |                                     |   
   '-------DATATAGINSC--+-IN-----+--(----integer-constant-+--)----------------------------------'   
                        '-NOT IN-'                                                                  

alter-threshold-exceeded-actions

   .---------------------------------------------------------------------------.   
   V  (1)                                                                      |   
|--------+-COLLECT ACTIVITY DATA--+-| alter-collect-activity-data-clause |-+-+-+--|
         |                        '-NONE-----------------------------------' |     
         '-+-STOP EXECUTION------------+-------------------------------------'     
           +-CONTINUE------------------+                                           
           +-FORCE APPLICATION---------+                                           
           '-| remap-activity-action |-'                                           

alter-collect-activity-data-clause

                     .-MEMBER-.     
|--+-ON COORDINATOR--+--------+-+------------------------------->
   |         .-MEMBERS-.        |   
   '-ON ALL--+---------+--------'   

>--+-WITHOUT DETAILS---------------------------+----------------|
   |       .-,---------------.                 |   
   |       V           (3)   |                 |   
   '-WITH----+-DETAILS-----+-+--+------------+-'   
             '-SECTION-----'    '-AND VALUES-'     

remap-activity-action

|--REMAP ACTIVITY TO--service-subclass-name--------------------->

   .-NO EVENT MONITOR RECORD--.   
>--+--------------------------+---------------------------------|
   '-LOG EVENT MONITOR RECORD-'   

Notes:
  1. The same clause must not be specified more than once.
  2. Each data tag value can be specified only once.
  3. The DETAILS keyword is the minimum to be specified, followed by the option separated by a comma.

Description

threshold-name
Identifies the threshold to be altered. This is a one-part name. It is an SQL identifier (either ordinary or delimited). The name must uniquely identify an existing threshold at the current server (SQLSTATE 42704).
WHEN alter-threshold-predicate or WHEN EXCEEDED
Replaces the existing upper bound value in the threshold predicate condition with a new upper bound value. The condition of the threshold cannot be changed to a different one.
PERFORM ACTION
When altering the value of the threshold predicate condition, specifies that the threshold exceeded action is not changed.
EXCEEDED
Specifies to keep the same threshold predicate that was specified originally for this altered threshold.
alter-threshold-predicate
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.
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.
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 zero or any positive integer (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.
CONNECTIONIDLETIME > integer-value DAY | DAYS | HOUR | HOURS | MINUTE | MINUTES
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. 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 since the application is no longer connected to the data server.

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).
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).
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.

Consequently, nested coordinator activities that were automatically started under the invocation of a database manager utility or routines in the SYSIBM, SYSFUN, or SYSPROC schemas are not counted toward the upper bound specified by this threshold.

Internal SQL activities, such as those generated 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. 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.
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 ../../com.ibm.db2.luw.admin.wlm.doc/doc/r0051917.html .

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.
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). 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 (such as utilities, procedures, or 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).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 a utility, procedure, or 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.
ACTIVITYTOTALTIME > integer-value DAY | DAYS | HOUR | HOURS | MINUTE | MINUTES | SECONDS
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).

UOWTOTALTIME > integer-value DAY | DAYS | HOUR | HOURS | MINUTE | MINUTES | SECONDS
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). 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.

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 a utility, procedure, or 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.

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 a utility, procedure, or 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.

Activities 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 that are initiated by the database manager through a utility or procedure (with the exception of the ADMIN_CMD procedure) are not counted for this condition.
  • 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 at the end of each request (like a fetch operation, for example) and 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 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.

Activities 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 that are initiated by the database manager through a utility or procedure (with the exception of the ADMIN_CMD procedure) are not counted for this condition.
  • 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 at the end of each request (like a fetch operation, for example) and 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 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.
CPUTIME > integer-value DAY | DAYS | HOUR | HOURS | MINUTE | MINUTES | SECOND | SECONDS
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).

Activities 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 that are initiated by the database manager through a utility or procedure (with the exception of the ADMIN_CMD procedure) are not counted for this condition.
  • 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 activity 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 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 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.
CPUTIMEINSC > integer-value DAY | DAYS | HOUR | HOURS | MINUTE | MINUTES | SECOND | SECONDS
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 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).

Activities 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 that are initiated by the database manager through a utility or procedure (with the exception of the ADMIN_CMD procedure) are not counted for this condition.
  • 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 activity 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 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 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.
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 (either at the table space or 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 (such as utilities, procedures, or internal SQL) are not tracked by this condition.

This threshold is only checked 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 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 (such as utilities, procedures, or internal SQL) are not tracked by this condition.

This threshold is only checked 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.

alter-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 all active threshold violations event monitors.
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 COLLECT ACTIVITY DATA setting does not apply to non-activity thresholds, such as CONNECTIONIDLETIME, TOTALDBPARTITIONCONNECTIONS, TOTALSCPARTITIONCONNECTIONS, CONCURRENTWORKLOADOCCURRENCES, or UOWTOTALTIME.
alter-collect-activity-data-clause
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 input data values, 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 should 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.
NONE
Specifies that activity data should not be collected for each activity that exceeds the threshold.
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. When the condition also has a queue, this option causes queuing to extend beyond the size of the queue.
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 thresholds like CPUTIMEINSC, SQLROWSREADINSC, DATATAGINSC IN and DATATAGINSC NOT IN 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.
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. Currently running database activities will continue to execute without the restriction of this threshold.
DISABLE
The threshold is not used by the database manager to restrict the execution of database activities. New database activities will not be restricted by this threshold. Thresholds with a queue, for example TOTALSCMEMBERCONNECTIONS or CONCURRENTDBCOORDACTIVITIES, must be disabled before they can be dropped.

Notes

Example

Alter the threshold MAXBIGQUERIESCONCURRENCY to a maximum of three activities rather than two.
   ALTER THRESHOLD MAXBIGQUERIESCONCURRENCY
     WHEN CONCURRENTDBCOORDACTIVITIES > 3
     STOP EXECUTION
Because this is a threshold with a queue, the threshold cannot be dropped unless it is disabled, as follows:
   ALTER THRESHOLD MAXBIGQUERIESCONCURRENCY DISABLE