Wednesday, November 4, 2015

Using Relative Record Number with data files in RPG

relative record number, rrn, in rpg using recno and inzpfm

Lately I have received a lot of emails and messages about using Relative Record Numbers, RRN, with data files in RPG programs. So many I have decided to write this post so I can refer people here rather than answer their questions individually.

Before I get started I just want to say I can think of no good business reason to use RRN to get and manipulate records from Physical or Logical files. The best I can recall the last time I wrote a program using them was way back in time in the late 1980s on an IBM System/36, one of the ancestors of the AS400 and IBM i. If someone asked me today to write a program using RRN I would ask them "Why?" and need a very good reason why.

The Relative Record Number identifies which position in a file the record is in. If, for example, a record has the RRN of 10 there does not have to be nine records before it. When a record is deleted its space in the file is retained, and it is not "freed" until the file is reorganized using the RGZPFM command, or if the file is set to reuse deleted records a new record is added to the file.

To illustrate my examples I created a very simple file:

  A          R TESTFILER
  A            FLD001         1A
  A          K FLD001

Into which I entered the letters of the first row of letters on my keyboard:

     FLD001
  01   Q
  02   W
  03   E
  04   R
  05   T
  06   Y
  07   U
  08   I
  09   O
  10   P

To be able to use the RRN I need to use the RECNO keyword in the file definition/specification, which contains a numeric field for the number of the record:

  dcl-f TESTFILE disk recno(RRN) ;
  dcl-s RRN packed(10) ;


  FFilename++IPEASF.....L.....A.Device+.Keywords+++++
  TESTFILE   IF   E             DISK    recno(RRN)

  DName+++++++++++ETDsFrom+++To/L+++IDc.Keywords+++++
  D RRN             S             10  0

Let me start with a simple program to read and display the RRN of the records in the file. My program looks like this:

01  dcl-f TESTFILE disk recno(RRN) ;
02  dcl-s RRN packed(10) ;

03  dow (1 = 1) ;
04    read TESTFILER ;
05    if (%eof) ;
06      leave ;
07    endif ;
08    dsply ('Key = ' + FLD001 + ' - ' + %char(RRN)) ;
09  enddo ;

10  *inlr = *on ;

The output displayed by the Display Message operation, DSPLY, looks like:

DSPLY  Key = Q - 1
DSPLY  Key = W - 2
DSPLY  Key = E - 3
DSPLY  Key = R - 4
DSPLY  Key = T - 5
DSPLY  Key = Y - 6
DSPLY  Key = U - 7
DSPLY  Key = I - 8
DSPLY  Key = O - 9
DSPLY  Key = P - 10

I can also Chain to the file to retrieve a specific record, for example the seventh:

01  chain 7 TESTFILE ;                      
02  if (%found) ;                          
03    dsply (FLD001 + ' - ' + %char(RRN)) ;
04  endif ;                                

Which gives me:

  DSPLY  Key = U - 7

I can easily delete the seventh record:

01  dcl-f TESTFILE disk recno(RRN) usage(*input:*delete) ;

02  dcl-s RRN packed(10) ;

03  chain 7 TESTFILER ;
04  if (%found) ;
05    delete TESTFILER ;
06  endif ;

07  *inlr = *on ;

For those of you unfamiliar with the USAGE(*INPUT:*DELETE) part of the file definition you should read my post File definition in RPG all free.

Now when I run the program to list all of the records with their RRN the seventh record is missing:

  DSPLY  Key = Y - 6
  DSPLY  Key = I - 8

The seventh record is not really missing it is just flagged as deleted.

I can also change a record using the RRN. In the example below I am going to replace the value in the fifth record with 'A':

01  RRN = 5 ;
02  chain RRN TESTFILER ;
03  FLD001 = 'A' ;
04  update TESTFILER ;

  DSPLY  Key = A – 5

My file only contains ten records, you would think if I wanted to add another record I could just do:

01  dcl-f TESTFILE disk recno(RRN) usage(*output) ;

02  dcl-s RRN packed(10) ;

03  RRN = 11 ;
04  FLD001 = 'A' ;
05  write TESTFILER ;

06  *inlr = *on ;

When I run the above program I get an error message:

  I/O error CPF5006 was detected in file TESTFILE (C G D F).

It will not allow me to add the eleventh record. What I need to do is add records to the file outside of the program. Fortunately there is a CL command to do this: Initialize Physical Member, INZPFM. If there is already data in the file it will add new records to the file:

  INZPFM FILE(MYLIB/TESTFILE) RECORDS(*DLT) TOTRCDS(20)

As I already have 10 records in the file and I want to add 10 more the total records parameter needs to be 20 (= 10 existing + 10 new). There is a default value for this parameter of *NXTINCR, to increase the file size by one increment, but as I always create my files with a size of *NOMAX the default is not allowed.

The RECORDS parameters allows me to say do I want to add blank records, RECORDS(*DFT), or deleted ones , RECORDS(*DLT). As both blank and deleted records take up space there is no size advantage to using either. I just prefer to add deleted records as I can then write new records to the file when needed. With blank records I now have 20 active records, my original ten and ten blank ones.

When I run the INZPFM I will receive a message informing of the number of new records added.

Message ID . . . . . . :   CPC3102       Severity . . . . . . . :   00
Message type . . . . . :   Information

Message . . . . :   Member TESTFILE initialized with 10 records.
Cause . . . . . :   Member TESTFILE file TESTFILE in library MYLIB was
  initialized with 10 records.

Now I can a new record to my file, and I don't have to write it to the 11th record. I am going to write it to the 17th.

01  dcl-f TESTFILE disk recno(RRN) usage(*output) ;

02  dcl-s RRN packed(10) ;

03  RRN = 17 ;
04  FLD001 = 'V' ;
05  write TESTFILER ;

06  *inlr = *on ;

When I run the program to display all the records I can see my new record with the RRN of 17:

  DSPLY  Key = Q - 1
  DSPLY  Key = W - 2
  DSPLY  Key = E - 3
  DSPLY  Key = R - 4
  DSPLY  Key = T - 5
  DSPLY  Key = Y - 6
  DSPLY  Key = U - 7
  DSPLY  Key = I - 8
  DSPLY  Key = O - 9
  DSPLY  Key = P - 10
  DSPLY  Key = V - 17

I just want to state a few things, which in my opinion should be self evident, but I am doing here just for the record:

  1. I cannot use zero for RRN.
  2. I cannot have two or more records with the same RRN.

 

How about you just want to see the RRN of the records read without all of malarkey described above. I can use an Information Data Structure, INFDS, with the file.

01  dcl-f TESTFILE disk keyed infds(FileDs) ;

02  dcl-ds FileDs qualified ;
03    RRN uns(10) pos(397) ;
04  end-ds ;

05  dow (1 = 1) ;
06    read TESTFILER ;
07    if (%eof) ;
08      leave ;
09    endif ;

10    dsply ('Key = ' + FLD001 + ' - ' + %char(FileDs.RRN)) ;
11  enddo ;

12  *inlr = *on ;

The INFDS is defined with the file, line 1, and I have given it the totally unimaginative name of "FileDs". The data structure contains a whole lot of information, but I am only interested in the RRN. The RRN is a ten long unsigned integer variable that starts at position 397, line 3. I have chosen to qualify my data structure subfields, see line 2.

Notice that I have defined the file as KEYED, therefore, when I read the file I am reading it in keyed order rather than by RRN.

Now I can read my file, line 6, and display the value of my key field along with the RRN, line 10.

The output shows the fields in key order with their RRN.

  DSPLY  Key = E - 3
  DSPLY  Key = I - 8
  DSPLY  Key = O - 9
  DSPLY  Key = P - 10
  DSPLY  Key = Q - 1
  DSPLY  Key = R - 4
  DSPLY  Key = T - 5
  DSPLY  Key = U - 7
  DSPLY  Key = W - 2
  DSPLY  Key = Y - 6

The equivalent definitions using fixed format would be:

    FFilename++IPEASF.....L.....A.Device+.Keywords++++++
01  FTESTFILE   IF   E           K DISK    infds(FileDS)

    DName+++++++++++ETDsFrom+++To/L+++IDc.Keywords+
02  D FileDs          DS                  qualified
03  D  RRN                  397    400U 0

 

You can learn more about on the IBM website:

 

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

8 comments:

  1. I agree I have rarely used RRN inside my programs but it is good to know you can pull it in.

    ReplyDelete
  2. I agree, it has been a very long time since I last wrote a program using RRN. There will be a scenario where it is relevant but I can't think if one in everyday programming.

    ReplyDelete
  3. The oldest trick in SQL is using RRN to delete duplicate records.

    DELETE FROM FILEA a
    WHERE RRN(FILEA) >
    (SELECT RRN(FILEA)
    FROM FILEA B
    WHERE A.KEY=B.KEY
    )

    I am sure everyone has used it a 1000 times in the past 100 years. If you prefer writing purely in RPG, then the above can be converted into RPG code.

    The same way for OLAP purpose if we do not want to use cool SQL trick like "row_number over" trick, we can write RPG code where smallest RRN can be used to determine the first record of the group. etc etc etc.

    ReplyDelete
  4. Oh yes, there are many Reasons to use the rrn !
    ------------------------------------------------------------------------
    In Net.Data i get the rrn in a link to update details, an then i access with sql and "where rrn=variable" with the rrn !!!
    its great, faster than any other "where" in sql !

    ReplyDelete
  5. Can I speak to the use of specialized questions in interviews. I benefit when this is done to me, because I know an excruciating amount of RPG specialized techniques of varying degrees of obscurity, and I am always tempted to ask some of it of others. But it dawned on me how ridiculous this was when my perfectly competent coworker overheard me interviewing someone, and said he would have flunked the whole thing. The difference between treasure and trash in programmers is never due to some special technique that might be totally avoided in actual applications.

    ReplyDelete
    Replies
    1. Allan Roberto GarciaNovember 6, 2015 at 8:39 PM

      That comment of yours just made my day Lynne. Your words ring so ever true. I have decided not so long ago that that attitude of having a thirst for knowledge is a much, much better gauge of competence.

      Delete
  6. Writing a transaction file that requires the record count and total value on the header record... write the header record initialized. write the transactions and then chain back to first record to update the counts and value.

    ReplyDelete
  7. This article overlooks the single most useful application of RRN.

    When you read a keyed record, the RRN of the physical record is placed in the file information status data structure (be sure you are processing randomly: sequential files access only updates the RRN when a block read occurs.) Of course the lib/file.mbr is also put there but for now we can assume a simple file.

    Requested records are loaded into a subfile for mainenance (or drilldown.)

    Put the RRN of the physical record in a hidden field of each subfile record.

    Add a synonym of the physical file to the program, renaming the record (and prefixing the fields.) It will be processed by RRN.

    When the subfile records are processed for update, maintain the table using the RRN synonym.

    What does this do for you??

    You may remember all those stupid sequence fields you have struggled to maintain so a unique key is available??? They are completely unnecessary. EVERY record in the subfile could have IDENTICAL KEYs and file maintenance will still be flawless (as soon as you deal with locks, etc.)

    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.