|Join two physical files to create Join logical file in AS400|
In this blog, we will be seeing an example that tells us how to create a Join Logical file (JLF) to join two physical files (PF).
Create first physical file PF1
PF1 has 2 fields namely ROLLNO of 10 chars and STUNAME of 15 chars respectively and record format name as RPF1.Use the CRTPF command to create the object for file PF1.
A R RPF1 A ROLLNO 10A A STUNAME 15A
Create second physical file PF2
PF2 has 2 fields namely ROLLNO of 10 chars and CLASS of 2 numeric respectively and record format name as RPF2.Use the CRTPF command to create the object for file PF2.
A R RPF2 A ROLLNO 10A A CLASS 2P 0
Create a Join Logical file (JLF)
JLF1 would have 3 fields namely ROLLNO, STUNAME, and CLASS from files PF1 and PF2, where the ROLLNO field is common in both the physical files PF1 and PF2.
A R RJLF1 JFILE(PF1 PF2) A J JOIN(PF1 PF2) A JFLD(ROLLNO ROLLNO) A ROLLNO JREF(PF1) A STUNAME A CLASS
Data in PF1
ROLLNO STUNAME 1 NAME1 2 NAME2 ******** End of data ********
Data in PF2
ROLLNO CLASS 1 1 2 2 ******** End of data ********
Data in join logical file (JLF1)
ROLLNO STUNAME CLASS 1 NAME1 1 2 NAME2 2 ******** End of data ********
R denotes the Record format of the join logical file. Here in this example, JLF1 has a record format name as RJLF1.
JFILE keyword accepts parameters as file names. Here you must specify at least two physical files (PF) where the first file is the primary file and the second file is the secondary file.
J denotes the beginning of the Join specification. At least one Join specification must be defined in the Join logical file (JLF). The next J would be on the next line.
JOIN denotes that which two files will be joined using the JOIN keyword. Let me tell you if only two physical files are joined then this JOIN keyword is optional to use in Join logical file (JLF).
JFLD keyword denotes join fields that will join rows from the physical files specified on the JOIN keyword.JFLD keyword must be specified at least once for each join keyword. Let me tell you the join fields are those fields that are common to both the physical files. All the fields must be of the same type, the length specified on the JFLD if they are not character types.
At least one field name must be specified from the physical file and you can use keywords such as CONCAT, SST, RENAME here.
JREF keyword is used for the field names which must specify that from which files this field is referred from in Join logical file (JLF). Here, in this example, ROLLNO fields are present in both the files PF1 and PF2 and JLF must know in advance that from where the field comes from.
Please note that in this example PF1 and PF2 do not have key fields. So Join logical file (JLF) is an arrival sequence file in this case, but in case we have defined any key fields then the Join logical file would be an indexed file(keyed sequence).
Note and Warning
Using the Join Logical file, the program will do only one read operation in JLF instead of two reads in two files.
You cannot change a physical file using join logical file
Points to remember
All records matched in Primary and Secondary file
All the rows in the primary file get selected for reading.
Record missing in Secondary file and JDFTVAL keyword not used by Join logical file
In this case, only matched record in the primary file with the secondary file gets selected for reading.
Record missing in the Secondary file and JDFTVAL keyword used by Join logical file
In this case, All the records in the primary file get selected for reading even if any record missing in the secondary file, and for the fields for which the record is missing will be set as blanks and zeros for character and numeric fields respectively.
An extra record present in the Secondary file
That extra record won't get selected for reading until and unless that record does not present in the Primary file specified in the JOIN specification.
- Logical file in AS400
- Non-Join Logical file in AS400
- Join Logical file in AS400
- Join more than one physical file field to create Join logical file in AS400
- Use JDUPSEQ keyword to arrange duplicate records in secondary file in join logical file in AS400
- Join fields with different attributes to create a Join logical file in AS400
- Fields that never appear in Record format in Join Logical File in AS400
- Specify the key fields in the Join Logical File in AS400
- Joining three or more physical files to create Join Logical File in AS400
- Joining physical file to itself in Join Logical File in AS400
- Select and Omit criteria in Logical file in AS400