Joining files using OPNQRYF

Date:Archived
Product/Release:LANSA for the AS/400
Abstract:An example of how to join files using OPNQRYF
Submitted By:LANSA Technical Support

Some customers have asked us for an Example of LANSA reading a join file created by OPNQRYF

The example below uses OPNQRYF to join the following files:

Filename: FILE1
                Fields Type Length
                FIELD1 Alpha 5 (File key)
                FIELD2 Alpha 5

Filename: FILE2
                Fields Type Length
                FIELD1 Alpha 5 (File key)
                FIELD3 Alpha 5

Filename: FILE3
                Fields Type Length
                FIELD1 Alpha 5 (File key)
                FIELD4 Alpha 5

An extra file is needed for OPNQRYF to use as a "result" record format for the join...

Filename: JOINFILE
                Fields Type Length
                FIELD1 Alpha 5 (File key)
                FIELD2 Alpha 5
                FIELD3 Alpha 5
                FIELD4 Alpha 5

Important notes when defining JOINFILE...

  • When defining this file to LANSA, use the data base attributes screen to define the file as SHARE..YES. Also put "SHARE(*YES)" at the bottom of the screen in the CRTPF parameters section.
  • Make sure JOINFILE has been defined as having at least one key. The key does not have to match the keys of the result file produced by OPNQRYF.
  • All of the above files were created using LANSA so they all will have the field @@UPID automatically added to the record formats. Once the above files are compiled, a function can be written to execute the OPNQRYF and read the file.
OPEN *ALL *ONDEMAND
POINT FILE(JOINFILE) TOFILE(FILE1)
DEF_LIST #LIST (#FIELD1 #FIELD2 #FIELD3 #FIELD4)
* ********* Execute the OPNQRYF
EXEC_OS400 ('OPNQRYF FILE(FILE1 FILE2 FILE3) FORMAT (JOINFILE) JFLD((FILE1/FIELD1 FILE2/FIELD1)) ((FILE1/FIELD1FILE3/FIELD1)) MAPFLD((FIELD1'FILE1/FIELD1')(@@UPID'FILE1/@@UPID'))
* ********* Open JOINFILE immediately so LANSA will
* ********* not do a "SETLL"
OPEN FILE(JOINFILE) USE_OPTION(*IMMEDIATE)
SELECT FIELDS(#LIST) FROM_FILE(JOINFILE)
ADD_ENTRY #LIST
ENDSELECT
CLOSE JOINFILE
* ********* Close file created by OPNQRYF
EXEC_OS400 COMMAND('CLOF FILE1')
DISPLAY BROWSELIST(#LIST)

Important notes about the above function:

  • The JOINFILE must be opened "*ONDEMAND" so that it is not opened before the OPNQRYF is executed. See statement 1.
  • OPNQRYF will create a file that is exactly the same as the file specified in the FORMAT() parameter but the actual file name will be the name of the first file specified in the FILE() parameter. For this reason, the function SELECTs from JOINFILE but JOINFILE is POINTed to FILE1. See statement 2.
  • Just before the JOINFILE is read, open it *IMMEDIATEly. When this is done, LANSA knows the file has just been opened so it does not attempt to use SETLL (RPG operation) to position the file to the beginning before the first read. See statement 7. (Using RPG's SETLL on a file created by OPNQRYF is invalid and it will cause the function to abort).
  • After reading the JOINFILE, CLOSE it and then use the CL command "CLOF" to close the file (FILE1) created by OPNQRYF. See statement 13.

There is another solution, by following the instructions detailed below LANSA can be tricked into reading the join logical file without using OPNQRYF:

  1. Create a physical file with the same name and record format as the join logical. This will have to be created in a different library from the join logical.
  2. Load this file as an 'OTHER' file into LANSA.
  3. Compile the file.
  4. Rename the physical file created outside of LANSA or delete the file. It is probably best to rename it as it may be necessary to make changes and reload it at a later stage.
  5. Do a CHGLF on the join file LVLVHK(*NO).
  6. Make sure that the join logical file is in the library list.
  7. It is now possible to do reads of the join logical file via the LANSA I/O module.