Moving one person's spool files to a different output queue

I was asked if there was a simple way to check an output queue, and if there is a spool file of a certain name for one user, to move it to another output queue.

Fortunately this is not as complicated as it sounds as I can use a SQL Table function to retrieve a list of those spool files, and a scalar function to move the spool file.

In this example I am going to be the user, my profile is SIMON, and whenever I find a spool file QPQUPRFIL in the output queue MYOUTQ I want to move it to the output queue OUTQ2.

First I need to produce a list of eligible spool files. Here I can use the SPOOLED_FILE_INFO SQL Table function:

01 SELECT SPOOLED_FILE_NAME, 02 QUALIFIED_JOB_NAME, 03 SPOOLED_FILE_NUMBER, 04 CREATION_TIMESTAMP 05 FROM TABLE(QSYS2.SPOOLED_FILE_INFO( 06 USER_NAME => 'SIMON', 07 STARTING_TIMESTAMP => CURRENT_TIMESTAMP - 1 DAY, 08 STATUS => '*READY *HELD', 09 OUTPUT_QUEUE => '*LIBL/MYOUTQ')) 10 WHERE SPOOLED_FILE_NAME = 'QPQUPRFIL'

Lines 1 – 4: I want the following columns of information:

1. Spool file name
2. Full job name
3. Spool file's file number
4. Creation timestamp – I don't really need this, but I will show you why I wanted it in my results later

Lines 5 – 9: SPOOLED_FILE_INFO has a number of parameters I can use to narrow the selection of results returned to me:

1. List all spool files belonging to the user profile SIMON
2. The spool files must have been created less than a day ago
3. The spool files must have the status of either ready or held
4. The spool files must me in the output queue MYOUTQ

Line 10: An additional selection as I only want spool files where their name is QPQUPRFIL.

When I execute this SQL statement I am returned the following results:

SPOOLED SPOOLED _FILE _FILE_ _NAME QUALIFIED_JOB_NAME NUMBER CREATION_TIMESTAMP --------- ----------------------- ------- ------------------- QPQUPRFIL 147198/SIMON/QPADEV0001 19 2025-05-11-11.53.47 QPQUPRFIL 147201/SIMON/QPADEV0003 1 2025-05-11-12.41.37

As I know I have data I can use, I am ready to get ready to create my program. Before I start work on the program, as I am going to use the QCMDEXC scalar function and I could return more than one result, I am going to need a Table to receive the data from the SQL statement in the program.

My DDL Table is called TESTTABLE:

01 CREATE TABLE MYLIB.TESTTABLE 02 (SPOOL_FILE FOR "SPLFNAME" VARCHAR(10), 03 JOB_NAME FOR "JOBNAME" VARCHAR(28), 04 FILE_NUMBER FOR "FILENBR" DECIMAL(6,0), 05 RETURN_CODE FOR "RTNCODE" DECIMAL(1,0), 06 CREATE_TIME FOR "CRTTIME" TIMESTAMP, 07 RUN_TIME FOR "RUNTIME" TIMESTAMP)

My Table has the same columns as my previous SQL statement, with addition of the RUN_TIME column. The Run Time column will contain the timestamp when the spool file was moved.

And now the modern RPG program:

01 **free 02 exec sql 03 INSERT INTO TESTTABLE 04 SELECT SPOOLED_FILE_NAME, 05 QUALIFIED_JOB_NAME, 06 SPOOLED_FILE_NUMBER, 07 QSYS2.QCMDEXC('CHGSPLFA FILE(' || SPOOLED_FILE_NAME || 08 ') JOB(' || QUALIFIED_JOB_NAME || 09 ') SPLNBR(' || SPOOLED_FILE_NUMBER || 10 ') OUTQ(QUSRSYS/OUTQ2)'), 11 CREATION_TIMESTAMP, 12 CURRENT_TIMESTAMP 13 FROM TABLE(QSYS2.SPOOLED_FILE_INFO( 14 USER_NAME => 'SIMON', 15 STARTING_TIMESTAMP => CURRENT_TIMESTAMP - 1 DAY, 16 STATUS => '*READY *HELD', 17 OUTPUT_QUEUE => '*LIBL/MYOUTQ')) 18 WHERE SPOOLED_FILE_NAME = 'QPQUPRFIL' ; 19 *inlr = *on ;

Lines 2 – 18: This is the SQL statement that will move the spool file to OUTQ2, and write to TESTTABLE a row. This is a SQL Insert statement to insert the results of this SQL statement into TESTTABLE.

Lines 7 – 10: This is the part of the statement that moves the spool file. I am using the Change Spool File Attribute command, CHGSPLFA, which is the same as when with the WRKSPLF command I put a "2" next to the spool file. This command needs several pieces of information to move the spool file:

• SPOOLED_FILE_NAME:  spool file name, which is in the results from the SPOOLED_FILE_INFO Table function
• QUALIFIED_JOB_NAME:  full job name, from SPOOLED_FILE_INFO results
• SPOOLED_FILE_NUMBER:  spool file number, from SPOOLED_FILE_INFO results
• The to output queue, which I have hard coded in the statement

The QCMCEXC Scalar function returns "1" if the statement executed successfully, and "-1" if it failed.

Line 12: Is also an addition. My output file needs the timestamp of when the file was copied, therefore I use the CURRENT_TIMESTAMP to provide that piece of information.

After creating this program I call it. To see whether the spool files were successfully copied I can check the data in TESTFILE:

01 SELECT * FROM TESTTABLE

Which returns two rows of results:

SPOOL_ FILE_ RETURN FILE JOB_NAME NUMBER _CODE CREATE_TIME RUN_TIME --------- ----------------------- ------ ------ ----------------------- -------- QPQUPRFIL 147198/SIMON/QPADEV0001 19 1 2025-05-11-11.53.47... 2025-... QPQUPRFIL 147201/SIMON/QPADEV0003 1 1 2025-05-11-12.41.37... 2025-...

As the Return Code column contains "1" I know that both spool files were moved to OUTQ2.

If I want to be really sure I can use the following SQL statement to check the moved to output queue, OUTQ2.

01 SELECT SPOOLED_FILE_NAME, 02 QUALIFIED_JOB_NAME, 03 SPOOLED_FILE_NUMBER 04 FROM TABLE(QSYS2.SPOOLED_FILE_INFO( 05 USER_NAME => 'SIMON', 06 STARTING_TIMESTAMP => CURRENT_TIMESTAMP - 1 DAY, 07 STATUS => '*READY *HELD', 08 OUTPUT_QUEUE => '*LIBL/OUTQ2')) 09 WHERE SPOOLED_FILE_NAME = 'QPQUPRFIL'

Lines 1 – 3: I only need these three columns to check if my spool files were copied.

Lines 4 – 8: I use SPOOLED_FILE_INFO again.

Line 8: This is the only difference this time I want the results for the output queue OUTQ2.

These are my results:

SPOOLED SPOOLED _FILE _FILE_ _NAME QUALIFIED_JOB_NAME NUMBER --------- ----------------------- ------ QPQUPRFIL 146980/SIMON/QPADEV0001 5 QPQUPRFIL 147198/SIMON/QPADEV0001 19 QPQUPRFIL 147201/SIMON/QPADEV0003 1

I have three files in my results as the first one was already in the output queue before I copied the other two here.

If there were no eligible spool files the SQL statement does not error, it returns a SQL code of 100.

If I want this program to run continuously I would put the SQL statement within a Do loop and use the Sleep C function to pause the program:

01 **free 02 ctl-opt dftactgrp(*no) ; 03 dcl-pr sleep int(10) extproc('sleep') ; 04 *n uns(10) value ; 05 end-pr ; 06 dcl-s SleepReturnCode int(10) ; 07 dow (*on) ; . . . 35 SleepReturnCode = sleep(120) ; 36 enddo ; 37 *inlr = *on ;

Line 2: As I am using the Sleep C procedure I cannot execute this program in the default activation group.

Lines 3 – 5: The procedure prototype of the Sleep C procedure.

Line 7: Start of the never ending Do loop.

The lines with the dots ( .) denote where the SQL statement from the previous program would be.

Line 35: After executing the SQL statement I decided this program would pause for 120 seconds, or two minutes, before the program loops up to line 7.

SQL makes this seemingly complicated task simple.