Copying multiple spool files into one PDF

The task came in: I needed to take many spool files from an output queue and convert them into just one PDF. I have written about converting individual spool files into PDF, but now there was the need for the to be only one PDF.

My solution would need to:

1. Merge all of the spool files into one spool file
2. Convert the one spool file into a PDF in the IFS

Both of these I have done separately on many occasions; this was the first time to do them together.

I cannot use the spool files I used for this task, but I have two spool files in my personal output queue:

Work with All Spooled Files Device or Total Opt File User Queue User Data Sts Pages _ QPPGMDMP SIMON MYOUTQ RDY 1 _ TESTPGM SIMON MYOUTQ RDY 10

The first of these spool files is a program dump, the second a compile listing. By all means they are unrelated, but this is just merging and converting the spool to PDF, any spool files will do.

I have done everything in one RPG program that is all SQL. I chose to use RPG as I can format the SQL statement in my opinion in a more readable format in this post in RPG that I would using the RUNSQL command in a CL program.

I am going to break the program into parts so that I can more easily describe which part does.

I am going to start with the part that deleted and creates the work files I will be using:

01 **free 02 exec sql SET OPTION COMMIT = *NONE ; 03 exec sql CALL QCMDEXC('DLTF QTEMP/WK*') ; 04 exec sql CALL QCMDEXC('CRTPF FILE(QTEMP/WKSPLFS) RCDLEN(133)') ;

Line 1: By using totally free RPG my code can start in the first line of the source member and go all the way to the end. You can't do that in fixed format!

Line 2: I always add this Set option to ensure that commitment control is not used.

Line 3: I really like using the SQL QCMDEXC procedure as I only have to pass it the string, or variable, that contains the command. No need to pass it the length of that string. Here I am deleting all of my work files in QTEMP, they both start with the characters "WK".

Line 4: Here I am building my work file for the spool files. Both of my spool files have a maximum length of 132 characters. Therefore, I make my output file 133, one more than 132, so it will contain the FCFC characters need to format the final spool file.

05 exec sql CREATE TABLE QTEMP.WKSQLRTN 06 (SPOOLED_FILE_NAME,JOB_NAME,FILE_NUMBER,RETURN_CODE) 07 AS 08 (SELECT SPOOLED_FILE_NAME,JOB_NAME,FILE_NUMBER, 09 QSYS2.QCMDEXC('CPYSPLF FILE(' || SPOOLED_FILE_NAME || 10 ') TOFILE(QTEMP/WKSPLFS) JOB(' || JOB_NAME || 11 ') SPLNBR(' || FILE_NUMBER || 12 ') CTLCHAR(*FCFC) MBROPT(*ADD)') 13 FROM TABLE(QSYS2.OUTPUT_QUEUE_ENTRIES('*LIBL','MYOUTQ','*NO')) 14 WHERE STATUS = 'READY' 15 ORDER BY CREATE_TIMESTAMP) 16 WITH DATA ;

This look a lot more complicated than it is. This SQL statement is what copies the spool files from the output queue into the work file I just created.

Line 5: I am creating a new work file "on the fly" to contain information about whether I could successfully copy the spool file.

Line 6: I need to give the list of columns that this table will contain as the last column, RETURN_CODE, is not a named column in SQL statement I will be executing.

Line 8: These are the first three columns that are inserted into the output table. What I like about the SQL long column names is that they can fully describe the data the column contains.

lines 9 – 12: This is where is looks complicated, but trust me it is not. I am using the QCMDEXC scalar function. Being a scalar function means I can add it into a select statement to be executed when each row of the results is returned. It must contain an IBM i command. Here I am using the Copy to Spool file command, CPYSPLF, using the columns returned in the results. I use the double pipe ( ||) to denote concatenation, I know this is not ANSI standard SQL but I never want this code to run in any other operating.

The formatted statements for the two spool files are:

CPYSPLF FILE(QPPGMDMP) TOFILE(QTEMP/WKSPLFS) JOB(355067/SIMON/QPADEV000H) SPLNBR(6) CTLCHAR(*FCFC) MBROPT(*ADD) CPYSPLF FILE(TESTPGM) TOFILE(QTEMP/WKSPLFS) JOB(355130/SIMON/QPADEV000J) SPLNBR(10) CTLCHAR(*FCFC) MBROPT(*ADD)

The QCMDEXC scalar functions returns "1" if the statement was successful, and something else if it was not. This is why I am capturing this information in a table so that I will be able to see if any of the spool files was not copied to the output file in the CPYSPLF command.

Line 13: I am using the OUTPUT_QUEUE_ENTRIES table function as that will only return results for my output queue only. The "*NO" indicates that I only want the limited results returned, by using this the results are returned faster.

Line 14: I only want to return the spool files that are ready to print.

Line 15: And I want the spool files returned in time order, the oldest first.

Line 16: If I don't have this the output table is not created, or will be empty.

If I was to check the WKSQLRTN table after line 16, I can see the whether my spool files were successfully copied:

SELECT * FROM QTEMP.WKSQLRTN SPOOLED_FILE_NAME JOB_NAME FILE_NUMBER RETURN_CODE ---------------- ----------------------- ----------- ----------- QPPGMDMP 355067/SIMON/QPADEV000H 1 1 TESTPGM 355130/SIMON/QPADEV000J 10 1

As the return codes are both "1" I know that both of the spool files were copied without error into the output table.

17 exec sql CALL QSYS2.QCMDEXC('OVRPRTF FILE(QSYSPRT) CTLCHAR(*FCFC) HOLD(*YES) + USRDTA(''Combined'') OVRSCOPE(*JOB)') ; 18 exec sql CALL QSYS2.QCMDEXC('CPYF FROMFILE(QTEMP/WKSPLFS) TOFILE(QSYSPRT) + MBROPT(*ADD)') ; 19 exec sql CALL QSYS2.QCMDEXC('DLTOVR FILE(QSYSPRT) LVL(*JOB)') ;

Line 17: I need to override an existing printer file to contain the data I am going to copy to it from the WRKSPLFS table, which contains all of the spool files' data. I need to use an override of job here rather than call level. The call level ends when the QCMDEXC procedure finishes executing the statement. The job level override makes sure that the override lingers. The new spool file will have a unique User Data value so that I can easily identify it.

Line 18: I use the CPYF command to copy the contents of the table into the spool file.

Line 19: As the override was set as job I need to delete the override.

If I was to go to my output queue I can see my new spool file with the total number of pages as the spool files I copied into it.

Work with All Spooled Files Device or Total Opt File User Queue User Data Sts Pages _ QPPGMDMP SIMON MYOUTQ RDY 1 _ TESTPGM SIMON MYOUTQ RDY 10 _ QSYSPRT SIMON MYOUTQ Combined HLD 11

Last step is to copy the contents to a PDF.

20 exec sql CALL QSYS2.QCMDEXC('CPYSPLF FILE(QSYSPRT) + TOFILE(*TOSTMF) + SPLNBR(*LAST) + TOSTMF(''/home/MyFolder/combined.pdf'') + WSCST(*PDF) + STMFOPT(*REPLACE)') ; 21 *inlr = *on ;

Line 20: This is my final CPYSPLF command. I have said I want a PDF and where in the IFS I want this file to be created. I have even said that if there is already a file in that folder with the same name I want that replaced with this new file.

After compiling the above program and calling it I can use SQL to check if the PDF is present in my folder in the IFS using the IFS_OBJECT_STATISTICS table function:

SELECT PATH_NAME,OBJECT_TYPE FROM TABLE(QSYS2.IFS_OBJECT_STATISTICS('/home/MyFolder','NO','*ALLSTMF')) ;

I have used three parameters with the table function:

1. '/home/MyFolder':  The folder to return results for
2. 'NO':  Do not include any subfolders
3. '*ALLSTMF'  Return only stream files, PDF are stream files

The results are:

PATH_NAME OBJECT_TYPE --------------------------- ----------- /home/MyFolder/combined.pdf *STMF

I did copy this file to my PC, opened it as a PDF, and the data contained within was clearly visible.

Now you'll agree when I say this looks more complicated than it really is.

 

This article was written for IBM i 7.5, and should work for some earlier releases too.