Control long-running queries with the DB2 UDB for AS/400 Predictive Query Governor

The DB2 UDB for AS/400 Predictive Query Governor can stop the initiation of a query if the estimated or predicted run time (elapsed execution time) for the query is excessive. The governor acts before a query is run instead of while a query is run. The governor can be used in any interactive or batch job on the AS/400. It can be used with all DB2 UDB for AS/400 query interfaces and is not limited to use with SQL queries.

The ability of the governor to predict and stop queries before they are started is important because:

• Operating a long-running query and abnormally ending the query before obtaining any results wastes system resources.
• Some operations within a query cannot be interrupted by the End Request (ENDRQS) CL command. The creation of a temporary index or a query using a column function without a GROUP BY clause are two examples of these types of queries. It is important to not start these operations if they will take longer than the user wants to wait.

The governor in DB2 UDB for AS/400 is based on the estimated runtime for a query. If the query's estimated runtime exceeds the user defined time limit, the initiation of the query can be stopped.

To define a time limit for the governor to use, do one of the following:

• Use the Query Time Limit (QRYTIMLMT) parameter on the Change Query Attributes (CHGQRYA) CL command.This is the first place where the query optimizer attempts to find the time limit.
• Set the Query Time Limit option in the query options file. This is the second place where the query optimizer attempts to find the time limit.
• Set the QQRYTIMLMT system value. Allow each job to use the value *SYSVAL on the CHGQRYA CL command, and set the query options file to *DEFAULT. This is the third place where the query optimizer attempts to find the time limit.

How the query governor works

The governor works in conjunction with the query optimizer. When a user requests DB2 UDB for AS/400 to run a query, the following occurs:

1. The query access plan is evaluated 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.

2. The estimated runtime is compared against the user-defined query time limit currently in effect for the job or user session.
3. If the predicted runtime for the query is less than or equal to the query time limit, the query governor lets the query run without interruption and no message is sent to the user.
4. If the query time limit is exceeded, inquiry message CPA4259 is sent to the user. The message states that the estimated query processing time of XX seconds exceeds the time limit of YY seconds.

   Note:

   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 predicted runtime exceeds the governor time limit.