iSeries stored procedure - how to get handle on the spool file output?

Question

We have a stored procedure written using a CL and RPG program combination. When called locally on the iSeries all is fine. When called externally (for example from a SQL frontend) the RPG program cannot get a hadle on the spool file it produces because the spool file appears under a different (random?) job number and user. The jobs run as QUSER in the QUSRWRK subsystem but the spool file gets the user id of which the connection was made externally in the connection pool (i.e USERA).

Is there a way of being able reliably to get a handle on the correct sppol file as the job runs (rather than relying on picking the last spool fiel from that queue etc).

Answer 1

I need a bit more information, but I'll make some assumptions. Please clarify if I assumed wrong.

The QUSER in QUSRWRK behavior is correct. You are now running through the SQL server (or similar server). All connections run under these settings.

There are a couple approaches.

1) Assuming that this all runs in one job. Using '*" for the job information should work.

2) The other option is to use RTVJOBA CURUSER(&ME). The current user is the person that is logged in. USER would not work in this case.

Answer 2

If you can modify the RPG program you can retrieve job information from the Program Status Data Structure while the File Information Data Structure has the spool file number from the open feedback area. However I'm not sure the job information will be for the QUSER job (not what you need) or for the USERA job (what you need). The spool file number could be enough of a handle for subsequent Print API calls.

Answer 3

The job itself knows or can find out (see previous answers) so, if all else fails, modify the program to place a message on a queue that provides the information you need. Read it off at your leisure.

.

Answer 4

If you're running a stored procedure (running in job QZDASOINIT), you will no be able to access the spooled output via the program status data structure. Those spooled files reside in a job named, user/QPRTJOB, where user is the "current user" running the stored procedure. To access the spooled files, run api QSPRILSP to obtain a structure that points you to the spooled file.

Both the behavior and API is well documented in IBM's infocenter.