Setting resource limits with the Predictive Query Governor

The Db2 for i Predictive Query Governor can stop the initiation of a query if the estimated run time (elapsed execution time) or estimated temporary storage 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 system. It can be used with all Db2 for i 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 CQE 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 take longer than the user wants to wait.

The governor in Db2 for i is based on two measurements:

  • The estimated runtime for a query.
  • The estimated temporary storage consumption for a query.

If the query estimated runtime or temporary storage usage exceed the user-defined limits, the initiation of the query can be stopped.

To define a time limit (in seconds) 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. The command language used is the first place where the optimizer attempts to find the time limit.
  • Set the Query Time Limit option in the query options file. The query options file 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 Change Query Attributes (CHGQRYA) CL command, and set the query options file to *DEFAULT. The system value is the third place where the query optimizer attempts to find the time limit.

To define a temporary storage limit (in megabytes) for the governor to use, do the following:

  • Use the Query Storage Limit (QRYSTGLMT) parameter on the Change Query Attributes (CHGQRYA) CL command. The command language used is the first place where the query optimizer attempts to find the limit.
  • Set the Query Storage Limit option STORAGE_LIMIT in the query options file. The query options file is the second place where the query optimizer attempts to find the time limit.

The time and temporary storage values generated by the optimizer are only estimates. The actual query runtime might be more or less than the estimate. In certain cases when the optimizer does not have full information about the data being queried, the estimate could vary considerably from the actual resource used. In those cases, you might need to artificially adjust your limits to correspond to an inaccurate estimate.

When setting the time limit for the entire system, set it to the maximum allowable time that any query must be allowed to run. By setting the limit too low you run the risk of preventing some queries from completing and thus preventing the application from successfully finishing. There are many functions that use the query component to internally perform query requests. These requests are also compared to the user-defined time limit.

You can check the inquiry message CPA4259 for the predicted runtime and storage. If the query is canceled, debug messages are still written to the job log.

You can also add the Query Governor Exit Program that is called when estimated runtime and temporary storage limits have exceeded the specified limits.