Querying the System Catalog Like DSPFD

Hey, Howard:

I liked your DSPDBR spreadsheet that called a stored procedure. Is it possible to do the same thing without calling the stored procedure, by querying the SYSTABLES or other system catalog file?

text

--John

You can't find the related tables in the SQL catalog views provided in the QSYS2 library. However, there are other catalog tables on the iSeries in the QSYS library. One file, QADBFDEP, describes how files are related to one another on the iSeries. The file contains fields for the file name (DBFIL), the library (DBFLIB), the dependent file (DBFFDP), and its library (DBFLDP). The following query will give you a list of files dependent on the IVPMSTR file in the library HD1100PD.

SELECT DBFLDP AS LIBRARY, DBFFDP AS FILE, 
	DBXTXT AS DESCRIPTION, 
	DBXATR AS FTYPE
FROM QSYS.QADBFDEP INNER JOIN QSYS.QADBXREF
	ON (DBFLDP=DBXLIB AND DBFFDP=DBXFIL)
WHERE DBFFIL = 'IVPMSTR' AND DBFLIB='HD1100PD'

This query selects the library of the dependent file, the file name, the text description of the file, and the type of file by joining QADBFDEP to QADBXREF on the dependent file name and library. The WHERE clause of the query causes the program to only list files that are dependent on IVPMSTR, in the library HD1100PD. The output of the query looks like this:

Note that, depending on the security on your iSeries, you may not be able to directly query the QXDBXREF or QADBFDEP files. If you have a problem, use one of the logical files built over these physicals by substituting QADBLDEP for the QADBFDEP file and QADBXATR for the QADBXREF.

For more information about the system catalogs, see the iSeries SQL reference in the iSeries Information Center or go to my Web site, at www.SQLThing.com.

Howard F. Arner, Jr., is the author of iSeries and AS/400 SQL at Work. Howard also designed SQLThing Enterprise Edition, a query program and stored procedure editor specifically for the AS/400's unique capabilities, and Import Expert 400, a program for quickly importing data from almost any data source to the iSeries. You can purchase a copy of Howard's book or learn more about his products at www.sqlthing.com. Send your SQL questions to harner@sqlthing.com.

Sponsored By ADVANCED SYSTEMS CONCEPTS

WHAT IF Creating Queries, Reports and Downloads was Easy, Fast and Convenient for ALL Users? - - - Make it happen with SEQUEL ! - - - Get a Free Trial today. Read More

---

Editors: Howard Arner, Joe Hertvik, Ted Holt, David Morris,

Managing Editor: Shannon Pastore

Publisher and Advertising Director: Jenny Thomas

Advertising Sales Representative: Kim Reed

Contact the Editors: To contact anyone on the IT Jungle Team
Go to our contacts page and send us a message.

---