Using DETECTION_FREQUENCY to protect system resources

Edit online

Because the Query Supervisor may be the first line of detection and defense against a critical performance problem, it is important to make the operation of the Query Supervisor as light-weight as possible. For this reason, each threshold has a DETECTION_FREQUENCY associated with it. This value, which defaults to 600 seconds (10 minutes), indicates how much time a given thread must wait after detecting the threshold before it may detect and signal the same threshold again. If the threshold is exceeded again in the thread before the defined interval is completed, the threshold will be ignored, and the exit program(s) will not be called.

Note that the DETECTION_FREQUENCY applies to multiple occurrences of a specific threshold within the same thread.

Consider a single threaded job, JOB1, which is executing queries. The following thresholds are defined:

THRESHOLD_NAME => 'WARNING'
THRESHOLD_TYPE => 'ELAPSED TIME'
THRESHOLD_VALUE => 10
DETECTION_FREQUENCY => 600
THRESHOLD_NAME => 'RUNAWAY'
THRESHOLD_TYPE => 'ELAPSED TIME'
THRESHOLD_VALUE => 120
DETECTION_FREQUENCY => 600

The following example timeline shows the impact of DETECTION_FREQUENCY:

00:00:00: JOB1 begins a query that will take 20 seconds to complete.
00:00:10: Query Supervisor calls the exit program in JOB1. The exit program input structure includes the name of the threshold: WARNING.
The WARNING threshold will be silenced for any future queries in JOB1 for 600 seconds, until 00:10:10.
00:00:30: JOB1 begins another query that runs for 25 seconds.
No Query Supervisor action is taken after 10 seconds of execution since the WARNING threshold's DETECTION_FREQUENCY time interval has not been met.
00:01:05: JOB1 begins a query that runs for 215 seconds.
00:03:05: Query Supervisor calls the exit program in JOB1. The exit program input structure includes the name of the threshold: RUNAWAY.
The RUNAWAY threshold will be silenced for any future queries in JOB1 for 600 seconds, until 00:13:05.
00:09:59: JOB1 begins a query that will run for 50 seconds.; No Query Supervisor action is taken after 10 seconds of execution since the WARNING threshold's DETECTION_FREQUENCY time interval has not been met. Since the threshold value is detected while reporting is silenced, this query will not call the exit program for the WARNING threshold.

A DETECTION_FREQUENCY of 0 is permitted, in which case every instance of the threshold is processed by a call to the exit program(s).