Writing a Query Supervisor exit program

A well-written exit program is the key to making the Query Supervisor effective. Each time a threshold is reached, the exit program receives information about the threshold, the job, and the query. The exit program can take action based on this information, including the option to terminate the query.

Because exit programs run inline and interrupt the query execution, it is recommended that the exit program should be as simple as possible. When more complex operations are required of the exit program, it is suggested that they be done asynchronously by passing information to another job for processing. By doing this, the exit program can complete faster, allowing the query to resume execution.

Some possible operations an exit program can perform are:

  • Send an inquiry message, suspending the thread (and query) until a reply is received
  • Send the query text, host variables, and parameter markers to another job to be logged to a database table for future review and analysis
  • Terminate the query
SQL and native database I/O must not be run within an exit program. However, it is possible to run SQL native database I/O in a separate job. Some useful SQL operations include:
  • Send a message to QSYSOPR using the QSYS2.SEND_MESSAGE procedure. See SEND_MESSAGE procedure for details.
    1. The exit program can send the message using the SQL7064 message identifier which has been defined for Query Supervisor notifications. The message is in the QSYS/QSQLMSG message file and has a severity level of 80.
    2. Alternatively, the exit program can send the message with a user created message identifier.
  • Dump information about the plan for the query that was running when the exit program was called. The plan identifier that is part of the exit program data can be passed on the call to this procedure. See DUMP_PLAN_CACHE procedure for details.
  • Use INSERT or MERGE statements to maintain a log of expensive queries.
  • Push details regarding the query supervision event to external systems management solutions.

Any messages generated by a failure to call the exit program or by an unhandled exception within the exit program will appear in the job log of the job that caused the threshold criteria to be met.