Wednesday, August 3, 2022

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.

3 comments:

  1. Hi Simon
    great job
    Thanks a lot

    ReplyDelete
  2. Like all of your articles, this is excellent.
    Thank you for taking the time to do this!

    ReplyDelete
  3. I know the *CALL QCMDEXC(* method for creating the file on the fly works. Is there any value to be gained using the *SQL global temporary table* method? I use this often in programs where I want a QTEMP table.

    declare global temporary table WKSPLFS
    (SPOOLDATA CHAR (133 ) NOT NULL WITH DEFAULT)
    <

    ReplyDelete

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.