ADD_QUERY_THRESHOLD procedure

Edit online

The ADD_QUERY_THRESHOLD procedure defines a new threshold to be used by the Query Supervisor. Adding a threshold only affects subsequently executed queries. Queries that are currently running continue to use the thresholds that were in effect when the query execution was initiated. The Query Supervisor only supervises user jobs. It does not affect system jobs or queries run by the operating system. The thresholds only apply to resources used by the SQL Query Engine (SQE).

Query Supervisor thresholds can include using job name, user name, and subsystem filters. The filters are combined to restrict the set of jobs for the rule. For jobs that meet the filtering criteria, when the threshold is met or exceeded, any exit programs registered with the QIBM_QQQ_QRY_SUPER exit point are called. An exit program can be used to implement actions such as query logging or real-time notification. It can also direct SQE to terminate the query. The exit program interface is described here: Query Supervisor Exit Program.

When the first threshold is defined for a system, any active jobs, including the job that runs the initial ADD_QUERY_THRESHOLD, will not be affected by Query Supervisor thresholds.

Authorization: The caller must have *JOBCTL special authority or be authorized to the QIBM_DB_SQLADM function usage ID.

Read syntax diagramSkip visual syntax diagramADD_QUERY_THRESHOLD( THRESHOLD_NAME => threshold-name,THRESHOLD_TYPE => threshold-type,THRESHOLD_VALUE => threshold-value,JOB_NAMES => job-names,INCLUDE_USERS => include-users,EXCLUDE_USERS => exclude-users,SUBSYSTEMS => subsystems,DETECTION_FREQUENCY => detection-frequency,LONG_COMMENT => long-comment )

The schema is QSYS2.

threshold-name
A character or graphic string that provides a name for the threshold. The name can be up to 30 characters long and can contain any characters including blanks. The name cannot be the same as an existing threshold name.
threshold-type
A character or graphic string that specifies the type of the threshold to be enforced.
CPU TIME
The total processing unit time used by the query, in seconds.
ELAPSED TIME
The total clock time, in seconds.
TEMPORARY STORAGE
The amount of storage, in megabytes (MB), that the query allocates.
TOTAL IO COUNT
The total number of I/O operations.
threshold-value
A big integer value that contains the threshold value, in units defined by the specified threshold-type. The value must be greater than 0.
job-names
A character or graphic string that specifies up to 100 job names separated by either a blank or a comma. Each job name can end with a wildcard character. For example, 'QPADEV*' indicates that any job name starting with the characters 'QPADEV' is a match.
Only jobs running with a matching job name are eligible to be supervised. Can contain the following special value:
*ALL
All jobs are supervised. This is the default.
include-users
A character or graphic string that specifies up to 100 user names separated by either a blank or a comma. A group profile does not expand to include all profiles that are members of the group. Each name can end with a wildcard character. For example, 'ADM*' indicates that any user name starting with the characters 'ADM' is a match.
Queries where the effective user of the thread matches one of these names are eligible to be supervised. If one or more names are specified for include-users, the exclude-users parameter must have a value of *NONE. Can contain the following special value:
*ALL
Jobs for all users are supervised. This is the default.
exclude-users
A character or graphic string that specifies up to 100 user names separated by either a blank or a comma. A group profile does not expand to include all profiles that are members of the group. Each name can end with a wildcard character. For example, 'ADM*' indicates that any user name starting with the characters 'ADM' is a match.
Queries where the effective user of the thread matches one of these names are not supervised. If one or more names are specified for exclude-users, the include-users parameter must have a value of *ALL. Can contain the following special value:
*NONE
No jobs are explicitly eliminated from being supervised based on the user name. This is the default.
subsystems
A character or graphic string that specifies up to 100 subsystem names separated by either a blank or a comma. Each name can end with a wildcard character. For example, 'RSBS*' indicates that any subsystem name starting with the characters 'RSBS' is a match.
Only jobs running in a subsystem that match one of these names are eligible to be supervised. Can contain the following special value:
*ALL
All jobs are supervised. This is the default.
detection-frequency
An integer value that specifies the minimum time, in seconds, between calls to exit programs registered with the QIBM_QQQ_QRY_SUPER exit point for this threshold for a specific thread. During the detection-frequency time interval following a threshold detection, the Query Supervisor does not process additional instances for this threshold in the same thread. Each time a query is run in a thread, a specific threshold can be processed at most once during the execution of that query.
The detection-frequency provides a protection from having a specific threshold recognized and processed more frequently than desired. If the same threshold is encountered within the same thread and the detection-frequency time period has not yet completed, the associated exit programs are not called and there is no external evidence that the Query Supervisor did not perform threshold detection.
The default is 600 (10 minutes).
long-comment
A character or graphic string that describes this Query Supervisor threshold. It can be up to 2000 characters long.

Examples

  • Set a threshold value for total CPU time to 30 seconds for all jobs. When this value is reached, any registered exit programs will be called.
    
CALL QSYS2.ADD_QUERY_THRESHOLD(THRESHOLD_NAME  => 'MAXTIME',
                               THRESHOLD_TYPE  => 'CPU TIME',
                               THRESHOLD_VALUE => 30,
                               LONG_COMMENT    => 'Maximum runtime for all jobs');
  • Set a threshold value for temporary storage to 10 megabytes for all jobs except for those running with the BATCHRUN user profile. Set a detection frequency of 2 minutes. When the storage threshold value is reached, any registered exit programs will be called. If the same thread reaches this threshold for a different query within 2 minutes, the registered exit programs will not be called. 
    
CALL QSYS2.ADD_QUERY_THRESHOLD(THRESHOLD_NAME         => 'Temp storage',
                               THRESHOLD_TYPE         => 'TEMPORARY STORAGE',
                               THRESHOLD_VALUE        => 10,
                               EXCLUDE_USERS          => 'BATCHRUN',
                               DETECTION_FREQUENCY    => 120);