Tailoring for Performance › Using Join Logicals

Using Join Logicals

A join logical file is a logical view of the physical files upon which it is based. It also enables data from another physical file or a related (or joined-to) physical file to be read. The join is performed when the records in the based-on physical are read. One or more fields in the based-on physical are matched with fields in each joined-to physical and data from each matched record is read. The join logical is composed of only one record format, which contains fields from the based-on file and the joined-to physical files. The join fields are read-only capable and cannot be updated through the join logical.

An access path generates and compiles as a join logical if there are virtual field entries on the access path format entries. The physical file that contains the virtual fields is joined to the primary files using the keys of the file-to-file relations to provide the match. These relations can be Owned by, Refers to, or Extended by.

The operating system does the work. The join (matching records through common fields) is implemented using a machine interface (I/O) routine to read from the joined-to physical files. You can define join logicals that join through several physical files. The maximum number of physical files allowed in a join file definition (direct joins or chained joins) is 32. A field that is brought across more than six (or as few as three or four) chained joins actually loses the performance benefit because it requires many I/O routines to obtain the data. Consequently, obtaining data across several joins is not recommended.

The use of join logicals is efficient only when the data that is being read is actually needed. If the data is not required, the operating system is doing unnecessary I/O to other files. In terms of internal active indexes, the join logical shares the active indexes of the based-on file just as with access path types.

Maintenance can be specified as IMMED, DLY, or REBLD. Only dynamic select/omit maintenance can be specified on CA 2E join logical files. In terms of performance, this has some significant consequences:

• Where it may be better to have static select/omit maintenance, you can drop the virtual field so that it can be specified. The additional data can be retrieved using your own reads to the secondary file.
• The operating system reads the joined-to data before applying the selection because the selection itself could be on a joined-to virtual field. This compounds the performance problem particularly where there is I/O to several different physical files.