Query Governor Exit Program

The Query Governor exit program is called when a job is running a query and the estimated runtime or temporary storage usage has exceeded the user specified limits. This exit is called in the job that is attempting to run the query. The exit program is passed a structure that contains the estimated runtime, the user specified runtime limit, the estimated temporary storage usage, and the user specified temporary storage limit for the query. Also included is the Structured Query Language (SQL) statement text of the query, if applicable. The exit program may set a return code value that is basically used to either ignore the exceeded limit and continue running the query or end the query request. When a query is run, the operating system calls the user-written exit program through the registration facility. For information about adding an exit program to an exit point, see the Registration Facility APIs.

Only query full opens will call the exit program, thus if the query is the result of an SQL statement, pseudo opens will not call the exit program. Also, a query of a DDM file will not call the exit program on the source system, but it could call the exit program on the target system. A native open of an SQL view could call the exit program.

The Query Governor can be enabled by using the Query processing time limit (QRYTIMLMT) or Query temporary storage limit (QRYSTGLMT) parameters on the Change Query Attributes (CHGQRYA) CL command. It can also be enabled by setting the QUERY_TIME_LIMIT or STORAGE_LIMIT options in the query options file. The query time limit can also be enabled by using the QQRYTIMLMT system value.

Authorities and Locks

User Profile Authority

*ALLOBJ and *SECADM to add or remove exit programs to the registration facility

Required Parameter Group

Query Governor Input Information

INPUT;CHAR(*)

Information needed by the exit program with respect to the query that exceeded user specified runtime or temporary storage usage limits. For the format of this parameter, see QRYG0100 Format.

Query Governor Output Information

OUTPUT;BINARY(4)

Return code. The return code to indicate whether the query should be canceled. The valid values are:

0

If it exists, the next exit program will be called allowing the query to still be run to completion or cancelled without issuing the inquiry message CPA4259. If this is the last exit program, the query will issue the CPA4259 inquiry message and let the system or user handle the request. This is the initial default action.

1

If it exists, the next exit program will be called allowing the query to still be cancelled without issuing the inquiry message. If this is the last exit program or no other exit programs return a 3, the exceeded limit will be ignored and the query will be allowed to run to completion.

2

Same as 1, except that the database monitor will also gather information about the query if *COND is specified on the FTRQRYGOVR parameter of the Start Database Monitor (STRDBMON) command. See Usage Notes for more information.

3

The query will be cancelled. Any remaining exit programs will not be called.

QRYG0100 Format

The following table shows the format of the input information parameter for the exit program. For detailed descriptions of the fields in the table, see Field Descriptions.

Field Descriptions

Current user name. The current user profile initiating the query.

Estimated runtime in seconds. The query's estimated runtime in number of seconds.

Estimated temporary storage usage in megabytes. The query's estimated query temporary storage usage number in megabytes.

Format name. The name of the format being used.

Job name. The name of the job issuing the query request.

Job number. The number of the job issuing the query request.

Length of SQL statement text. The length of the SQL statement that exceeded the user specified query limit. 0 if the query is not SQL.

Offset to SQL statement text. Offset from the start of the Query Governor Input Information to the SQL statement that exceeded the user specified query limit. 0 if the query is not SQL.

Reserved. A reserved field.

Size of fixed header for QRYG0100. Size of fixed header information.

Specified temporary storage limit in megabytes. The user specified query temporary storage usage limit in number of megabytes.

Specified time limit in seconds. The user specified query time limit in number of seconds.

User Name. The user name under which the job that is issuing the query request is started.

Usage Notes

• Exit program(s) will be called for all query requests where the estimated runtime and/or temporary storage usage exceed user specified limits.
• If an exit program fails for any reason (not found, not authorized, function check in the program) the messages will be left in the job log and processing of the query will be determined by the current default action. The current default action is determined by the return code of the previously called exit program. For the initial exit program, the default is 0.
• Exit program(s) registered for this exit point must be thread safe and compiled with ACTGRP(*CALLER) because the exit program may be called as the result of a query in an SQL external function. SQL external functions do not allow ACTGRP(*NEW).
• Exit program(s) will run in the job that issues the query request.
• Exit program(s) registered after a job has started might not be called for that existing job.
• Exit program(s) removed after a job has started may continue to be called for that existing job.
• Exit program(s) must be defined in the system ASP.
• When an exit program executes a query or an SQL function, the exit program could be called recursively. The exit program doing these operations must be coded to avoid recursion loops.
• Many applications and tools set the query time limit to 0 for gathering performance information. It is recommended that exit program(s) return a 0 for a query time limit of 0.
• The STRDBMON command can be used to gather information about the query that is predicted to exceed user specified limit(s).
  • If *ALL is specified on the FTRQRYGOVR parameter, the monitor will gather information for the query regardless of what return code is specified by the exit program(s).
  • If *COND is specified on the FTRQRYGOVR parameter, the monitor will gather information for the query if the return code specified by exit program(s) is a 2 or 3. Also, if the return code is 0 and the query is cancelled via the CPA4259 inquiry message, information will be gathered as well.
• The query governor will be ignored and exit program(s) will not be called for queries run in system jobs such as initial program load (IPL) and the install path.

[ Back to top | Database and File APIs | APIs by category ]