How Dynamic Is OPNQRYF?

Hey, Ted:

Several users run Program A to add records to a file. One user runs Program B to create a report from the file. I would like to use OPNQRYF to sort the records before Program B runs.

Will OPNQRYF read the records that are added to the file after Program B has started running?

--Barney

It will in some cases, but don't count on it. The system reads a block of records at a time into memory. Every time Program B issues a read operation, the system gives it the next record in memory. If there are no more records in memory, the system reads another block of records.

If the key field value of an added record falls within a block that has been read, OPNQRYF will not pick up the added record. If the key field value places the record in the block that has not yet been read, OPNQRYF will retrieve the added record.

Here's an example you can play with. Create a little database file.

create table mylib/mydata ( 
     keyfld dec (3),        
     datafld char (20),     
     primary key (keyfld))

Load three records into the file.

insert into mylib/mydata values(100,'AAAAA')
insert into mylib/mydata values(300,'CCCCC')
insert into mylib/mydata values(500,'EEEEE')

Write a short CL program that runs OPNQRYF over the file. (I called it PGMBCL.)

pgm  
ovrdbf  mydata share(*yes) 
opnqryf file(mydata) keyfld((datafld))
call    pgmbrg     
clof    mydata    
dltovr  mydata              
endpgm

Write a short RPG program to read and print the file. (I called it PGMBRG.)

Fmydata    ip   e     k disk    rename(mydata: myrec)
Fqsysprt   o    f 132   printer       
                                                           
D Counter         s              3  0        
C                   eval      *in01 = *on    
C                   eval      Counter = Counter + 1
C                   if        Counter = 1     
C     'Add rec now' dsply 
C                   endif 
C  

Oqsysprt   d    01                  1
O                       counter       4 
O                       keyfld           +0001
O                       datafld          +0001

Make sure these programs run in the same activation group. I used Create CL Program (CRTCLPGM) and Create Bound RPG Program (CRTBNDRPG) to compile them. Both ran in the default activation group.

The RPG program pauses while processing the first record, giving you time to insert a new record in the file from another session. After you insert the new record, press Enter in the first session to make the RPG program continue after the dsplay operation.

If you enter a record whose key value is greater than the other key values, the RPG program reads and prints the record. Since OPNQRYF reads the data in DATAFLD sequence, the value FFFFF places this data after the first three records.

insert into mylib/mydata values(600,'FFFFF')

However, OPNQRYF will not retrieve records that are inserted within the original three.

insert into mylib/mydata values(200,'BBBBB')
insert into mylib/mydata values(400,'DDDDD')

Notice that both of these records have values greater than the first record, whose key value was AAAAA, but less than the other two original records. At this point, all three original records had been read into memory by the system's first block read operation. For that reason, OPNQRYF does not access these records, even though the key values are less than the key value of the second record.

Now that you understand what's going on, let me tell you that this is not just an OPNQRYF phenomenon. It is true of any program that opens an open data path for input. Run the RPG program by itself and you will experience the same behavior. The RPG program will get an added record if the KEYFLD value is greater than the key field values of the original three records.

Thanks to Ritchie Nyland of IBM Rochester for verifying that I had my facts straight.

--Ted

Sponsored By ADVANCED SYSTEMS CONCEPTS

ABSTRACT DELIVERS: - Cross-Referencing - - Impact Analysis - - Flowcharting - - File Re-creation - - Documentation - - Field expansion - Follow the link below to discover why you need to replace your existing iSeries cross-reference tool with ABSTRACT Read More