Retrieving Spooled Files Generated in a Host Server Job

This information applies to any client that uses an IBM i host server. IBM i host servers include the database server (QZDASOINIT), and the remote command and program call server (QZRCSRVR). The host servers are used by many clients such as IBM i Access Client Solutions and the IBM Toolbox for Java™ JDBC driver and other classes.

Some users have stored procedures or programs that generate spooled files and then work with those spooled files. These programs work fine when run from a 5250 emulation command line or in batch; however, they fail when called in the context of a host server job. This is because spooled files generated by the host server jobs are created as described by the SET PROFILE (QWTSETP) API. The job name of the spooled file is set to <job number>/<current user>/QPRTJOB rather than the current job name (<job number>/QUSER/<hostserver>). There is no way to locate the spooled file based on the current job name. Any attempt to access the spooled file from within the server job by using "current job" (such as CPYSPLF FILE(MYSPLF) TOFILE(MYF) JOB(*) SPLNBR(*LAST)) fails.

Although a generated spooled file cannot be located by referencing the current job, there are other methods available. A few of these methods are listed in the following text.

QSPRILSP API
One possible solution is the Retrieve Identity of Last Spooled File Created (QSPRILSP) API. This API returns the complete spooled file identity of the last spooled file created for the current job or thread. This method is simple to implement but requires IBM i V5R2 or later. If the prestart job MAXUSE parameter is greater than 1 (the default for QZDASOINIT is 200), it can pick up a spooled file generated on the previous use of the job if that happens to be the last spooled file generated. A sample CL program, SPLFODBC2, is contained in the splfodbc.zip file referenced later in this document.

Unique User Profile
A second possible solution relies on the owner of the spooled file being the "current" user profile of the server job. If each server job runs under a unique profile and there is only one instance of the program, then any spooled file generated is the last spooled file generated for that user. The programmer needs to list all spooled files by user, then iterate through the list to locate the last spooled file. The user's spooled files can be found using the List Spooled File API (QUSLSPL) or Open List of Spooled Files API (QGYOLSPL). This method requires a lot more programming than the other methods but works at all releases.

Using OVRPRTF
If multiple jobs are running at one time under the same user profile, there is no way to determine which spooled file was generated by which job. One possible solution is to direct the spooled file to a unique output queue. For example, an output queue could be created based on the job number and the override print file (OVRPRTF) command could be used to direct printer output to the desired output queue.

Although the programmer could track down the spooled file name by iterating through the output queue or user spooled file list, another option is to use the data queue parameter of an output queue to find the fully qualified spooled file job name. The data queue parameter of an output queue sends the spooled file information as a message to the target data queue. If an output queue is unique to the job, the program can issue the RCVDTAQ command to locate the spooled file name. This technique is used in the sample program available at ftp://public.dhe.ibm.com/services/us/igsc/cs2/ApiSamples/Splfodbc.zip. The stored procedure accepts a CL command as input, runs the command, and then returns any spooled file generated by the CL Command to the client as a result set.

Note that the override print file (OVRPRTF) command has no effect when called through the command interpreter. For example, some programmers might try a call to QCMDEXC with the OVRPRTF command in an attempt to have all database server spooled file output directed to a specific output queue or device. This does not work. Instead, add the OVRPRTF command to the actual program (stored procedure). If the program cannot be altered, wrap the program in a CL program and call the CL program as the stored procedure's external program object.

QWTSETP Documentation
For quick reference, the R450 iSeries Information Center documentation for the QWTSETP API is included below:
 

If you use this API to begin running under a specific profile, any spooled files created are, by default, owned by that profile. This is controlled by the spooled file owner (SPLFOWN) parameter on the CRTPRTF command and is done by putting the file under a QPRTJOB job. Any spooled file command that references the spooled file with the job special value * can access only those files that were created before the profiles were swapped. QPRTJOB A QPRTJOB job is the name of a job that files are spooled under when the current job's user name is not the same as the user profile currently running. For example, if you use QWTSETP to set the profile to user JOE and create a spooled file, the file is spooled under job nnnnnn/JOE/QPRTJOB. This ensures that user JOE owns the spooled file and if that user uses the WRKSPLF command, the file is displayed. Output Queue Considerations The output queue a spooled file is placed in may be different after using this API. If the application using this API produces spooled output that needs to be on a secure output queue or the application is expecting the spooled output to be found on a particular output queue, the Printer Device Programming manual should be referenced to determine whether any configuration changes are required. Chapter 1 describes on which output queue the spooled output resides.