Wednesday, October 2, 2019

Getting database relationships using SQL

get list of dependent objects for a file 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.

2 comments:

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.