Wednesday, March 23, 2016

Copying multiple spool files into one

cpysplf copy spool file

Last week I received a request from my manager to merge three reports into one so that he can read it easily on his mobile phone. It has been a long time since I last did it, and when I asked my colleagues if they knew how to do it I received a lot of blank looks.

Let me start by saying I do not want to change the report programs, the printer files they produce are the right width to display on a mobile phone. Fortunately merging spool files is very simple, I can do it all with a few CL commands.

In this example I am going copy three spool files into one. The first and third spool files are generated by a RPG program. The second one is generated using Query. I really could have used any spool files providing they contain plain text.

In my example program I have to create a file that will contain the data from the spool files. I need to create the file to be one character larger than the widest of the spool files I will be copying. In this example the widest spool file has a record length of 132. The extra character will be used for the ANSI form control character.

04  CRTPF FILE(QTEMP/WSPLF) RCDLEN(133) +
            TEXT('To contain copied spool files')

Now I can call the RPG and copy the generated spool file into file I have just created.

05  OVRPRTF FILE(TESTPRTF) HOLD(*YES) SPLFNAME(PRTFILE1) +
              OVRSCOPE(*CALLLVL)

06  CALL PGM(TESTRPG) PARM('1')

07  CPYSPLF FILE(PRTFILE1) TOFILE(QTEMP/WSPLF) +
              SPLNBR(*LAST) MBROPT(*ADD) CTLCHAR(*FCFC)

08  DLTSPLF FILE(PRTFILE1) SPLNBR(*LAST)

Line 5: I am using the Override print file command, OVRPRTF, to hold the file so it does not print before I have copied it, change the spool files name to PRTFILE1, and override the printer file at the call level. This level of override means that when this program ends the override is removed from the file.

Line 6: Nothing surprising here, the program that creates the spool file is called.

Line 7: The Copy Spool File command, CPYSPLF, is used to copy from the spool file to the file I created on line 4. I need to give the Control Character parameter to tell the command to include the form control character: CTLCHAR(*FCFC)

Line 8: As I do not need the spool file any more I delete it.

The steps for copying the Query's spool file are similar:

09  OVRPRTF FILE(QPQUPRFIL) HOLD(*YES) OVRSCOPE(*CALLLVL)

10  RUNQRY QRY(TESTQRY)

11  CPYSPLF FILE(QPQUPRFIL) TOFILE(QTEMP/WSPLF) +
              SPLNBR(*LAST) MBROPT(*ADD) CTLCHAR(*FCFC)

12  DLTSPLF FILE(QPQUPRFIL) SPLNBR(*LAST)

I use the same program to generate the third spool file as I did with the first. I just override to a different spool file name in the OVRPRTF command.

13  OVRPRTF FILE(TESTPRTF) HOLD(*YES) SPLFNAME(PRTFILE3) +
              OVRSCOPE(*CALLLVL)

14  CALL PGM(TESTRPG) PARM('3')

15  CPYSPLF FILE(PRTFILE3) TOFILE(QTEMP/WSPLF) +
              SPLNBR(*LAST) MBROPT(*ADD) CTLCHAR(*FCFC)

16  DLTSPLF FILE(PRTFILE3) SPLNBR(*LAST)

If I look in the file WSPLF with the Display Physical File Member command, DSPPFM, I can see the form control characters in the first position:

1PRTFILE1    MM/DD/YY
 Line No. 1
 Line No. 2
 Line No. 3
 Line No. 4
 Line No. 5
 Line No. 6
0* * *   E N D   O F   R E P O R T   * * *
1
-
0MM/DD/YY  HH:MM:SS  From Query  PAGE    1
0F001
0This is from a Query
0* * *  E N D  O F  R E P O R T  * * *
1PRTFILE3    MM/DD/YY
 Line No. 1
 Line No. 2
 Line No. 3
 Line No. 4
0* * *   E N D   O F   R E P O R T   * * *

The form control characters in this file are:

Control
character
Hexadecimal
equivalent
Description
1 x'F1' Skip to next page, equivalent of SKIPB(001) in the DDS of a printer file
blank x'40' Single spacing, equivalent of SPACEB(001)
0 x'F0' Double spacing, equivalent of SPACEB(002)
hyphen ( - ) x'60' Triple spacing, equivalent of SPACEB(003)

As this combined report is to be viewed on a mobile phone I want to remove all the page breaks and any triple spacing. I could write a RPG to do this, but decided to do the same in SQL within the CL program.

17  RUNSQL SQL('DELETE FROM QTEMP/WSPLF +
                 WHERE SUBSTR(WSPLF,1,1) = x''60''') +
             COMMIT(*NC)

18  RUNSQL SQL('UPDATE QTEMP/WSPLF +
                   SET WSPLF = x''F0'' CONCAT SUBSTR(WSPLF,2) +
                 WHERE SUBSTR(WSPLF,1,1) = x''F1''') +
             COMMIT(*NC)

For those of you not familiar with the RUN SQL Statement command, SQL, you can learn about it here.

Line 17: I am deleting the triple spacing. As there is only one field in this file I need to use a substring, SUBSTR, to extract the value in the first position.

Line 18: Here I am changing all the skip to next page characters. I cannot just change a substring using the Update statement, I need to give the values that will populate the whole field. Therefore, I give the hexadecimal value in the first position and use a concatenate, CONCAT, to combine this with the rest of the field starting in the second position.

The changed file looks like this:

0PRTFILE1    MM/DD/YY
 Line No. 1
 Line No. 2
 Line No. 3
 Line No. 4
 Line No. 5
 Line No. 6
0* * *   E N D   O F   R E P O R T   * * *
0
0MM/DD/YY  HH:MM:SS  From Query  PAGE    1
0F001
0This is from a Query
0* * *  E N D  O F  R E P O R T  * * *
0PRTFILE3    MM/DD/YY
 Line No. 1
 Line No. 2
 Line No. 3
 Line No. 4
0* * *   E N D   O F   R E P O R T   * * *

So now to copy the data from the file back into a spool file.

19  OVRPRTF FILE(QSYSPRT) CTLCHAR(*FCFC) HOLD(*YES) +
              USRDTA('Combined') OVRSCOPE(*CALLLVL)

20  CPYF FROMFILE(QTEMP/WSPLF) TOFILE(QSYSPRT) +
           MBROPT(*ADD)

Line 19: Before I copy the file into the spool file to override the spool file. I have to use the control character parameter, CTLCHAR(*FCFC). I am holding the spool file so that it will not print when it has finished copying. There is no reason to change the user data parameter, USRDTA, but I just like to.

Line 20: I use the Copy File command, CPYF, to copy the data from the file into the printer file.

When I run this program and look at my spool files I find that I have only one. As I stripped all the skip commands from the file it is only one page.

                       Work with All Spooled Files

                              Device or                     Total
 Opt  File        User        Queue       User Data   Sts   Pages
  _   QSYSPRT     SIMON       MYOUTQ      Combined    HLD       1

Here is the program in its entirety :

01  PGM

02  DLTF FILE(QTEMP/WSPLF)
03  MONMSG MSGID(CPF0000)

04  CRTPF FILE(QTEMP/WSPLF) RCDLEN(133) +
            TEXT('To contain copied spool files')


05  OVRPRTF FILE(TESTPRTF) HOLD(*YES) SPLFNAME(PRTFILE1) +
              OVRSCOPE(*CALLLVL)

06  CALL PGM(TESTRPG) PARM('1')

07  CPYSPLF FILE(PRTFILE1) TOFILE(QTEMP/WSPLF) +
              SPLNBR(*LAST) MBROPT(*ADD) CTLCHAR(*FCFC)

08  DLTSPLF FILE(PRTFILE1) SPLNBR(*LAST)

09  OVRPRTF FILE(QPQUPRFIL) HOLD(*YES) OVRSCOPE(*CALLLVL)

10  RUNQRY QRY(TESTQRY)

11  CPYSPLF FILE(QPQUPRFIL) TOFILE(QTEMP/WSPLF) +
              SPLNBR(*LAST) MBROPT(*ADD) CTLCHAR(*FCFC)

12  DLTSPLF FILE(QPQUPRFIL) SPLNBR(*LAST)

13  OVRPRTF FILE(TESTPRTF) HOLD(*YES) SPLFNAME(PRTFILE3) +
              OVRSCOPE(*CALLLVL)

14  CALL PGM(TESTRPG) PARM('3')

15  CPYSPLF FILE(PRTFILE3) TOFILE(QTEMP/WSPLF) +
              SPLNBR(*LAST) MBROPT(*ADD) CTLCHAR(*FCFC)

16  DLTSPLF FILE(PRTFILE3) SPLNBR(*LAST)

17  RUNSQL SQL('DELETE FROM QTEMP/WSPLF +
                 WHERE SUBSTR(WSPLF,1,1) = x''60''') +
             COMMIT(*NC)

18  RUNSQL SQL('UPDATE QTEMP/WSPLF +
                   SET WSPLF = x''F0'' CONCAT SUBSTR(WSPLF,2) +
                 WHERE SUBSTR(WSPLF,1,1) = x''F1''') +
             COMMIT(*NC)

19  OVRPRTF FILE(QSYSPRT) CTLCHAR(*FCFC) HOLD(*YES) +
              OVRSCOPE(*CALLLVL)

20  CPYF FROMFILE(QTEMP/WSPLF) TOFILE(QSYSPRT) +
           MBROPT(*ADD)

21  ENDPGM

 

You can learn more about the CPYSPLF command from the IBM website here.

 

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


Addendum

Since writing this post I have received several comments and messages from companies saying their software does this already.

The purpose of this blog is to describe how to do things with a "vanilla" version of IBM i, with no third party tools. This way any person can use what is discussed in these posts without purchasing additional tools or software.

I am not saying that there is anything wrong with third party tools and software. I use several non-IBM tools every day.

I do not want this blog to become a free advertisement or promotion platform for these tools. If you really want your tool on this blog you can always pay for an advert.

9 comments:

  1. It works! Thanks. Can you share how you are delivering the file to the phone? I would probably send it as an email attachment, but would like to learn if there are better ways.

    ReplyDelete
    Replies
    1. I use a non-IBM tool that allows me to include a spool file into the body of the email.

      Delete
  2. Simon,

    Great tip and source code sample. You deserve more recognition for being the IBM i champion that you are.

    I was just thinking about how page breaks might be preserved in the final output. One idea would be to output the QTEMP file as an HTML stream file (replacing the page break control characters with an HTML / CSS alternative) rather than outputting a final spool file. Then transform the HTML file to a PDF.

    ReplyDelete
    Replies
    1. Thank you for the compliment.

      That does sound a very interesting suggestion. It may be the germ of another post.

      Delete
  3. Very informative post.
    Thanks for the detailed explanation for every line of code.

    ReplyDelete
  4. I used CPYSPLF to take multiple spool files of labels, which are generated by one of the popular label-printing software packages currently available. Since our label printers are remotely attached, the performance of actual printing slowed down when a group of labels was being sent to the printer. I used APIs within RPG to generate a user space, and in that space I generated a list of the spool files for these labels. I processed that list with CPYSPLF to generate one concatenated spool file, which is then sent to the output queue attached to the remote printer. The labels now stream out of that printer, so this was another successful use of CPYSPLF!

    ReplyDelete
    Replies
    1. Thank you for sharing how use this methodolgy.

      Delete

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.