 |
| Join Logical file in AS400 |
Introduction
- A Join Logical file consists of fields from more than one physical file in one record format.
- A join logical file can only have one record format.
- We cannot delete, update and write records to the physical file using the join logical file i.e. JLF.
- We cannot use commitment control with the Join logical file (JLF).
- We cannot use the DFU to display the Join logical file (JLF).
- The first file in JLF is the primary file and all other files would be secondary files.
- Try to keep the file having the most number of records as the primary file in the JLF for the performance per se.
- We can rename the field using RENAME keyword in JLF.
- We can concatenate the fields using the CONCAT keyword in JLF.
- We can even create a new field by doing substring to a field using the SST keyword in JLF.
- Even we can change the fields attribute in the JLF.
- Key fields cannot be from the secondary files.
- With the Select and Omit fields, we can specify DYNSLT (Dynamic Select) keyword at the file level.
Level of Entries to consider while creating JLF
Optional JDFTVL keyword
Use this optional keyword in your Join Logical file at the file level when you want to get a row for each entry in the primary file even if there are no matching records present in the secondary files.
Optional JDUPSEQ keyword
Use this optional keyword in your Join Logical file at the Join level when you have duplicate values for the fields which are not joined and you want them to be sorted.
Using JOIN and JFLD keyword
For each secondary file use the JOIN specification to join the primary file with the secondary file and use the JFLD keyword to join fields of primary and secondary files. These are JOIN level entries.
Using JFILE keyword
Specify the physical file name in the JFILE keyword as a parameter. The first file is a PRIMARY file and all other files are SECONDARY files. This keyword is record level entry
Using JREF keyword
Specify the physical file name in the JREF keyword as a parameter for those files having the same field names in different physical files. This is a field-level optional keyword.
Examples
There are several examples for Join Logical files (JLF)
Join two physical files to create 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