A system catalog is essentially a database file that i5/OS maintains behind the scenes and then uses to track objects and other information on the system. And a little bit of basic knowledge about them can make programming simpler for you.

As an example, let's talk about QADBXREF (the Database Cross-Reference system catalog). This system catalog is a physical file with an object of type *FILE and an attribute of PF-DTA. It contains a record for each database file and SQL table on your iSeries. Whenever you add a file to your iSeries—using CRTPF, CRTLF, CREATE TABLE, etc.— i5/OS adds a record to QADBXREF containing all sorts of information about that file: file name, library name, attribute of the file (physical, logical, table, index, view, etc.), owner.... In fact, here's a list of all the fields in QADBXREF:

So what can system catalogs do for you? They provide an alternative means to solve certain problems. If you have an application that needs a list of files on the system, you can...

• Get that list using an API. This requires some complex coding, and probably a user space.
• Use DSPFD to generate a list of files to an output file. This requires a front-end CL driver and possibly some database overriding.
• Simply read QADBXREF from your program.

System catalogs have logical files, so you can use the access path that best meets your needs. Use DSPDBR and DSPFD to find the best logical. You can also create your own logicals, which many applications do.

SQL creates logical files over system catalogs as well—for example, SYSTABLES. In fact, a SYSTABLES file is generated for every schema created through SQL, selecting only the files in that schema.

Now, some things to be aware of when using system catalogs:

• Always open them as input, never as update. Access them only as read-only.
• You may not have enough authority to use a system catalog. If not, try one of its logicals.
• Study the record format and fields of a system catalog carefully; some information you need may not be immediately available and will need to be calculated.

Other useful system catalogs:

• QADBIFLD lists every field and column on your iSeries.
• QADBKFLD lists every key field and column on your iSeries.
• QADBXTRIGB lists the triggers on your iSeries. I once published a utility called Trigger Tracker that uses this system catalog.
• QAOKP01A lists the users in the System Distribution Directory.
• QATMSMTPA contains the email addresses for the users in the System Distribution Directory.

Here is a sample program using QAOKP01A and QATMSMTPA:

System catalogs are a great place to get information about your iSeries objects and are an alternative to APIs and command outfiles. Just be sure to use them carefully.

Doug Eckersley is the chief iSeries developer at Dominion Homes in Dublin, Ohio. He has over 16 years of application development experience and is the co-author of Brainbench’s RPG/IV certification test.