Wednesday, April 5, 2017

How to write data to a multiple member file

handle multiple member files in rpg

Often I get the same question asked by several people, this time I have been asked the same question five times just this week, and I have been told that this is a question people have been asked in interviews:

How do you write data to different members in the same file using RPGLE?
The file has ten members, and you need to write data to the fifth and sixth members in one RPGLE program.

Before I get started I want to state my thoughts on multiple member files. Just because you can do something is not always the reason why you should. In my opinion data files should contain just one member. If you have multiple companies, branches, entities, etc they can easily be identified by company, branch, and entity code fields. I have worked with applications where companies' data was separated into different members, while it was not impossible to share data across all companies it was more difficult than it needs to be. So, one member per data file.

Here is my example file, TESTFILE, with its ten members, MBR01 - MBR10:

                          Work with Members Using PDM
File  . . . . . .   TESTFILE  
  Library . . . .     MYLIB     

Opt  Member
     MBR01
     MBR02
     MBR03
     MBR04
     MBR05
     MBR06
     MBR07
     MBR08
     MBR09
     MBR10

I quickly came up with three ways to only write data to the fifth and sixth members. There may be others ways, but I am going to describe:

  1. OVRDBF the file to a member, and then call RPGLE program
  2. Define the member in the files' definition
  3. Use SQL, the question did not say I could not use SQL in the RPGLE program

 

OVRDBF the file to a member

This is the most common way I have seen people coping with multiple members. They start with a CL program that uses the Override Database File command, OVRDBF, to override to specific member names.

01  PGM

02  OVRDBF FILE(TESTFILEM5) TOFILE(TESTFILE) +
             MBR(MBR05) OVRSCOPE(*CALLLVL)

03  OVRDBF FILE(TESTFILEM6) TOFILE(TESTFILE) +
             MBR(MBR06) OVRSCOPE(*CALLLVL)


04  CALL PGM(TESTRPG)

05  ENDPGM

Line 2: I am overriding the file name TESTFILEM5 to file TESTFILE's MBR05. I have used the override scope *CALLLVL, which means that only programs called by this one will have the override.

Line 3: As I cannot have two files in a RPG program with the same name I am overriding the file name TESTFILEM6 to the sixth member in TESTFILE.

Line 4: I call my RPGLE program.

01  **free

02  dcl-f TESTFILEM5 usage(*output)
                       extdesc('TESTFILE')
                       rename(TESTFILER:MBR05R)
                       prefix(m5_) ;

03  dcl-f TESTFILEM6 usage(*output)
                       extdesc('TESTFILE')
                       rename(TESTFILER:MBR06R)
                       prefix(m6_) ;

04  m5_F1 = '1' ;
05  m5_F2 = '5th member' ;
06  write MBR05R ;

07  m6_F1 = '1' ;
08  m6_F2 = '6th member' ;
09  write MBR06R ;

10  *inlr = *on ;

Line 1: If am writing RPG you know it is going to be totally free format.

Lines 2 and 3: Here I am defining my two "files" that were "created" by the OVRDBF commands in the CL program. They are both defined for output only using the USAGE keyword. The EXTDESC tells the RPG compiler to use the file TESTFILE for the file's definition when compiling, I do this as we know that TESTFILEM5 and TESTFILEM6 only "exist" after the OVRDBF commands have been executed. I use the RENAME keyword record formats of the two files as they cannot have the same name, and to make the member names more descriptive of the file they belong to. I am using the PREFIX keyword to make the fields from each file have unique names, if I did not the fields from both of the files would have the same names which could lead to confusion. If you want to learn more about these keywords see the post Useful keywords for file definitions.

Lines 4 – 6: This section of code is very simple, move values to the fields and write to the record format for the fifth member.

Lines 7 – 9: Does the same, but to the record format for the sixth member.

If I had to define the files in fixed format RPG they would look like:

02  FTESTFILEM5O    E             DISK    extdesc('TESTFILE')
    F                                       rename(TESTFILER:MBR05R)
    F                                       prefix(m5_)
03  FTESTFILEM6O    E             DISK    extdesc('TESTFILE')
04  F                                       rename(TESTFILER:MBR06R)
05  F                                       prefix(m6_)
     /free

The keywords are all the same, just the format of the definition is different.

 

Define the member in the files' definition

There is a keyword EXTMBR that allows me to define the member name to open when the file is opened. If I use this approach there is no need for a CL program, the RPG program can be called directly.

This program is similar to the previous one, except…

01  **free

02  dcl-f TESTFILEM5 usage(*output)
                       extfile('TESTFILE')
                       extmbr('MBR05')
                       extdesc('TESTFILE')
                       rename(TESTFILER:MBR05R)
                       prefix(m5_) ;

03  dcl-f TESTFILEM6 usage(*output)
                       extfile('TESTFILE')
                       extmbr('MBR06')
                       extdesc('TESTFILE')
                       rename(TESTFILER:MBR06R)
                       prefix(m6_) ;

04  m5_F1 = '2' ;
05  m5_F2 = '5th member' ;
06  write MBR05R ;

07  m6_F1 = '2' ;
08  m6_F2 = '6th member' ;
09  write MBR06R ;

10  *inlr = *on ;

Lines 2 and 3: If you notice that are two keywords in the definition that were not in the previous example:

  • EXTFILE - External file, this is the external (system) name of the file to open. As I cannot have the same file open more than once in the RPG program I have used the file names TESTFILEM5 and TESTFILEM6 to be the internal names of the files, even though they are really the same file, TESTFILE.
  • EXTMBR - Name of the member to open when the file is opened.

Lines 4 – 10: Same as previous example program.

If I had to define the files in fixed format they would look like:

02  FTESTFILEM5O    E             DISK    extdesc('TESTFILE'
    F                                       extfile('TESTFILE')
    F                                       extmbr('MBR05')
    F                                       rename(TESTFILER:MBR05R)
    F                                       prefix(m5_)
03  FTESTFILEM6O    E             DISK    extdesc('TESTFILE')
    F                                       extfile('TESTFILE')
    F                                       extmbr('MBR06')
    F                                       rename(TESTFILER:MBR06R)
    F                                       prefix(m6_)
     /free

 

Use SQL

The question did not say that I could not use SQL within my RPGLE program. I cannot stipulate the member I want to use in the common SQL statements. If I have a multi member file DB2 of i allows me to create an Alias for that member using the CREATE ALIAS statement. Once the Alias is created I can use it as I would any other table or file.

01  exec sql SET OPTION COMMIT = *NONE ;

02  exec sql CREATE ALIAS QTEMP.TESTFILE_MBR05
                      FOR TESTFILE(MBR05) ;

03  exec sql INSERT INTO QTEMP.TESTFILE_MBR05
               VALUES('3','5th member') ;

04  exec sql DROP ALIAS QTEMP.TESTFILE_MBR05 ;


05  exec sql CREATE ALIAS QTEMP.TESTFILE_MBR06
                      FOR TESTFILE(MBR06) ;

06  exec sql INSERT INTO QTEMP.TESTFILE_MBR06
               VALUES('3','6th member') ;

07  exec sql DROP ALIAS QTEMP.TESTFILE_MBR06 ;

08  *inlr = *on ;

Line 1: I am using the SET OPTION statement to ensure that this program does not use commitment control.

Line 2: Here I define my Alias. I always define my Aliases as <file name>_<member name> as I think it is leaves no confusion which file and member this Alias is built for. I also define them in QTEMP, so that this Alias is only for this job.

Line 3: This is just a regular SQL Insert statement using the Alias I just created.

Line 4: Once I am done with the Alias I always drop (delete) them.

Lines 5 – 7: Are just the same as the lines 2 – 4 but for the sixth member.

 

After all three programs have run if I look in MBR05 and MBR06 I see the records I wrote/inserted into them:

       F1  F2
000001 1   5th member
000002 2   5th member
000003 3   5th member
       F1  F2
000001 1   6th member
000002 2   6th member
000003 3   6th member

 

If you are on an interview and you asked this question now you can offer these three solutions to the interviewer to show that you know more than one way to handle multi member files.

 

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

13 comments:

  1. With "Define the member in the files' definition", it's not practical to hard-code the member name in the RPG program's file specifications. People will want to pass the member name as a parameter. You can specify EXTMBR(pCompany) (or whatever it is that defines the member).

    ReplyDelete
    Replies
    1. I meant to add a link to an earlier post that gave an example of how to use a variable name for the member name in the EXTMBR keyword, Useful keywords for your file defintions. My bad :(

      Delete
  2. In "Use SQL", it might be worthy of noting that you can create an alias for an RPG program to use.

    I.E. If an RPG program is using a file named TESTFILE,
    CREATE ALIAS QTEMP.TESTFILE_MBR06 FOR TESTFILE(MBR06)
    will cause the RPG program to use member MBR06.

    ReplyDelete
    Replies
    1. Glenn, your way is the best way ;-). For 2017 and beyond the need and use of native RPG I/O is no longer there.

      Delete
  3. Let's add one more way to perform member I/O . Procedure wrapper around IBM i QC2IO functions.

    https://rd.radile.com/rdweb/info2/rio011.html

    ReplyDelete
  4. It's probably worth mentioning that if a job uses a combination of OVRDBF with EXTFILE/EXTMBR, then the OVRDBF still has precedence.

    Ringer

    ReplyDelete
  5. Thanks for taking the time to explain these ways to do that.

    ReplyDelete
  6. You could also shovel a pile of money at it and buy the optional part of 57##SS1 for DB2 multisystem. This will automatically stick rows into a particular member based on data. For example, break down members by year of a transaction date. You create the table with SQL and use the partitioning clause. Then, in RPG you shouldn't have to tell it which member. It will figure it out.
    Rob

    ReplyDelete
  7. Does ovrdbf work with SQL, if overriding a particular member. I am aware that it fails when ovrdbf is used to refer all members before using SQL. But does this fail even when overriding to a particular member only?

    ReplyDelete
  8. Can we dynamically change member in RPGLE ?.

    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.