AS/400-iSeries Starter Kit

For many years, IBM sold the S/38 on the premise that it was the "logical choice." Yes, that play on words was corny, but true. One of the S/38's strongest selling points was the relational database implementation provided by logical files, and the AS/400 has inherited that feature.

Logical files on the AS/400 provide the flexibility needed to build a database for an interactive multiuser environment.

As I said in the last chapter, there are two kinds of database files: physical files and logical files. Physical files contain data; logical files do not. Logical files control how data in physical files is presented, most commonly using key fields (whose counterpart on the S/36 is the alternate index) so that data can be retrieved in key-field sequence. However, the use of key fields is not the only function logical files provide. Let me introduce you to the following basic concepts about logical files:

• record format definition/physical file selection
• key fields
• select/omit logic
• multiple logical file members

Record Format Definition/Physical File Selection

To define a logical file, you must select the record formats to be used and the physical files to be referenced. You can use the record format found in the physical file, or you can define a new record format. If you use the physical file record format, every field in that record format is accessible through the logical file. If you create a new record format, you must specify which fields will exist in the logical file. A logical file field must either reference a field in the physical file record format or be derived by using concatenation or substring functions.

Because the logical file does not contain any data, it must know which physical file to access for the requested data. You use the DDS PFILE keyword to select the physical file referenced by the logical file record format. You specify the physical file in the PFILE keyword as a qualified name (i.e., library_name\file_name) or as the file name alone.

Figure 22.1a lists the DDS for physical file HREMFP, and Figure 22.1b shows the DDS for logical file HREMFL1. Notice that the logical file references the physical file's record format (HREMFR). Consequently, every field in the physical file will be presented in logical file HREMFL1. Also notice that the PFILE keyword in Figure 22.1b references physical file HREMFP.

In Figure 22.1c, logical file HREMFL2 defines a record format not found in PFILE-referenced HREMFP. Therefore, this logical file must define each physical file field it will use. A logical file can thus be a projection of the physical file -- that is, contain only selected physical file fields. Notice that fields EMEMP#, EMSSN#, and EMPAYR all appear in the physical file but are not included in file HREMFL2.

Key Fields

Let's look at Figures 22.1b and 22.1c again to see how key fields are used. File HREMFL1 identifies field EMEMP# as a key field (in DDS, key fields are identified by a K in position 17 and the name of the field in positions 19 through 28). When you access this logical file by key, the records will be presented in employee number sequence. The logical file simply defines an access path for the access sequence -- it does not physically sort the records.

The UNIQUE keyword in this source member tells the system to require a unique value for EMEMP# for each record in the file, thus establishing EMEMP# as the primary key to physical file HREMFP. Should the logical file be deleted, records could be added to the physical file with a non-unique key, giving rise to a question that has been debated over the years: Is it better to use a keyed physical file or a keyed logical file to establish a file's primary key?

You could specify EMEMP# as the key in the DDS for physical file HREMPF and enforce it as the primary key using the UNIQUE keyword. Making the primary key a part of the physical file has a distinct advantage: The primary key is always enforced because the physical file cannot be deleted without deleting the data. Even if all dependent logical files were deleted, the primary key would be enforced. However, placing the key in the physical file also has a disadvantage. Should the access path for a physical file data member be damaged (a rare, but possible, occurrence), the damaged access path prevents access to the data. Your only recourse in that case would be to delete the member and restore it from a backup. Another minor inconvenience is that any time you want to process the file in arrival sequence (e.g., to maximize retrieval performance), you must use the OVRDBF (Override with Database File) command or specify arrival sequence in your high-level language program.

Placing the primary key in a logical file, as I did in Figure 22.1b, ensures that access path damage results only in the need to recompile the logical file -- the physical file remains intact. This method also means that you can access the physical file in arrival sequence. As I mentioned earlier, the negative effect is that deleting the logical file results in leaving the physical file without a primary key.

Let me make a few comments concerning the issue of where to place the primary key. Access path maintenance is costly; when records are updated, the system must determine whether any key fields have been modified, requiring the access path to be updated. The overhead for this operation is relatively small in an interactive environment where changes are made randomly based on business demands. However, for files where batch purges or updates result in many access path updates, the overhead can be quite detrimental to performance. With that in mind, here are some suggestions.

• For work files, which are frequently cleared and reloaded, create the physical file with no keys, and place the primary and alternate keys in logical files. Then delete the logical files (access paths) before you clear and reload the file. The update will be much faster with no access path maintenance to perform. After the update, rebuild or restore the logical files.
• The same method works best for very large files. When you need to update the entire file, you can delete the logical files, perform the update, and then rebuild or restore the logical files.
• For files updated primarily through interactive maintenance programs, putting the key in the physical file poses no performance problems.

The UNIQUE keyword is also expensive in terms of system overhead, so you should use it only to maintain the primary key. Logical file HREMFL2 specifies three key fields -- EMLNAM (employee last name), EMFNAM (employee first name), and EMMINT (employee middle initial). The UNIQUE keyword is not used here because the primary key is the employee number and there is no advantage in requiring unique names (even if you could ensure that no two employees had the same name). A primary key protects the integrity of the file, while alternative keys provide additional views of the same data.

Select/Omit Logic

Another feature that logical files offer is the ability to select or omit records from the referenced physical file. You can use the keywords COMP, VALUES, and RANGE to provide select or omit statements when you build logical files.

Figure 22.2 shows logical file HREMFL3. Field EMTRMD (employee termination date) is used with keyword COMP to compare values, forming a SELECT statement (notice the S in position 17). This DDS line tells the system to select records from the physical file in which field EMTRMD is equal to 0 (i.e., no termination date has been entered for that employee). Therefore, when you create logical file HREMFL3, OS/400 builds indexed entries in the logical file only for records in which employee termination date is equal to zero, thus omitting terminated employees (EMTRMD NE 0). When a program accesses the logical file, it reads only the selected records.

Before looking at some examples, I want to go over some of the basic rules for using select/omit statements.

1. You can use select/omit statements only if the logical file specifies key fields (the value *NONE in positions 19 through 23 satisfies the requirement for a key field) or if the logical file uses the DYNSLT keyword. (I'll go into more detail about this keyword later.)

2. To locate the field definitions for fields named on a select/omit statement, OS/400 first checks the field name specified in positions 19 through 28 in the record format definition and then checks fields specified as parameters on CONCAT (concatenate) or RENAME keywords. If the field name is found in more than one place, OS/400 uses the first occurrence of the field name.

3. Select/omit statements are specified by an S or an O in position 17. Multiple statements coded with an S or an O form an OR connective relationship. The first true statement is used for select/omit purposes.

4. You can follow a select/omit statement with other statements containing a blank in position 17. Such additional statements form an AND connective relationship with the initial select or omit statement. All related statements must be true before the record is selected or omitted.

5. You can specify both select and omit statements in the same file, but the following rules apply:

a. If you specify both select and omit for a record format, OS/400 processes the statements only until one of the conditions is met. Thus, if a record satisfies the first statement or group of related statements, the record is processed without being tested against the subsequent select/omit statements.

b. If you specify both select and omit, you can use the ALL keyword to specify whether records that do not meet any of the specified conditions should be selected or omitted.

c. If you do not use the ALL keyword, the action taken for records not satisfying any of the conditions is the converse of the last statement specified. For example, if the last statement was an omit, the record is selected.

Now let's work through a few select/omit examples to see how some of these rules apply. Consider the statements in Figure 22.3. Based on rule 3, OS/400 selects any record in which employee termination date equals 0 or employee type equals H (i.e., hourly). Both statements have an S coded in position 17, representing an OR connective relationship.

Contrast the statements in Figure 22.3 with the statements in Figure 22.4. Notice that the second statement in Figure 22.4 does not have an S or an O in position 17. According to rule 4, the second statement is related to the previous statement by an AND connective relationship. Therefore, both comparisons must be true for a record to be selected, so all current hourly employees will be selected.

To keep it interesting, let's change the statements to appear as they do in Figure 22.5. At first glance, you might think this combination of select and omit would provide the same result as the statements in Figure 22.4. However, it doesn't -- for two reasons. As rule 5a explains, the order of the statements is significant. In Figure 22.5, the first statement determines whether employee type equals H. If it does, the record is selected and the second test is not performed, thus allowing records for terminated hourly employees to be selected.

The second reason the statement in Figures 22.4 and 22.5 produce different results is because of the absence of the ALL keyword, which specifies how to handle records that do not meet either condition. According to rule 5c, records that do not meet either comparison are selected because the system performs the converse of the last statement listed (e.g., the omit statement).

Figure 22.6 shows the correct way to select records for current hourly employees using both select and omit statements. The ALL keyword in the last statement tells the system to omit records that don't meet the conditions specified by the first two statements. In general, however, it is best to use only one type of statement (either select or omit) when you define a logical file. By limiting your definitions this way, you will avoid introducing errors that result when the rules governing the use of select and omit are violated.

Select/omit statements give you dynamic selection capabilities via the DDS DYNSLT keyword. DYNSLT lets you defer the select/omit process until a program requests input from the logical file. When the program reads the file, OS/400 presents only the records that meet the select/omit criteria. Figure 22.7 shows how to code the DYNSLT keyword.

So now I guess you are wondering just how this differs from an example without the DYNSLT keyword. It differs in one significant way: performance.

In the absence of the DYNSLT keyword, OS/400 builds indexed entries only for those records that meet the stated select/omit criteria. Access to the correct records is faster, but the overhead of maintaining the logical file is increased. When you use DYNSLT, all records in the physical file are indexed, and the select/omit logic is not performed until the file is accessed. You only retrieve records that meet the select/omit criteria, but the process is dynamic. Because DYNSLT decreases the overhead associated with access path maintenance, it can improve performance in cases where that overhead is considerable. As a guideline, if you have a select/omit logical file that uses more than 75 percent of the records in the physical file member, the DYNSLT keyword can reduce the overhead required to maintain that logical file without significantly affecting the retrieval performance of the file, because most records will be selected anyway. If the logical file uses less than 75 percent of the records in the physical file member, you can usually maximize performance by omitting the DYNSLT keyword and letting the select/omit process occur when the file is created.

Multiple Logical File Members

The last basic concept you should understand is the way logical file members work. The CRTLF (Create Logical File) command has several parameters related to establishing the member or members that will exist in the logical file. These parameters are MBR (the logical file member name), DTAMBRS (the physical file data members upon which the logical file member is based), and MAXMBRS (the maximum number of data members the logical file can contain). The default values for these parameters are *FILE, *ALL, and 1, respectively.

Typically, a physical file has one data member. When you create a logical file to reference such a physical file, these default values instruct the system to create a logical file member with the same name as the logical file itself, base this logical file member on the single physical file data member, and specify that a maximum of one logical file member can exist in this file.

When creating applications with multiple-data-member physical files, you often don't know precisely what physical and logical members you will eventually need. For example, for each user you might add members to a temporary work file for each session when the user signs on. Obviously, you (or, more accurately, your program) don't know in advance what members to create. In such a case, you would normally

• Create the physical file with no members:

      CRTPF FILE(TESTPF) MBR(*NONE)

• Create the logical file with no members:

      CRTLF FILE(TESTLF) MBR(*NONE)

• For every user that signs on, add a physical file member to the physical file:

      ADDPFM FILE(TESTPF) MBR(TESTMBR) TEXT('Test PF Data Member')

• For every physical file member, add a member to the logical file and specify the physical file member on which to base the logical member:

      ADDLFM FILE(TESTLF) MBR(TESTMBR) DTAMBRS((TESTPF TESTMBR)) +
            TEXT('Test LF Data Member')

When a logical file member references more than one physical file member, and your application finds duplicate records in the multiple members, the application processes those records in the order in which the members are specified on the DTAMBRS parameter. For instance, if the CRTLF command specifies

 CRTLF FILE(TESTLIB/TESTLF) MBR(ALLYEARS)  +
      DTAMBRS((YRPF DT1988) (YRPF DT1989) (YRPF DT1990))

a program that processes logical file member ALLYEARS first reads the records in member DT1988, then in member DT1989, and finally in member DT1990.

Keys to the AS/400 Database

Understanding logical files will take you a long way toward creating effective database implementations on the AS/400. Since I have introduced the basic concepts only, I strongly recommend that you spend some time in the manuals to increase your knowledge about logical files. Start with the description of the CRTLF command in IBM's Programming: Control Language Reference (SC41-0030) and also refer to Chapter 3, "Setting Up Logical Files," in the AS/400 Database Guide (SC41-9659). As you master the methods presented, you will discover many ways in which logical files can enhance your applications.