Joining i5/os Files Using OPNQRYF – may be known as os400 dds

another snippet from an old document I found in a dim dark and hazy corner of an even older HDD… rather than let it disintegrate, here it is migrating to the web just in the (extreme) off chance that it may be useful to someone out there. Hey.. if YOU are reading this then its done its job 😉

Detailed Description :

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

FieldsTypeLength

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.

1 OPEN *ALL *ONDEMAND

2 POINT FILE(JOINFILE) TOFILE(FILE1)

3 DEF_LIST #LIST (#FIELD1 #FIELD2 #FIELD3 #FIELD4)

4 ********** Execute the OPNQRYF

5 EXEC_OS400 ('OPNQRYF FILE(FILE1 FILE2 FILE3) FORMAT(JOINFILE) JFLD((FILE1/FIELD1 FILE2/FIELD1)) ((FILE1/FIELD1FILE3/FIELD1)) MAPFLD((FIELD1’FILE1/FIELD1')(@@UPID'FILE1/@@UPID'))

6 ********** Open JOINFILE immediately so LANSA will not do a "SETLL"

7 OPEN FILE(JOINFILE) USE_OPTION(*IMMEDIATE)

8 SELECT FIELDS(#LIST) FROM_FILE(JOINFILE)

9 ADD_ENTRY #LIST

10 ENDSELECT

11 CLOSE JOINFILE

12 ********** Close file created by OPNQRYF

13 EXEC_OS400 COMMAND('CLOF FILE1')

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