|Specify the key fields in the 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 specify the key fields in the Join logical file (JLF).
Rules for specifying the key fields in Join logical file
- The key field in JLF must be from the Primary file i.e. first file.
- The key field in JLF must be the fields defined as Neither (N) fields from the primary physical file.
Create first physical file PFA1
PFA1 has 2 fields namely ROLLNO of 2 packed decimal and STUNAME of 15 chars respectively and record format name as RPFA1.Use the CRTPF command to create the object for file PFA1.
A R RPFA1 A ROLLNO 2P 0 A STUNAME 15A
Create a second physical file PFA2
PFA2 has 2 fields namely ROLLNO of 2 zoned decimals and CLASS of 2 numeric respectively and record format name as RPFA2.Use the CRTPF command to create the object for file PFA2.
A R RPFA2 A ROLLNO 2S 0 A CLASS 2P 0
Create a Join Logical file (JLF) with a key field.
JLFA1 would have 3 fields namely ROLLNO, STUNAME, and CLASS from files PFA1 and PFA2, where the ROLLNO field is common in both the physical files PFA1 and PFA2.
A R RJLFA1 JFILE(PFA1 PFA2) A J JOIN(PFA1 PFA2) A JFLD(ROLLNO ROLLNO) A ROLLNO S JREF(1) A STUNAME A CLASS A K ROLLNO
In this example, the ROLLNO field is used to join two files having different attributes i.e. zoned and packed and this ROLLNO field is also defined as the key field from the primary file PFA1.
Data in PFA1
ROLLNO STUNAME 1 NAME1 2 NAME2 ******** End of data ********
Data in PFA2
ROLLNO CLASS 1 1 2 1 ******** End of data ********
Data in join logical file (JLFA1) - ROLLNO field is not available for reading
STUNAME CLASS NAME1 1 NAME2 1 ******** End of data ********
R denotes the Record format of the join logical file. Here in this example, JLFA1 has a record format name as RJLFA1.
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.
Field Name with data type and Use as N
We are redefining a file field referred to in JLF. Here, we are redefining ROLLNO keyword as zoned since we are referring to 1st file (primary file) having a packed decimal field that is joined with ROLLNO from another file (secondary file) having a zoned decimal field. So the fields to join must have the same attributes. Therefore, we redefine the join field in Join logical file (JLF). Also, this field is set for use as N means neither for Input or Output. Som this field will not appear in the record format during reading the JLF.
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 PFA1 and PFA2 and JLF must know in advance that from where the field comes from. We can specify either file name or number. Here we refer to the field from the primary file as the number is 1.
Please note that in this example PFA1 and PFA2 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
Click the below button to navigate to the Note and Warning pageNotes and Warning
Points to remember
Click the below button to navigate to the Points to remember pagePoints to remember
- Logical file in AS400
- Non-Join Logical file in AS400
- Join Logical file in AS400
- 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
- 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
Post a Comment