Query Supervisor configuration and operation

Query Supervisor thresholds are configured using the add and remove procedures: ADD_QUERY_THRESHOLD procedure and REMOVE_QUERY_THRESHOLD procedure. The QUERY_SUPERVISOR view shows the defined thresholds.

Before a query runs, a list of applicable thresholds is passed to SQE. As the query executes, SQE monitors the resource usage and determines if a threshold has been reached. When a threshold is met or exceeded, the query execution is interrupted and any exit programs registered with the Query Supervisor Exit Point (QIBM_QQQ_QRY_SUPER) are called in sequence of their exit program registration number, from lowest to highest. The exit program is called inline within the same thread of the query that reached the threshold. As a result, the query is interrupted and paused until the exit program processing has completed.

Information passed to the exit programs includes details such as the threshold reached, the job name, the SQL statement or native query request, the client special register values, and host variable or parameter marker values. The structure passed to the exit program includes enough detail to allow the DBE to take a wide variety of actions, such as logging the information, sending a message, or retrieving detailed information about the query from the plan cache.

The exit program also has the option to indicate that the query should be terminated. If query termination is requested, no further exit programs are called, and the Query Supervisor issues a CPF5209 escape message with reason code 3. SQL statements will fail with SQLCODE -666 and SQLSTATE 57005.

If query termination is not requested, execution of the query resumes after all the registered exit programs have completed. It is possible for multiple, distinct thresholds to be reached simultaneously, in which case multiple calls to the exit programs will be made before the query resumes.

Resource usage is measured only for processing that the query engine performs while producing the query result set. It does not apply to other database or operating system processing that happens in conjunction with the operation. Consider the following SQL DELETE statement: DELETE FROM <table> WHERE <column> <= 100. Query supervision only applies to the system resources used to determine the rows selected by the WHERE predicate. The resources consumed by the deletion of rows, journal processing, and any associated index maintenance for the DELETE are not supervised.

The Query Supervisor will also supervise queries executed as part of a user-defined function (UDF) or user-defined table function (UDTF) invocation when the reference to that function is not inlined. When a query uses a function, the Query Supervisor independently supervises the resources consumed by any queries run during execution of the function. Resources consumed by the invoking query prior to the function invocation are not counted as part of the resources used by the function’s queries; the resource usage counters for each query within the function start at 0. However, resources consumed by the queries within the function are added to the resource usage totals for the invoking query.

Any threshold that is applied to the function’s queries and that is met while the function is executing will cause the exit program(s) to be called at that point in time. However, when a threshold that is applied to the invoking query is met while a function is running, the exit program call will be delayed until the function completes and the invoking query continues executing.

Consider a TOTAL IO COUNT threshold of 500 I/Os and a query that invokes a function that executes another query. Assume the invoking query consumes 400 I/Os and then invokes the function, whose query consumes 200 I/Os. The exit program will be called for the invoking query but only after the function completes. However, if the function's query consumes 500 I/Os, it is possible (subject to the threshold’s DETECTION_FREQUENCY) that the exit program could be called both within the function execution (with 500 I/Os) and for the invoking query (with 900 I/Os) once the function completes.

In general, resource usage is checked on sub-second intervals while SQE is processing the query. As a result, there may be a small delay between when the query reaches a threshold and when the query supervisor detects this and calls the exit program. The delay is most likely to affect TOTAL IO COUNT and TEMPORARY STORAGE thresholds and may result in threshold consumption values that exceed the defined threshold value. Certain operations within the query engine, such as queries containing UDF calls as described above, can produce a more measurable delay that affects all threshold types. In all cases, however, the exit program will eventually be called and will be provided with the accurate and current resource consumption detail.

A specific Query Supervisor threshold will apply at most one time for each query execution within a thread. For example, a threshold defined for ELAPSED TIME of 5 seconds is reached only at the first 5 seconds of a query’s execution and not repeatedly at further intervals of 5 seconds while the query runs.