Logical files are one of the features of DB2/400 that make it so powerful. They’re so easy to create that make databases are cluttered with unnecessary logical files. Use this handy utility to find an existing access path when you need one.

Recently, I was working on a project when I came across a physical file that had 40 dependent logical files. Forty! This got my curiosity going, so I started looking at the keys to the various logical files, and I discovered that several of them were duplicates of each other. I know that, right now, you’re either wondering how this could possibly happen or nodding your head in complete understanding. Well, our shop is lean and mean, which means we have no database administrator to control the addition of logical files. And, like most other shops, we have had our share of “come-and-go” contract programmers. You can probably see where I’m going with this: Inexperienced or hasty programmers might create a logical file without first checking to see if one already exists with the key they need.

This unnecessary duplication is a problem that can quickly multiply, and the result is a drain on performance, since each change to a record requires DB2/400 to maintain more and more access paths. The obvious solution is to look for the needed key in the existing logical files before creating a new one. But how do you convince your programmers to manually check 40 logical files for a particular key, especially if they are pressed for time? You could provide them with a quick, easy, and accurate utility to do the work for them. Our shop didn’t have such a utility, however, so I created one myself.

This utility, called Check Logicals (CHKLGL) combines basic CL commands with the power of API messaging, and the result is an automated file search that takes a fraction of the time that a manual search would take. (For the source code to this utility, see MC’s Web site at www.midrangecomputing.com/mc/99/01.) The programmer simply enters the name of a physical file, its library, and the appropriate key fields in the desired order. The program then displays the applicable logical file name, if one exists. If the physical file itself has the appropriate key list, that file name is displayed, and the logical files are not checked.

The display file is simple enough. It collects the physical file name, the library where it resides, and the fields required for the file key. I have allowed for 10 key fields. The display file uses the Cursor Location (CSRLOC) keyword to position the cursor on fields in error without using indicators. I use a basic line-24 message subfile to display error and information messages to the programmer. I have enabled F3 to exit the program and F5 to refresh the screen, and an indicator is set to protect the input fields when the results are displayed. I have also enabled Help, which I have written in the User Interface Manager (UIM) panels. The Help panels are included on MC’s Web site for review but will not be discussed in this article.

You will need to create source for three logical files. These files will be based on the output from the following three commands that will run in the CL modules. Each logical file name is a combination of the command, Q for library QTEMP, and L for logical file.

• DSPFFDQL will be a view of the output from the Display File Field Descriptions (DSPFFD) command by field name. This file will be used to validate the field names entered by the programmer.

• DSPDBRQL will be a view of the output from the Display Database Relations (DSPDBR) command by file name. This file will be used to capture all of the dependent logical files.

• DSPFDQL will be a view of the output from the Display File Description (DSPFD) command by file name, key field name, and key field number in key list, omitting records for join logical files. The programmer will use this file to search for the first logical file that has the same key as the key entered.

The main RPGLE module, CHKLGL_A, collects the file information from the programmer and edits the information for accuracy. When an error is encountered, it uses the system-supplied Send Program Message (QMHSNDPM) and Remove Program Message (QMHRMVPM) APIs to write the error message(s) to the line-24 message subfile and to clear the error message subfile. When a screen field is found in error, the row and column positions are placed in the ROW and COL fields in order to position the cursor on the field in error. I wanted to capture as many errors as possible, so the main module performs the following edits:

• The file name and library name must be entered.
• The programmer must enter at least one key field.
• The programmer must not enter the same key field more than once.
• The file must exist in the specified library and must be a physical file. This implies that the library must also exist. Module CHKLGL_1 is called to validate the file and library.

• All fields entered must exist in the physical file. Module CHKLGL_B is called to validate each field.

Once all of the edits have been performed and the data is correct, module CHKLGL_C is called. This module looks for a logical file with the specified key list. If the key is found in the physical or a logical file, the file name and library are displayed in a message to the programmer. If the key list is not found, an informational message is displayed to the programmer.

Module CHKLGL_1 is the first module called by the edit routine of the main module. It is used to validate the file and library name entered by the programmer. It also creates the work files that will be used in this utility.

The Check Object (CHKOBJ) command is used to validate the file and library. If the file or library doesn’t exist, an error flag is set and returned to the main module. The Retrieve Object Description (RTV-OBJD) command is used to retrieve the file attribute. If the selected file is not a physical file, an error flag is set and returned to the main module.

If the file exists in the library and is a physical file, the work files will be created in library QTEMP for use by this utility. First, the logical and physical work files are deleted

from QTEMP if they exist. The physical work files are then created with file commands, with the output going to outfiles. I then take the following steps to create the files:

• I use the DSPFFD command to retrieve the file layout for the specified file. The resulting file will contain one record for every field in the file.

• I use the DSPDBR command to retrieve the dependent logical files. The resulting file will contain one record for every dependent logical file.

• I use DSPFD with the TYPE(*ACCPTH) keyword and parameter to retrieve the access path description for a file. This file will contain one record for every key field in the file.

After each work file is created, a logical file is created for it, using the source members that were created earlier.

Module CHKLGL_B is also called from the edit routine of the main module. It is used to validate the fields entered by the programmer. It does a Set Lower Limits (SETLL) operation on the work file DSPFFDQL with the field name received from the main module. The resulting indicator will be set on if the field is found in the file. (Note that I use a SETLL operation and not CHAIN. I just want to see if the field exists; I don’t want to do anything with it right now.)

Now that all of the edits are complete, there is just one more task to accomplish, and that is to find the file. Module CHKLGL_C is called to do this. It receives the key fields (in one string) and a result flag as parameters, as well as result fields to return the logical file name and library to the main module.

Recall that earlier we created a work file called DSPFDQL. It contains one record for every key field in the file in the correct order. The module first checks the physical file to determine if it contains the required key. If not, it starts reading the work file DSPDBRQL, which contains one record for every dependent logical file, except join logical files. Each time a record is read, the module closes the work file DSPFDQL and calls module CHKLGL_2. CHKLGL_2 re-creates file DSPFDQ with the access path information for the dependent file.

Look at the D-specs for this module, and note that the string of key fields received as a parameter is overlaid by an array. A key list is set up with the file name, the field name as an element of the array, and the element number. This element number will correspond to the key field number in the key list. The program steps through the array, again using the SETLL operation, to determine if the current key element matches the key in the access path. The first time the key fields do not match, you know that the key fields in the access path do not match the key order specified by the programmer. When this happens, the module will exit the loop, get the next record from work file DSPDBRQL, and begin the process again.

A logical file is considered a match if it has the same key list as entered or if it has a longer key list that matches the one entered. For example, if the programmer entered the fields FLD1, FLD2, and FLD3 in that order, and the logical has the fields FLD1, FLD2, FLD3, and FLD4 in its key in that order, that logical file is accepted.

When CHKLGL_C returns control to the main module, CHKLGL_A will display a message to the programmer. If a match is found, the message will contain the matching logical (or physical) file and its library. If no match is found, an informational message will be displayed and the programmer can try another key.

You can see that this utility will come in very handy. It takes advantage of system- supplied APIs and messages, and its modular approach makes for easy coding and maintenance. You will enjoy better performance on your AS/400 if you can cut down on the number of logical files, and this utility will pay for itself after just a few uses with the time that you save.

Also Read

The API Corner: Moving Jobs Within and Across Job Queues

The API Corner: What Time Zone Do You Want That Date and Time In?

The API Corner: How Do You Want That Date and Time?