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.
nice one. Thanks
ReplyDeleteIt 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.
ReplyDeleteI use a non-IBM tool that allows me to include a spool file into the body of the email.
DeleteHi, how to send email multiple spoolfiles at once through the program
DeleteI would move all the spool files into one output queue. Then you can use SQL to list the contents of the output queue, and then process each one in turn.
DeleteSimon,
ReplyDeleteGreat 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.
Thank you for the compliment.
DeleteThat does sound a very interesting suggestion. It may be the germ of another post.
Very informative post.
ReplyDeleteThanks for the detailed explanation for every line of code.
You are welcome.
DeleteI 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!
ReplyDeleteThank you for sharing how use this methodolgy.
DeleteThank you Simon, but my reports contains special LPI and CPI definitions, need mantain this, so CPYSPLF dont work.
DeleteYou set the CPI and LPI using the OVRPRTF command before you do the CPYSPLF.
DeleteDear Sir,
ReplyDeleteI have an outq of EOD where all the reports are generated overnight. Now I want to convert all spooled file of that outq into an IFS folder. But the problem is CPYSPLF take only one spooled file at a time. Can you pls help me out? (i need it disparately. Thank you.)
Read this.
Delete