LOGICAL FILES

Sometimes it is necessary to process a portion or subset of a database file. A logical file can be used to create a subset of the file. A logical file is a view of a physical file. It can contain some or all of the fields and records of the physical file. Additionally, an access path (i.e., key fields) can be specified for the logical file.

Logical files don't contain data; they are vehicles through which data from physical files is supplied to an application program. Application programs treat both physical and logical files exactly the same. It is the operating system's responsibility to maintain the proper link between the logical file and the physical file's data.

As mentioned, logical files represent a subset of a physical file. For example, if an application requires only two fields of a five-field physical file, a logical file can be created to present only those two fields to the program. This technique is referred to as mapping. Table 11.5 shows the relationship between the physical file data and a logical file based on the physical file.

Logical files are created with DDS source in the same manner as physical files. Logical file DDS, however, contains only the field names needed for the subset. For example, the DDS necessary to create the logical file listed in Table 11.5 is featured in Figure 11.8.

Figure 11.8: Logical file DDS.

In Figure 11.8, line 1 defines the record format name for the logical file and the physical file on which the logical is based. The PFILE keyword identifies the based-on physical file.

Lines 2 and 3 identify the fields that are included in the logical file. The field properties (e.g., type, length) don't need to be specified. The properties of the fields in the physical file are inherited by the field names in the logical file. The field properties can, however, be overridden in the logical file.

Line 4 defines the key field that is used as the access path for the logical file. If the key field of the logical file is the same as that of the physical file or some other logical file, the access path is shared. When an access path is shared, only one copy of the access path exists. This saves time when the access path is built and improves performance when records are added to the file.

As stated, the DDS shown in Figure 11.9 is for a logical file that contains a subset of fields of the physical file. If a subset of records is required, select/omit specifications can be added to the DDS for the logical file. Table 11.6 lists DDS for a typical logical file with select/omit statements. Although both SELECT and OMIT operations are supported, traditionally only SELECT statements are used.

Table 11.6: Subset of Fields and Records of a Logical File

ACTNBR	CSTCTY
05381	Geneva
05320	Lemont
05340	Oak Brook

 .....Aan01n02n03R.Format++++.Len++TDPURowColKeywords++++++++++++++++++ 0001 A                                      DYNSLT 0002 A          R CUSTREC                   PFILE(CUSTMAST) 0003 A            ACTNBR 0004 A            CSTCTY 0005 A          K ACTNBR 0006 A          S ACTNBR                    COMP(GT 5000) 

Figure 11.9: Logical file DDS with dynamic SELECT/OMIT.

Line 1 specifies that the select/omit specifications are dynamic. In other words, the select/omit is not combined with the access path but is performed by the operating system as the records are read by a high-level language.

Line 6 contains the select/omit specification. The field ACTNBR must be greater than 5,000 for the record to be included in this logical file. The resulting view of the physical file's data is listed in Table 11.6. Note that account number 1207 is not included because its ACTNBR doesn't match the select/omit criteria.

Join Logical Files

The topic of relational database and join logical files has filled several volumes. AS/400 join logical files are similar to logical files in that they provide a subset of a physical file. However, join logical files can represent a subset of multiple physical files.

An equi-join file is a join logical file that joins two or more files by a common value. For example, an inventory file (consisting of part number, quantity on-hand, and part description) and a customer-order history file (consisting of customer number, part number, and quantity ordered) can be joined by part number, forming a join-logical view of the two files.

When files are joined, a single join record is created. The join record can contain any or all of the fields from the files being joined. Figures 11.10 through 11.12 illustrate the DDS necessary to create a join logical file. Table 11.9 provides an example of data mapped through the join logical file that is created.

Figure 11.10 illustrates the DDS for a physical file containing the part number, quantity on hand, and part description.

 .....AAn01n02n03T.Name++++++RLen++TDcBRowColKeywords+++++++++++++++++++       *** Physical file: INVMAST      A          R INVENTRY      A            PARTNO         5P 0       COLHDG('Part' 'Number')      A            QTYOH          7P 0       COLHDG('Qty' 'On' 'Hand')      A            DESC          50A         COLHDG('Part' 'Desc.') 

Figure 11.10: DDS of a primary physical file.

Figure 11.11 illustrates the DDS for a physical file containing the customer number, part number, and quantity ordered.

 .....AAn01n02n03T.Name++++++RLen++TDcBRowColKeywords+++++++++++++++++++       *** Physical file: ORDHIST      A          R HISTREC      A            CUSTNO         5P 0       COLHDG('Part' 'Number')      A            PARTNO    R               REFFLD(PARTNO INVMAST)      A            QTYORD    R               REFFLD(QTYOH INVMAST)      A                                      COLHDG('Qty' 'Ordered') 

Figure 11.11: DDS of a secondary physical file.

Figure 11.12 illustrates the DDS for the join logical file. This file, or view, contains fields from the files of both Figures 11.10 and 11.11. The JFILE keyword (line 2) identifies the physical files on which the logical file is based.

 .....AAn01n02n03T.Name++++++RLen++TDcBRowColKeywords+++++++++++++++++++       *** Join-Logical file: INVORDHST 0001 A                                      JDFTVAL 0002 A          R INORHIST                  JFILE(INVMAST ORDHIST) 0003 A          J                           JOIN(1 2) 0004 A                                      JFLD(PART PART) 0005 A            PARTNO                    JREF(1) 0006 A            CUSTNO                    JREF(2) 0007 A            QTYORD                    JREF(2) 0008 A            DESC                      JREF(1) 0009 A          K PARTNO 

Figure 11.12: DDS of a join logical file.

The JOIN keyword (line 3) indicates the primary-to-secondary file sequence. In other words, the join is from file number 1 to file number 2. This means that a record from the INVMAST file (file 1) is retrieved, and then an equal record from the ORDHIST file (file 2) is retrieved.

On line 4, the JFLD keyword is used to identify the fields names from the INVMAST and ORDHIST files that are used to connect (i.e., join) the two files.

The fields used for the join logical file are listed on lines 5 to 8. The JREF keyword identifies the file from which the field's content is retrieved. This is important when a file name exists in more than one of the based-on physical files.

Finally, line 9 identifies the file name used for the key to the join logical file. The field name doesn't require the JREF keyword because it must (currently) be a name that exists in the primary physical file. An example of two physical database files and a join logical file is listed in Tables 11.7, 11.8, and 11.9, respectively. When the data from the INVMAST file (file 1, as listed in Table 11.7) is joined with the ORDHIST file (file 2, listed in Table 11.8), the resulting join-logical view, as listed in Table 11.9 is created.

Table 11.7: Example Physical File Data for File 1

Inventory File (INVMAST)
PART	QTYOH	DESC
100	5000	VGA Display
200	6	Hi-gain Antennas
300	1	OS/2 Applications

Table 11.8: Example Physical File Data for File 2

Customer Order History (ORDHIST)
CUST	PART	QTYORD
1207	100	50
5340	100	1000
5381	200	1
5382	200	1

Table 11.9: Join Logical File View of Data

Resulting Join Logical File: INVORDHST (File INVMAST Joined to ORDHIST)
PART	CUST	QTYORD	DESC
100	1207	50	VGA Display
100	5340	1000	VGA Display
200	5381	1	Hi-gain Antennas