Getting database relationships using SQL

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.