Set resource limits with the Predictive Query Governor

 

The DB2 Universal Database™ for iSeries™ 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 iSeries. It can be used with all DB2 Universal Database for iSeries 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 server 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 will take longer than the user wants to wait.

The governor in DB2 Universal Database for iSeries is based on two measurements:

• The estimated runtime for a query.

• The estimated temporary storage consumption for a query.

If the query’s 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. 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 Change Query Attributes (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.

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. This 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. This is the second place where the query optimizer attempts to find the time limit.

It is important to remember that 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 may vary considerably from the actual resource used. In those case, you may need to artificially adjust your limits to correspond to an inaccurate estimate.

When setting the time limit for the entire server, it is typically best to set the limit to the maximum allowable time that any query should be allowed to run. By setting the limit too low you will 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 will also be 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 will still be 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.

• Using the Query Governor
  The resource governor works in conjunction with the query optimizer.

• Canceling a query with the Query Governor
  When a query is expected to take more resources than the set limit, the governor issues inquiry message CPA4259.

• Controlling the default reply to the query governor inquiry message
  The system administrator can control whether the interactive user has the option of ignoring the database query inquiry message by using the Change Job (CHGJOB) CL command.

• Testing performance with the query governor
  You can use the query governor to test the performance of your queries.

• Examples of setting query time limits
  To set the query time limit for the current job or user session using query options file QAQQINI, specify QRYOPTLIB parameter on the Change Query Attributes (CHGQRYA) command to a user library where the QAQQINI file exists with the parameter set to QUERY_TIME_LIMIT, and the value set to a valid query time limit.

• Testing temporary storage usage with the query governor
  The predictive storage governor specifies a temporary storage limit for database queries. You can use the query governor to test if a query uses any temporary object to run the query, such as a hash table, sort or temporary index.

• Examples of setting query temporary storage limits
  The temporary storage limit can be specified either in the QAQQINI file or on the Change Query Attributes (CHGQRYA) command.

 

Parent topic:

Change the attributes of your queries with the Change Query Attributes (CHGQRYA) command

 

Related information

Query Governor Exit Program
End Request (ENDRQS) command
Change Query Attributes (CHGQRYA) command