API Corner: Database APIs

September 28, 2005 Shannon O'Donnell

The code for this article is available for download.

If you want to gather some information about an OS/400 database, there are several ways you can go about it. You could use the DSPFD (Display File Description) command to display top level information about a file. Or you could use the DSPFFD (Display File Field Descriptions) command to display information about each field. However, neither command has a very convenient method of getting the resulting information into a format that can be easily used by your applications. To do that, you need to use the database APIs.

The Database APIs

There are several database APIs that you can access to retrieve the specific types of information you are most interested in:

• QDBLDBR: List Database Relations
• QUSLRCD: List Record Formats
• QUSMBRD: Retrieve Member Description
• QUSLFLD: Retrieve File Field Information

In addition, there is a very handy API, QUSLOBJ (List Objects), which you can use to retrieve a list of OS/400 *FILE objects into a user space, which can then be displayed and acted upon by your application program.

For this article, I have written a series of four RPGLE programs which use all five of these APIs. The combined programs can be used independently or can be all executed in a chained fashion, as they are in the code samples for this article, to create a very handy OS/400 database file information utility.

The four programs are:

• LSTFILR1: This program uses the QUSLOBJ and QUSMBRD APIs to build a list of physical files from a specific, user selected library, into a user space. The output from this program is very similar to the WRKOBJ (Work with Objects) command.
• LSTDBRR1: This program uses the QDBLDBR API to list the database relations for a selected file into a user space object. The output from this program is very similar to the DSPDBR (Display Database Relations) command.
• LSTFMTR1: This program uses the QUSLRCD API to create a list of record formats, for a user specified file, into a user space object.
• LSTFLDR1: This program uses the QUSLFLD API to create a list of file field information into a user space, for a user selected file. The output from this program is very similar to the DSPFFD (Display File Field Descriptions) command.

The Mechanics

The mechanics of how all these programs work is very similar to how all the other list API programs that have appeared in previous API Corner articles in this newsletter. Because they are so similar, I will not repeat all that information here. Instead, I will highly recommend that you go back and read any of those previous API Corner articles if you have any questions about how to use list APIs such as those used in the four programs described in this article.

Instead, I will briefly describe the architecture of this utility and then you can download the source code for each of these and compile them on your own system to try them out.

Remember, if you so desire, you can run each of these programs as standalone entities. However, I find that the database APIs are most useful when used in conjunction with other database APIs. Therefore, I’ve tied all these programs together in a somewhat logical order.

When you’ve compiled the programs on your system, start the utility by calling the LSTFILR1 program (CALL LSTFILR1). You will be prompted to enter the name of a library that you want to get a list of physical files for. When you enter this library name and press Enter on this screen, the QUSLOBJ API will be executed, retrieving a list of *FILE objects for your selected library, into a user space. The user space will then be read, extracting each returned entry and testing it to make sure that it’s a file type of “PF” (physical file) and that it is a data file. The data file test is accomplished by passing the retrieved file name to the QUSMBRD API to determine whether this file is a data file or a source file. If it’s a data file, it will be written to the subfile.

From the resulting subfile list, you can now choose to display the database relations for any file in this list, or display the record formats for any file in this list. When you select the display database relations option, program LSTDBRR1 will be called, passing to it the file selected, and the library name it resides in.

LSTDBRR1 will execute the QDBLDBR API to retrieve a list of dependent files and display them in a subfile. From this new subfile, you can choose to display the record formats of the returned logical file.

Regardless of how you got here, when you choose to display the file record formats, program LSTFMTR1 will be called, passing to it the library and file name selected. This program will then list the record formats for this file into the subfile. You can now choose to display the fields defined for this record format by selecting a record format from the subfile and pressing the Enter key.

To display the file fields, the LSTFLDR1 program is called, passing to it the library, file name and record format name selected. LSTFLDR1 will then execute the QUSLFLD API to list the fields into a subfile. The returned information will contain some basic information about each field defined for that record format.

Extending the Utility

Each of the database APIs described in these programs can return much, much more information than is included in the utility. In fact, you will probably want to extend the utility to display more information, such as change and save dates, as well as more detailed information about each field. Each API’s receiver variable has already been pre-defined in its entirety in each program, for your convenience. All you need to do is add that data to the subfiles.

RELATED RESOURCE

API Corner: List Spooled Files From an Application