IBM i5/iSeries Primer(c) Concepts and Techniques for Programmers, Administrators, and Sys[... ]ators

Two physical files were defined at the beginning of this chapter: a vendor master (VNDMST) and a vendor contact (VNDCONT) file. Join the two files using the vendor number (common in both files) as the join field. See Figure 22.10.

Figure 22.10: Joining physical files using DDS and logical files.

Take note of the following points:

• The record definition contains the keyword JFILE that lists the files being joined.

• Immediately following is a line with a ‘J’ in 17. This line defines the method for joining the files. The JOIN keyword lists the two files being joined. The next line indicates which fields are used for joining. In this case, fields VNVEND and VCVEND are being used for joining. Because the JOIN keyword has VNDMST first, the VNVEND in JFLD (which is also first) is assumed to be coming from VNDMST. The same can be said about VCVEND, but it is coming from the second file.

• As opposed to simple logical files, in which you don't have to specify the fields that make up the record, join files do require that you specify the fields. In this case, you have included every field from both files (except for VNDUE from VNDMST because you don't need it). There is no need to include every field; you only need to include the fields to process the task at hand.

• This logical file is keyed by vendor number. Because no UNIQUE keyword is present, it can contain multiple records with the same key value.

You may be wondering why you have to repeat the names of the files being joined, first in the JFILE keyword, then in JOIN. You must do this because you can join more than two files. When you join more than two files, you name them all in JFILE (three, four, or however many). But then, each JOIN keyword can only contain two files. The example below clarifies this concept.

Joining Three Files

Imagine that a third physical file, vendor activity file (VNDACT), contains vendor information and also includes a vendor number field, called VAVEND. You can join all three files as shown in Figure 22.11.

Figure 22.11: Joining three physical files using DDS and logical files.

This example shows the difference between the JFILE and the JOIN keywords. In JFILE, you list all files being joined, regardless of how many are present.

Then, you need to join all files in groups of two. The first ‘J’ specification (‘J’ in 17, keywords JOIN and JFLD) joins VNDMST with VNDCONT by vendor number.

The second ‘J’ specification also joins by vendor number VNDCONT (which is already joined to VNDMST) with VNDACT. The net result is that all three files are joined by vendor number. The list of fields can include fields from any or all three files.