I was asked if it was possible to get the same information that is shown using the Display Database Relations command, DSPDBR , via SQL. You can generate an output file using the DSPDBR command, but that was not what was wanted.

There is a Db2 for i view SYSVIEWDEP which returns the dependencies of a view to its "parent". But it just contains only SQL views and indexes, no information for logical files. As I work in an environment where there are thousands of logical files in the ERP application this view does not give the dependency information I needed.

If the information is available via a command it must be contained somewhere within the IBM i operating system. In this case the dependency information is in the file QADBFDEP in QSYS .

There is not information about this file in IBM's KnowldegeCenter. The layout of the file is as follows:

Field Description Size Type DBFFIL Dependency name 130 Varchar DBFLIB Dependency Library name 10 Varchar DBFFDP File name of dependent file 130 Varchar DBFLDP Library name of dependent file 10 Varchar DBFTD Dependency: D-data,V-view,I-indirect 1 Char DBFRDP Format name of dependent file 10 Varchar DBFLB2 Library name 130 Varchar DBF_FSIG Signature 10002 Hex DBF_PCNT Parameter count 4,0 Bin IASPNUMBER Primary ASP 2,0 Bin

Now I have given the file definition I can give the SQL statement I used to show the dependency relationships:

01 SELECT DBFLIB AS "Library", 02 DBFFIL AS "File", 03 DBFLDP AS "Dep file lib", 04 DBFFDP AS "Dep file", 05 CASE WHEN DBFTDP = 'D' THEN 'Data' 06 WHEN DBFTDP = 'V' THEN 'View' 07 WHEN DBFTDP = 'I' THEN 'Indirect' 08 ELSE DBFTDP 09 END AS "Dependency" 10 FROM QSYS.QADBFDEP 11 WHERE DBFLIB = 'PRODLIB' AND DBFFIL = 'ORDHDR' 12 ORDER BY DBFLIB,DBFFIL

I have given the columns meaningful names as the names of the fields are not very descriptive.

Lines 5 – 9: Rather than give the single character from the field in the file I have used a CASE to map the letter to a description.

The returned results do not include any objects or their dependents in the library QTEMP .

Library File Dep file lib Dep file Dependency ------- ------ ------------ -------- ---------- PRODLIB ORDHDR PRODLIB ORDHDRLA Data PRODLIB ORDHDR PRODLIB ORDHDRLG Data PRODLIB ORDHDR PRODLIB ORDHDRLN Data PRODLIB ORDHDR PRODLIB ORDHDRLE Data PRODLIB ORDHDR PRODLIB ORDHDRL1 Data

Most of the dependencies for this file are "data" as the majority of the dependent objects are logical files. But there are a few that are not.

Library File Dep file lib Dep file Dependency ------- --------- ------------ -------- ---------- PRODLIB COST01V1 PRODLIB COST01V0 View PRODLIB COST01V2 PRODLIB COST01V0 View PRODLIB COST01V3 PRODLIB COST01V0 View PRODLIB MFGDTL PRODLIB COST01V0 Indirect PRODLIB ORDDTL PRODLIB COST01V0 Indirect PRODLIB ORDHDR PRODLIB COST01V0 Indirect

Looking at the different types of dependencies this is what I have discovered:

Data: Is returned if the dependent object is a logical file, SQL view or index built over the "parent" file or table.

View: If I have built a SQL View using another SQL View the new view is shown as the "Dep file" and the "parent" View is shown as the "File".

Indirect: The object shown in the "Dep file" is a SQL View that was built over another View. That "parent" SQL View was built over the file shown in the "File" column.

By all means that the file that contains the dependency information is not SQL, but the method to present its data is.

This article was written for IBM i 7.3, and should work for some earlier releases too.