Wednesday, November 14, 2018

Converting spool file data into data file

copy spool file data to physical file

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:

  1. What is the name of the spool file that will contain the information?
  2. What is the record length of the spool file?
  3. What is the layout of the data within the spool file, where can I find the information I desire?
  4. 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.

2 comments:

  1. Still a very common and prevalent technique.

    What 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.

    ReplyDelete
  2. 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:

    dcl-f SPLFILE disk(132) extfile('QTEMP/SPLFILE');

    read SPLFILE Data;

    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.