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.

12 comments:

  1. But what about the case when the view is created in QTEMP library.
    There is no entry in the system catalog table for the views created in QTEMP.

    ReplyDelete
    Replies
    1. Very true. That is minor inconvenience as any objects created in QTEMP are not permanent, they are deleted at the end of the job.

      Delete
  2. In theory, Simon. I've got some that are being generated (using 'create or replace view') by a new program that I'm testing in my interactive session. I am cancelling out in the middle of a test, fixing issues found, then relaunching. Then I noticed, using dspdbr, that there are quite a number of these still out there with no library. So, I ended my Interactive Session to get rid of them. They're still there. 0-(

    ReplyDelete
    Replies
    1. "I ended my Interactive Session"

      How did you end your interactive session?
      Signed off? Cancelled the job?

      Delete
  3. Hey Simon, thanks for the reply. 0-) I Signed off.

    ReplyDelete
  4. Use this almost daily!

    ReplyDelete
  5. Alas QADBFDEP comes in QSYS, which is always locked for programmers. We can only access QSYS2. It indeed has SYSVIEWDEP but we have a system written 45 years ago and uses logicals instead of indexes and views.

    ReplyDelete
  6. This is really strange, as a DSPDBR QADBFDEP shows that SYSINDEXES, SYSVIEWDEP and SYSTABDEP seem to be views over QADBFDEP, so i guess it would be easy to create a view providing the info about the parent file(s) for logical files.

    And even more strange, the documentation for API QDBRTVFD https://www.ibm.com/docs/en/i/7.4?topic=ssw_ibm_i_74/apis/qdbrtvfd.htm says "The QSYS2.SYSFILES view can be used as an alternative to this API." but we do not have this view on our system.

    ReplyDelete
    Replies
    1. My guess would be that your partition does not have the latest database PTFs. It was included in that.

      Delete

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.