This chapter provides information about performing cost analysis prior to an exit, result destinations, and e-mail message format.
DB2 Query Patroller can invoke a customer-written exit program just prior to performing cost analysis for a query. The exit program can examine the SQL statement and prevent DB2 Query Patroller from executing the statement. DB2 Query Patroller invokes the exit program for all jobs containing a SELECT, INSERT, UPDATE, or DELETE statement unless the user bypassed cost analysis. (The user can only bypass cost analysis if the system administrator sets the Cost Analysis Required option to N in the user's profile.)
DB2 Query Patroller invokes the exit analysis program if you set IWM_EXIT_AN to the path name of the executable program before starting the server processes. The exit program can be a shell script or any other executable. DB2 Query Patroller invokes the exit program under the iwm user ID. DB2 Query Patroller passes the job owner's user ID as the only argument to the exit. The SQL statement can be read from STDIN. If the exit routine terminates with an exit code of zero and without having written anything to STDOUT, DB2 Query Patroller continues to process the job normally. If the exit routine terminates with a non-zero exit code or writes anything to STDOUT, the job aborts. The job abort message includes the message written to STDOUT.
Normally, the database stores query results as tables. The Result Destinations table contains the descriptions of any available alternative destinations for query results. These alternative destinations can designate that the results should be written to a file or piped to a process. If written to a file or pipe, the results are output in comma-separated variable format.
When the results are to be written to a file, the destination description designates the file name. When the results are to be piped, the description designates the name of the executable to which the results will be piped, along with any command-line parameters.
For either files or piped destinations, the description can contain the following variables. The run-time values replace these variables before the query executes.
A seven-digit job number replaces this variable.
The user ID of the job's owner replaces this variable.
The path name of the home directory of the job's owner replaces this variable.
The pipe process can be a shell script or any other executable. DB2 Query Patroller invokes this process under the job owner's user ID, but with the environment variables set as they were when agent started. The process can read the results from STDIN unless the system administrator specifies the $(PIPE) variable symbol in the result destination description, in which case it can be read from a named pipe created by the DB2 Query Patroller SQL executor. If the process terminates before reading all of the results, the job aborts. To create a result file on the machine named "remote", you can use the following destination description:
rsh remote "cat > job$(JOB)_results"
Unless you set the IWM_MAIL environment, the DB2 Query Patroller notifier component uses the shell script $IWM_RUNTIME/bin/iwm_mail.sh to format e-mail messages. You can also create your own script to format the messages and then set the IWM_MAIL environment variable to the name of your script. If you want to create your own formatting script, use the iwm_mail.sh script supplied by IBM as a guide.
The formatting script executes each time the notifier needs to send e-mail to a user about a job that has either completed normally, aborted, been canceled, or been put on hold. The script receives the job details using STDIN and must format and write the message body to STDOUT. The iwm_mail.sh script supplied by IBM is written as a KornShell script. You may write a customized formatting script in any language.