IBM DB2 Query Patroller Administration Guide

Result Destination Administration

This section provides information and procedures for administering result destinations.

Result Destinations

Normally, DB2 Query Patroller stores query results as database tables. These tables reside in the tablespace defined by the DQP_RES_TBLSPC profile variable. If this profile variable does not designate a tablespace, result tables are placed into tablespaces determined by DB2. For more information on setting DB2 Query Patroller profile variables, see DB2 Profile Variables.

In the DB2 Query Patroller Result Destinations table, you can specify descriptions for alternative query result destinations. An alternative destination can be a file or a named pipe. If results are written to a file or pipe, the results are output in comma-separated variable format.

When the results are written to a file, the destination description designates the file name. When the results are written to a pipe, the description designates the name of the executable to which the results will be piped, along with any command line parameters.

Result Destination Variables

For either files or piped destinations, the description can contain the following variables. The run-time values replace these variables before the query executes:

$(JOB)

A seven digit number replaces this variable.

$(USER)

The user ID of the job's owner replaces this variable.

$(HOME)

The path name of the home directory of the job's owner replaces this variable. On Windows, this will be C:\.

If all three variables were used in a result destination description, the description might look similar to the following:

   $(HOME)\$(USER)\job$(JOB)_results\result.txt

After the result destination variables are resolved, the result destination will look similar to the following:

   C:\qpuser\job0000002_results\result.txt 

Another variable that can be specified is the $(PIPE) variable, which is used to represent a pipe that is named in the pipe program. Instead of writing the data to the command's STDIN, DB2 Query Patroller can write data to a named pipe.

If the $(PIPE) variable is used in the result destination description, the name of the pipe used by the DB2 Query Patroller server will be passed in as a program argument. For example, if the result destination description is:

C:\pipes\testprogram.exe $(PIPE)

the $(PIPE) variable will be resolved to the name of the pipe and passed into the program. The program must use this argument to open a connection to the named pipe in order to receive the result set. Sample pipe programs are provided on the DB2 Query Patroller CD-ROM in the db2/samples/c directory.

Piped Result Destinations

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 the 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. On UNIX, to create a result file on the machine named "remote", you can use the following destination description:

   rsh remote "cat > job$(JOB)_results"

Pipes to STDIN on Windows

In a Windows environment, pipes to STDIN are handled differently than on other operating systems. Because of the way Windows passes handles between applications, an additional argument is needed. The additional argument, -9 handleNumber, is appended to the call to the pipe program. For example, if the piped result destination is C:\pipes\testprogram.exe $(HOME)\ $(USER) \$(JOB), the call to the pipe program, after the result destination variables have been resolved, will be similar to the following:

C:\pipes\testprogram.exe C:\qpuser\0000001 -9 handleNumber

The additional argument, -9 handleNumber, is the parent process's pipe write handle. The pipe program must close this passed handle to allow the server process to break the pipe when it is finished writing. If the handle is not closed, the pipe process will hang waiting for data, and the server process will hang waiting for the pipe process to terminate.

Pipe program examples are included on the DB2 Query Patroller CD-ROM in the db2/samples/c directory. Samples include: pipe_stdin_NT.c pipe_named_NT.c pipes.c

Listing Result Destinations

Use the following procedure to list result destinations:

1. In the QueryAdministrator main window, select the Result Set Administration tab.
2. Click on List All Result Sets to list all result destinations. Each result destination is listed in tabular format on the Result Set Administration page.

Creating a Result Destination

Use the following procedure to create a result destination:

1. In the QueryAdministrator main window, select the Result Set Administration tab.
2. Click on New. An empty result destination window opens.

   
   
   

3. Enter a value in each of the fields.
4. Click on OK after all values have been entered.

The following list provides information for each node parameter.

Destination Name

Provides the name for the result destination. QueryEnabler displays these names in the Result Destinations field when a user schedules a new query.

Destination Type

Indicates the type of destination:

• File indicates that the destination is a file.
• Pipe indicates that the destination is an executable program.

Format

The only format supported is delimited ASCII.

Description

For a file, the description provides the name of the file. For a pipe, the description provides the command line for the executable program. Descriptions for both types may contain any of the following substitution variables:

• $(USER) is the user ID.
• $(HOME) is the user's home directory.
• $(JOB) is the DB2 Query Patroller job number.

Another variable that can be specified is the $(PIPE) variable, which is used to represent a pipe that is named in the pipe program code. Instead of writing the data to the command's STDIN, DB2 Query Patroller can write data to a named pipe.

If the $(PIPE) is used in the result destination description, the name of the pipe used by the DB2 Query Patroller server will be passed in as a program argument. For example, if the result destination description is:

C:\pipes\testprogram.exe $(PIPE)

the $(PIPE) variable will be resolved to the name of the pipe and passed into the program. The program must use this argument to open a connection to the named pipe in order to receive the result set. Sample pipe programs are provided on the DB2 Query Patroller CD-ROM in the db2/samples/c directory.

Editing a Result Destination

Use the following procedure to edit a result destination:

1. On the Result Set Administration page, select a result destination.
2. Click on View / Edit. The Detailed Information for Result Destination window opens.
3. Edit the destination type, the description, or both.
4. Click on OK.

Removing a Result Destination

Use the following procedure to remove a result destination:

1. On the Result Set Administration page, select the result destination you want to remove.
2. Click on Remove.
3. Click on Yes to verify that you want to remove the selected result destination.