I was surprised that I was asked this question, but as I have been asked six times in the last week it must be something that is important for people to find an answer for. Copying data from a spool file to a data file with separate fields is not an efficient way to get data. If you are considering using this method for capturing system information, spool files, active job info, etc, then you should be using the Views, Tables, etc that IBM has been creating for us to use. You can search this web site to see if I have written about getting to the information you desire using SQL. Trust me it is easier that what I am describing below.
In this example I am going to use the Work Output Queue command, WRKOUTQ, to generate a list of spool files, and I will be writing that data to a file. If I was doing this in the real world I would using the SQL and the method described in Output queue entries information via SQL. Now I have vented my feelings on this, let me proceed.
I want to know the following information about spool files in the QEZJOBLOG output queue:
01 A R OUTQINFOR 02 A SPLFNAME 10A 03 A USER 10A 04 A USRDTA 10A 05 A STATUS 3A 06 A PAGES 5P 0 07 A COPIES 3P 0 08 A FILENUMBER 6A 09 A JOBNAME 10A 10 A JOBNUMBER 6A 11 A CREATEDATE L |
This is an example of the importance of using meaningful field or column names.
Before I start I need to do some research. I need to know the following:
- What is the name of the spool file that will contain the information?
- What is the record length of the spool file?
- What is the layout of the data within the spool file, where can I find the information I desire?
- is there a unique code somewhere in the record to allow me to identify the records I want?
The above will be different for every spool file. You will need to work it out before you start writing your programs.
The Work Output Queue command generates the spool file QPRTSPLQ which is 132 characters wide. And its contents look like:
*...+....1....+....2....+....3....+....4....+....5....+....6....+....
5770SS1 V7R3M0 140418 Work With Output Queue QEZJOBLO
File User User Data Status Pages Copies Form Type Pty
QPJOBLOG D******** QZDASOINIT RDY 2 1 *STD 5
QPJOBLOG QSECOFR JOB1 RDY 2 1 *STD 5
QPJOBLOG QSECOFR JOB2 RDY 3 1 *STD 5
QPJOBLOG QSECOFR JOB3 RDY 2 1 *STD 5
QPJOBLOG BRMSDDM QRWTSRVR RDY 1 1 *STD 5
7....+....8....+....9....+....0....+....1....+....2....+....3..
G in QUSRSYS 10/14/18 01:39:32 Page 1
File Number Job Number Date Time
15 QPRTJOB 431235 10/14/18 00:01:17
1 JOB1 460865 10/14/18 00:05:02
1 JOB2 460994 10/14/18 00:11:24
1 JOB3 461122 10/14/18 00:15:01
1344 QPRTJOB 246107 10/14/18 00:32:40
|
I can map the fields I desire to:
| Field | Starting position |
Length |
| Spool file name | 2 | 10 |
| User profile | 13 | 10 |
| User data | 24 | 10 |
| File status | 36 | 3 |
| No. of pages | 42 | 5 |
| No. of copies | 50 | 3 |
| File number | 73 | 6 |
| Job name | 84 | 10 |
| Job number | 95 | 6 |
| Creation date | 102 | 8 |
I will be making this information into a data structure in a RPG program.
Now I can start coding. I am starting with a CL program:
01 PGM
02 WRKOUTQ OUTQ(QEZJOBLOG) OUTPUT(*PRINT)
03 CRTPF FILE(QTEMP/SPLFILE) RCDLEN(132)
04 CPYSPLF FILE(QPRTSPLQ) TOFILE(QTEMP/SPLFILE) +
SPLNBR(*LAST)
05 DLTSPLF FILE(QPRTSPLQ) SPLNBR(*LAST)
06 CLRPFM FILE(MYLIB/OUTQINFO)
07 CALL PGM(MYLIB/OUTINFOR1)
08 ENDPGM
|
Line 2: I am creating my spool file using the WRKOUTQ command, listing the contents of the output queue QEZJOBLOG.
Line 3: I am creating a "flat" file, that is the same number of characters long as the spool file is.
Line 4: The Copy Spool File command, CPYSPLF, will copy the contents of the spool file into the data file I created.
Line 5: As I have copied the spool file I no longer need it, therefore, I will delete it rather than leave it taking up space on my IBM i.
Line 7: I am calling the RPG program that will do the rest, copy the data from the flat file copy of the spool file into a data file with its separate fields.
The RPG program is a very simple program, it just reads the flat file and writes to the data file. Let me start with the definitions, including the data structure.
01 **free
02 ctl-opt option(*nodebugio:*srcstmt:*nounref) ;
03 dcl-f SPLFILE extfile('QTEMP/SPLFILE')
rename(SPLFILE:INPUT)
prefix(i_) ;
04 dcl-f OUTQINFO usage(*output)
extfile('MYLIB/OUTQINFO) ;
05 dcl-ds Data ;
06 SplfName char(10) pos(2) ;
07 User char(10) pos(13) ;
08 UsrDta char(10) pos(24) ;
09 Status char(3) pos(36) ;
10 PagesChar char(5) pos(42) ;
11 CopiesChar char(3) pos(50) ;
12 FileNumber char(6) pos(73) ;
13 JobName char(10) pos(84) ;
14 JobNumber char(6) pos(95) ;
15 CreateDateChar char(8) pos(102) ;
16 end-ds ;
|
Line 1: What is there not to like about getting away from all forms of columns and use totally free RPG.
Line 2: Need to have my favorite control options.
Line 3: This is the file definition for the flat file, containing the spool file's data. I never use a OVRDBF for files in QTEMP as I can use the External Name keyword, EXTNAME to inform the program where the file will be. When I have a flat file created in this way: File name = Record format name = Field name. Therefore, I need to rename the record format, using RENAME, and prefix the field name.
Line 4: My output file is located in my library, MYLIB, and is used only for being written to, output.
Lines 5 – 16: My data structure is defined the same as the table I gave above. I have called the data structure's subfields the same as the fields in the output file so I don't have to move from one to another field. The exceptions are the two numeric fields, PagesChar and CopiesChar, and the date field, CreateDateChar.
And now for the rest of the program:
17 dow (1 = 1) ;
18 read INPUT ;
19 if (%eof) ;
20 leave ;
21 elseif ((%subst(i_SPLFILE:46:1) < '0')
or (%subst(i_SPLFILE:46:1) > '9')) ;
22 iter ;
23 endif ;
24 Data = i_SPLFILE ;
25 monitor ;
26 PAGES = %dec(%xlate(',':' ':PagesChar):5:0) ;
27 on-error ;
28 PAGES = -1 ;
29 endmon ;
30 monitor ;
31 COPIES = %dec(%xlate(',':' ':CopiesChar):3:0) ;
32 on-error ;
33 COPIES = -1 ;
34 endmon ;
35 test(de) *mdy/ CreateDateChar ;
36 if (%error) ;
37 CREATEDATE = *loval ;
38 else ;
39 CREATEDATE = %date(CreateDateChar:*mdy/) ;
40 endif ;
41 write OUTQINFOR ;
42 enddo ;
43 *inlr = *on ;
|
Line 21: I needed to find something in the records of data I want that is not in all other records. In this spool file it is that position 46, part of the number of pages column, must be numeric. If this position is not numeric it is some other line that I do not care about, so I ITER on line 22, to get the next record.
Line 24: I move the input field into the data structure, to break it up into the sub fields.
Lines 25 – 29: I need to convert the character form of the page "number" to a real number. I am doing the conversion within a Monitor group because if there is an error during the conversion the ON-ERROR will handle it. On line 26 I am first translating, %XLATE, any commas ( , ) there may be in the field, as the next step will error if there are commas in the "number". The %DECH built in function converts the result of the %XLATE into a number, and moving it to the output field. If this statement errors then the line after the ON-ERROR, line 27, is executed. That line, line 28, moves -1 to the PAGES field. This allows me to identify the lines where there was an error.
Lines 30 – 34: Doing the same as the previous Monitor group for the number of copies field.
Lines 35 – 40: Here I validate that the date is valid, as I am on machine using USA dates my flat file contains the date in MDY format. If your IBM i uses a different date format this will be different for you. If the date is invalid I move *LOVAL, 0001-01-01, to the date field. If it is valid I use the %DATE built in function to convert the contents into a true date value.
Line 41: All I need to do now is write to the output file.
The first time I run the RPG program I always debug the contents of the data structure to ensure that my sub fields all contain the data I need, and in the format I want.
> EVAL Data SPLFNAME OF DATA = 'QPJOBLOG ' USER OF DATA = 'D********' USRDTA OF DATA = 'QZDASOINIT' STATUS OF DATA = 'RDY' PAGESCHAR OF DATA = ' 2' COPIESCHAR OF DATA = ' 1' FILENUMBER OF DATA = ' 15' JOBNAME OF DATA = 'QPRTJOB ' JOBNUMBER OF DATA = '431235' CREATEDATECHAR OF DATA = '10/14/18' |
When I run the program the data is moved to the fields in the output file without errors.
SPLFNAME USER USRDTA STATUS PAGES
QPJOBLOG D******** QZDASOINIT RDY 2
QPJOBLOG QSECOFR JOB1 RDY 2
QPJOBLOG QSECOFR JOB2 RDY 3
QPJOBLOG QSECOFR JOB3 RDY 2
QPJOBLOG BRMSDDM QRWTSRVR RDY 1
COPIES FILENUMBER JOBNAME JOBNUMBER CREATEDATE
1 15 QPRTJOB 431235 2018-10-14
1 1 JOB1 460865 2018-10-14
1 1 JOB2 460994 2018-10-14
1 1 JOB3 461122 2018-10-14
1 1344 QPRTJOB 246107 2018-10-14
|
As I said at the top of this article, this is not the way I would have got this data. I would have used the SQL View OUTPUT_QUEUE_ENTRIES:
SELECT SPOOLNAME,USER_NAME,USER_DATA,STATUS,
CAST(PAGES AS DECIMAL(5,0)) AS PAGES,
CAST(COPIES AS DECIMAL(3,0)) AS COPIES,
FILENUM,JOB_NAME,
CAST(CREATED AS DATE) AS DATE
FROM QSYS2.OUTPUT_QUEUE_ENTRIES
WHERE OUTQ = 'QEZJOBLOG'
|
And the results are identical to the contents of the output file I had generated:
SPOOLED_FILE_NAME USER_NAME USER_DATA STATUS PAGES COPIES
QPJOBLOG D********* QZDASOINIT READY 2 1
QPJOBLOG QSECOFR JOB1 READY 2 1
QPJOBLOG QSECOFR JOB2 READY 3 1
QPJOBLOG QSECOFR JOB3 READY 2 1
QPJOBLOG BRMSDDM QRWTSRVR READY 1 1
FILENUM JOB_NAME DATE
15 431235/D*********/QPRTJOB 10/14/2018
1 460865/QSECOFR/JOB1 10/14/2018
1 460994/QSECOFR/JOB2 10/14/2018
1 461122/QSECOFR/JOB3 10/14/2018
1,344 246107/BRMSDDM/QPRTJOB 10/14/2018
|
I hope you now understand what you need to do to create your own programs to extract data from a spool file, and into your own data file.
This article was written for IBM i 7.3, and should work for some earlier releases too.
Still a very common and prevalent technique.
ReplyDeleteWhat is your approach to get the RPG to compile seeing as to that SPLFILE doesn't exist? Create it, compile it, delete it? How will someone else recompile this if this is a production program?
These were the types of situations where, dare I say it, program described files came in handy, but RPG-free does not support those anymore. Don't cringe Jon, read on ;)
Like it or not, flatfiles are used for all kinds of purposes. That's why I have created a collection of generic ones: FLAT132P, FLAT500P, FLAT2500P etc. They are permanent objects but not intended to be written to. Dup to QTEMP and rename as needed, tell the RPG Extdesc(FLAT132) Extfile('QTEMP/SPLFILE') and we're ready to roll. No mystery as in "where did this file come from and how did this compile"? No record format rename needed either, it's defined in the (ahem) DDS. Field names are known and consistent i.e. FIELD132, FIELD500. I have a number of them with different widths so I can chose the best match. Writing 80 bytes to a 5000 byte flatfile slows down the writes and wastes DASD. Granted, I would do this particular exercise by hitting the system tables too, but my question is more about flatfiles and ad-hoc files in general and a proper solution for not having program described PFs anymore. I've seen shops create program-specific flatfiles and they had thousands of them. Many were nearly identical in width. That just clutters up the system with all these permanent objects. I ditched them all and pared it down to 10 model flatfiles. Curious what your thoughts are.
Actually, free format RPG does support program described files by specifying a record length on the DISK keyword. You can read the file directly into the data structure:
ReplyDeletedcl-f SPLFILE disk(132) extfile('QTEMP/SPLFILE');
read SPLFILE Data;
*** HELP ***
ReplyDeleteI did a project years ago where we needed the ability to 'Reprint Billings' from archived spool files by just entering the Group# and date. I work elsewhere now and don't have access to the code.
I would capture the original spool files when created and convert to a physical file. Guessing now that I did the CPYSPLF to a PF with 'Print Control Character'. Once in a PF, as above is demonstrated, I was able to write RPG programs to isolate and Extract the desired billing to be reprint and copied that to a Work File. But I can Not figure out how I converted that PF, with Print Control Characters, BACK to a spool file for the Actual Reprint. Any clues what that Command would be???????? Major Deadline... sure wish I could get my hands on that old code I wrote. Please Help!
I cannot get your old code back for you. But I can tell you what I do.
DeleteAs you have a file that contains the data and the Printer Control Characters you should just be able to CPYF the work file into a printer file. For example:
OVRPRTF FILE(QSYSPRT)
CTLCHAR(*FCFC) +
OUTQ(MYOUTQ) +
HOLD(*YES) +
USRDTA('Whatever') +
OVRSCOPE(*CALLLVL)
CPYF FROMFILE(QTEMP/WORKFILE)
TOFILE(QSYSPRT) +
MBROPT(*ADD)
Thank You. The *FCFC worked followed by the CPYSPLF command into the file being overridden. :)
ReplyDelete