Query Supervisor Exit Program


  Required Parameter Group:


  Exit Point Name: QIBM_QQQ_QRY_SUPER

  Exit Point Format Name: QRYS0100

  QSYSINC Member Name: EQQQRYSV

The Query Supervisor exit program is called when a job is running a query and a resource threshold defined by the Query Supervisor is met or exceeded. This exit is called in the thread that is running the query. The exit program is passed a structure that contains information about the job, the monitored threshold, and the actual value measured during query execution. Also included when applicable are the SQL statement text of the query, a comma-separated list of host variables, and SQL client special registers. The exit program may set a return code value indicating whether to continue running the query or to terminate the query.

The resource thresholds monitored by the Query Supervisor may be configured by using the ADD_QUERY_THRESHOLD procedure and the REMOVE_QUERY_THRESHOLD procedure. A newly defined threshold will not affect currently running queries but will apply to any subsequent queries that are run. Similarly, thresholds that are removed will remain in effect for all queries already running at the time of the removal. The resource thresholds apply to each individual query and do not accumulate across queries in a job.

The Query Supervisor is active on both full opens and pseudo opens of queries. The defined thresholds apply to all operations that run through the SQL Query Engine (SQE) and relate to only those resources used by SQE. This includes both native and SQL queries, as well as native file opens of views or of files with Row and Column Access Control. 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.

When a Query Supervisor threshold is met or exceeded, the operating system calls user-written exit program(s), in sequence of definition. For information about adding an exit program to an exit point, see the Registration Facility APIs. A maximum of eight exit programs may be registered for QIBM_QQQ_QRY_SUPER.

The Query Supervisor is to be distinguished from the Query Governor in that the Query Supervisor monitors actual resource usage of running queries while the Query Governor applies to estimated resource usage before a query begins execution.

Fully functional Query Supervisor example exit programs, written in ILE C and ILE RPG, can be found here.

Authorities and Locks

User Profile Authority
To add or remove exit programs to the registration facility, *ALLOBJ special authority or QIBM_DB_SQLADM function usage is required.

Required Parameter Group

Query Supervisor Input Information
INPUT;CHAR(*)

Information needed by the exit program with respect to the query that met or exceeded a defined resource threshold. For the format of this parameter, see QRYS0100 Format.


Query Supervisor Output Information
OUTPUT;BINARY(4)

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

0
If it exists, the next exit program will be called. If this is the last exit program, the query will continue to run.
1
The query will be terminated. Any remaining exit programs will not be called. When the query is terminated, the Query Supervisor issues a CPF5209 escape message and SQL statements fail with SQLCODE -666 and SQLSTATE 57005.

QRYS0100 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 CLIENT_ACCTNG special register. The CURRENT CLIENT_ACCTNG special register contains the value of the accounting string from the client information specified for the query. See CURRENT CLIENT_ACCTNG for more information about this special register.

CURRENT CLIENT_APPLNAME special register. The CURRENT CLIENT_APPLNAME special register contains the value of the application name from the client information specified for the query. See CURRENT CLIENT_APPLNAME for more information about this special register.

CURRENT CLIENT_PROGRAMID special register. The CURRENT CLIENT_PROGRAMID special register contains the value of the client program ID from the client information specified for the query. See CURRENT CLIENT_PROGRAMID for more information about this special register.

CURRENT CLIENT_USERID special register. The CURRENT CLIENT_USERID special register contains the value of the client user ID from the client information specified for the query. See CURRENT CLIENT_USERID for more information about this special register.

CURRENT CLIENT_WRKSTNNAME special register. The CURRENT CLIENT_WRKSTNNAME special register contains the value of the workstation name from the client information specified for the query. See CURRENT CLIENT_WRKSTNNAME for more information about this special register.

Format name. The name of the format being used.

Host variable list. The comma separated list of host variables and parameter markers used by the SQL statement. The host variable list is a UTF-16 string encoded as CCSID 1200. If the host variable list is longer than 10,000 characters (20,000 bytes), only the first 10,000 characters are included.

Job name. The name of the job running the query.

Job number. The number of the job running the query.

Job user. User profile under which the job was initiated.

Length of CURRENT CLIENT_ACCTNG special register. The length of the SQL CURRENT CLIENT_ACCTNG special register.

Length of CURRENT CLIENT_APPLNAME special register. The length of the SQL CURRENT CLIENT_APPLNAME special register.

Length of CURRENT CLIENT_PROGRAMID special register. The length of the SQL CURRENT CLIENT_PROGRAMID special register.

Length of CURRENT CLIENT_USERID special register. The length of the SQL CURRENT CLIENT_USERID special register.

Length of CURRENT CLIENT_WRKSTNNAME special register. The length of the SQL CURRENT CLIENT_WRKSTNNAME special register.

Length of host variable list. The length in bytes of the list of host variables and parameter markers used by the SQL statement. If the query is not SQL, this value will be 0.

Length of SQL statement text. The length in bytes of the SQL statement that reached the threshold. If the operation type is not 1, this value may be 0.

Offset to CURRENT CLIENT_ACCTNG special register. Offset from the start of the Query Supervisor Input Information to the SQL CURRENT CLIENT_ACCTNG special register.

Offset to CURRENT CLIENT_APPLNAME special register. Offset from the start of the Query Supervisor Input Information to the SQL CURRENT CLIENT_APPLNAME special register.

Offset to CURRENT CLIENT_PROGRAMID special register. Offset from the start of the Query Supervisor Input Information to the SQL CURRENT CLIENT_PROGRAMID special register.

Offset to CURRENT CLIENT_USERID special register. Offset from the start of the Query Supervisor Input Information to the SQL CURRENT CLIENT_USERID special register.

Offset to CURRENT CLIENT_WRKSTNNAME special register. Offset from the start of the Query Supervisor Input Information to the SQL CURRENT CLIENT_WRKSTNNAME special register.

Offset to host variable list. Offset from the start of the Query Supervisor Input Information to the list of host variables and parameter markers used by the SQL statement. If the query is not SQL, this value will be 0.

Offset to SQL statement text. Offset from the start of the Query Supervisor Input Information to the SQL statement that reached the threshold. If the operation type is not 1, this value may be 0.

Operation type. Indicates the type of operation that reached the threshold. The possible values are:

1
SQL.
3
Native query opened for input only.
4
Native query opened for other options.
5
Native open for input only.
6
Native open for other options.

Query identifier. A hexadecimal string uniquely identifying the query text and its execution environment. Also referred to as the QRO hash.

Query plan identifier. A numeric value uniquely identifying the specific access plan that is implementing the query.

Reserved. A reserved field.

Size of fixed header for QRYS0100. Size of fixed header information.

SQL statement text. The SQL statement that reached the threshold. The SQL statement text is a UTF-16 string encoded as CCSID 1200. For non-SQL queries, an SQL-like approximation of the query will be generated if possible. If the SQL statement text is longer than 10,000 characters (20,000 bytes), only the first 10,000 characters are included.

Subsystem. The subsystem in which the job is running.

Threshold consumption value. The actual measurement of the resource associated with the threshold type. The unit of measure depends on the threshold type.

Threshold name. The user-defined name of the threshold that was reached. The name is a blank-padded UTF-16 string encoded as CCSID 1200.

Threshold type. The threshold type that was reached. The possible values are:

CPU TIME
The total processing unit time used by the query, in seconds.
ELAPSED TIME
The total clock time, in seconds.
TEMPORARY STORAGE
The amount of storage, in megabytes (MB), that the query uses.
TOTAL IO COUNT
The total number of I/O operations.

User name. The effective user name of the thread running the query.


Usage Notes


Exit Program Requirements


Exit program introduced: V7R3

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