In the first installment, I introduced you to the eight database cross-reference files that the AS/400-iSeries automatically maintains for the database files on your system. These eight files contain exhaustive information about each and every database file on your AS/400-iSeries. Now let's look at a real-life example of how you can put these cross-reference files to work in your shop.
A utility to help you find the right logical file
We've all gone through the time-consuming searching that is sometimes involved when trying to locate an existing logical file to use in an application. We know what physical file the data is in, but we need to access the data by a certain key. Some of us have purchased third-party utilities or have put together in-house programs to do the job. For those who haven't, I've put together my own utility to save you time.
The Find Logical File utility (FNDLF), takes advantage of the cross-reference files to accomplish the task. You pass the program the name of the library and the name of the physical file over which you want to find a logical file. The program first uses the QADBFDEP file to find all the dependent files (i.e., logical files, SQL views, etc.) built over the physical file. Then, using the logical files ADBKFLD0 and ADBIFLD0, it prints a list of all the dependent files along with their key fields.
Important note: The logical files ADBKFLD0 and ADBIFLD0 are not pre-defined. You must build these logical files yourself over the cross-reference files QADBKFLD and QADBIFLD, respectively, using the DDS source at the following links: ADBKFLD0.LF and ADBIFLD0.LF.
Besides the two parameters mentioned above (library and file name), the program accepts two others:
- LFONLY -- Include only logical files in the listing. When set to '1', only logical files are included in the listing. Other types of dependent files, such as SQL views, are not included . When you set the LFONLY parameter to '1' the program uses the DBIATR field from the QADBXREF file to check whether dependent files are logical files.
- PRINT -- Print or display report. When set to '1', the report is printed. When set to anything other than '1', the report is displayed on your screen.
Compiling reminder: Many of the fields in the cross-reference files are null-capable and, in fact, many records contain null values in one or more of these fields. Also, some fields are defined as variable-length character fields. To alleviate having to do any special coding to handle these fields, remember to specify CVTOPT(*VARCHAR) and ALWNULL(*YES) on the RPG compiler commands (either CRTBNDRPG or CRTRPGMOD). That forces the compiler to declare variable-length character fields as fixed-length fields and allow fields containing null values to be retrieved.
Note: Putting a command interface on the front end of this utility is a simple exercise and is left to the reader if desired.
For more information on DB2's cross-reference files, see "DB2 for OS/400 Database Programming" (SC41-3701).
-----------------------------------
About the author: Ron Turull is editor of Inside Version 5. He has more than 20 years' experience programming for and managing AS/400-iSeries systems.
