Using the Query Governor

 

The resource governor works in conjunction with the query optimizer.

When a user issues a request to the server to run a query, the following occurs:

  1. The query access plan is created by the optimizer.

    As part of the evaluation, the optimizer predicts or estimates the runtime for the query. This helps determine the best way to access and retrieve the data for the query. In addition, as part of the estimating process, the optimizer also computes the estimated temporary storage usage for the query.

  2. The estimated runtime and estimated temporary storage is compared against the user-defined query limit currently in effect for the job or user session.

  3. If the estimates for the query are less than or equal to the specified limits, the query governor lets the query run without interruption and no message is sent to the user.

  4. If the query limit is exceeded, inquiry message CPA4259 is sent to the user. The message states the estimates as well as the specified limits. Realize that only one limit needs to be exceeded; it is possible that you will see that only one limit was exceeded. Also, if no limit was explicitly specified by the user, a large integer value will be shown for that limit.

    A default reply can be established for this message so that the user does not have the option to reply to the message, and the query request is always ended.

  5. If a default message reply is not used, the user chooses to do one of the following:

    • End the query request before it is actually run.

    • Continue and run the query even though the estimated value exceeds the associated governor limit.

Setting the resource limits for jobs other than the current job

You can set either or both resource limits for a job other than the current job. You do this by using the JOB parameter on the Change Query Attributes (CHGQRYA) command to specify either a query options file library to search (QRYOPTLIB) or a specific QRYTIMLMT, or QRYSTGLMT, or both for that job.

Using the resource limits to balance system resources

After the source job runs the Change Query Attributes (CHGQRYA) command, effects of the governor on the target job is not dependent upon the source job. The query resource limits remain in effect for the duration of the job or user session, or until a resource limit is changed by a Change Query Attributes (CHGQRYA) command. Under program control, a user might be given different limits depending on the application function being performed, the time of day, or the amount of system resources available. This provides a significant amount of flexibility when trying to balance system resources with temporary query requirements.

 

Parent topic:

Set resource limits with the Predictive Query Governor