Query Supervisor Exit Program
Required Parameter Group:
| 1 | Query Supervisor Input Information | Input | Char(*) |
| 2 | Return Code | Output | Binary(4) |
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, ILE RPG, and CL 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.
| Offset | Type | Field | |
|---|---|---|---|
| Dec | Hex | ||
| 0 | 0 | BINARY(4) | Size of fixed header for QRYS0100 |
| 4 | 4 | CHAR(8) | Format name |
| 12 | C | CHAR(10) | Job name |
| 22 | 16 | CHAR(10) | Job user |
| 32 | 20 | CHAR(6) | Job number |
| 38 | 26 | CHAR(10) | Subsystem |
| 48 | 30 | CHAR(10) | User name |
| 58 | 3A | CHAR(8) | Query identifier |
| 66 | 42 | BINARY(8), UNSIGNED | Query plan identifier |
| 74 | 4A | CHAR(60) | Threshold name |
| 134 | 86 | CHAR(30) | Threshold type |
| 164 | A4 | BINARY(8) | Threshold consumption value |
| 172 | AC | BINARY(2) | Operation type |
| 174 | AE | BINARY(4) | Offset to SQL statement text |
| 178 | B2 | BINARY(4) | Length of SQL statement text |
| 182 | B6 | BINARY(4) | Offset to host variable list |
| 186 | BA | BINARY(4) | Length of host variable list |
| 190 | BE | BINARY(4) | Offset to CURRENT CLIENT_ACCTNG special register |
| 194 | C2 | BINARY(4) | Length of CURRENT CLIENT_ACCTNG special register |
| 198 | C6 | BINARY(4) | Offset to CURRENT CLIENT_APPLNAME special register |
| 202 | CA | BINARY(4) | Length of CURRENT CLIENT_APPLNAME special register |
| 206 | CE | BINARY(4) | Offset to CURRENT CLIENT_PROGRAMID special register |
| 210 | D2 | BINARY(4) | Length of CURRENT CLIENT_PROGRAMID special register |
| 214 | D6 | BINARY(4) | Offset to CURRENT CLIENT_USERID special register |
| 218 | DA | BINARY(4) | Length of CURRENT CLIENT_USERID special register |
| 222 | DE | BINARY(4) | Offset to CURRENT CLIENT_WRKSTNNAME special register |
| 226 | E2 | BINARY(4) | Length of CURRENT CLIENT_WRKSTNNAME special register |
| 230 | E6 | CHAR(*) | Reserved |
| * | * | CHAR(*) | SQL statement text |
| * | * | CHAR(*) | Host variable list |
| * | * | CHAR(*) | CURRENT CLIENT_ACCTNG special register |
| * | * | CHAR(*) | CURRENT CLIENT_APPLNAME special register |
| * | * | CHAR(*) | CURRENT CLIENT_PROGRAMID special register |
| * | * | CHAR(*) | CURRENT CLIENT_USERID special register |
| * | * | CHAR(*) | CURRENT CLIENT_WRKSTNNAME special register |
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(s) will run in the thread that issues the query request. If the Db2 Symmetric Multiprocessing feature is being used, it is possible that the exit program(s) will be run in a thread being used for parallel execution of the query request.
- 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 will continue with any remaining exit programs. In addition, if any Query Supervisor exit program failures are encountered message CPD43B0 will be sent to the QSYSOPR message queue once per day and will provide information about the first failure.
- When an exit program is built with USRPRF(*USER), it will run with the authorities of the user that caused the query threshold to be met or exceeded. If the exit program needs specific authorization, consider using adopted authority, USRPRF(*OWNER), when building the exit program.
- 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.
- The Query Supervisor 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.
- In some cases, the actual value may exceed the threshold value configured for the named threshold. This is because there may be a brief interval between the time that the threshold is reached and the time that the exit program is called. Additional resources may be used in this interval, and the actual value reflects the resource usage at the time of the exit program call.
- SQL statement text which is longer than 10,000 characters (20,000 bytes) is truncated.
- A host variable list which is longer than 10,000 characters (20,000 bytes) is truncated. In addition, any individual host variable which is longer than 1,024 characters (2,048 bytes) is truncated.
- When any column referenced by the query has a SECURE YES attribute, all values in the host variable list will be replaced with *SECURE.
- During execution of the exit program(s), the Query Supervisor will be disabled in the thread that is running the exit program. If the exit program initiates database work in other threads or jobs, care must be exercised, as the system will not prevent recursive calls to the exit program. One way to prevent recursion is to include the SQL SET OPTION MONITOR = *SYSTEM statement in the program to disable Query Supervisor during the work performed on behalf of the exit program.
Exit Program Requirements
- Exit program(s) must be defined in the system ASP.
- Exit program(s) must use ACTGRP(*CALLER).
- Exit program(s) registered for this exit point must be thread safe. If the exit program is implemented using ILE RPG, use THREAD(*CONCURRENT).
- Use of SQL or native database I/O within an exit program is not supported.
- Since the exit program will run in the user's job, avoid using commitment control within the exit program.
Exit program introduced: V7R3