Wednesday, August 14, 2024

Finding logical files that exist in a different library to the physical

I am sure I am not the only person who wants any logical files in the same library as the physical file they are created over. I do realize that join logical files and SQL views can be built over files in different libraries, and they have always been handled in a case-by-case basis.

My senior System Administrator stumbled across a number of logical files which were in a different to their physical files, and wondered how many more there may be?

SQL to rescue and with a simple SQL statement I can give her that information.

Let me explain the scenario I will be using in these examples. I have a physical file, TESTFILE that exists in the library MYLIB. I built three logical files over it, each one was in a different library:

  1. Logical file: TESTFILE1 in the library MYLIB
  2. TESTFILE1 in the library QTEMP
  3. TESTFILE1 in the library MYLIB2

I can display the database relationship between the physical file and these objects by using the Display Database Relations command, DSPDBR:

  DSPDBR MYLIB/TESTFILE

For this example I am interested in the last few lines of the output:

    Number of dependent files . . . . . . . . :  3
Files Dependent On Specified File
  Dependent File         Library       Dependency
      TESTFILE1          MYLIB         Data
      TESTFILE1          MYLIB2        Data
      TESTFILE1          QTEMP         Data

I don't want to use the DSPDBR one command at a time for every file on this partition. What I want is a SQL View or Table I can interrogate to get the database relationships from. Fortunately one exists, QADBFDEP, which exists in the library QSYS.

What information do I want returned? The following I thought would do for my purposes:

  1. Dependent object's name and library
  2. "Parent" object's name and library
  3. The object type of the dependent object

This is the first version of my SQL statement:

01  SELECT DBFLDP AS "DEPENDENT_LIBRARY",
02         DBFFDP AS "DEPENDENT_NAME",
03         DBFLIB AS "PARENT_LIBRARY",
04         DBFFIL AS "PARENT_NAME",
05         CASE DBFTDP
06           WHEN 'D' THEN 'DATA'
07           WHEN 'V' THEN 'VIEW'
08           WHEN 'I' THEN 'INDEX'
09           ELSE DBFTDP
10         END AS "DEPENDENCY"
11    FROM QSYS.QADBFDEP
12     AND DBFLIB <> DBFLDP  
13   ORDER BY 1,2
14   LIMIT 5

Lines 1 - 10: I am giving the five columns new column headings to make it easier to understand the information in each one.

Lines 5 – 10: I am using this Case statement to create a new column that maps the single character dependency code in the DBFTDP column to a meaningful description.

Line 12: I only want results when the parent's and dependent's library are not the same.

Line 13: Order the results by the first two columns, dependent object's library and name.

Line 14: In this statement I only want to return the first five results.

DEPENDENT_  DEPENDENT_  PARENT_     PARENT_
LIBRARY     NAME        LIBRARY     NAME        DEPENDENCY
----------  ----------  ----------  ----------  ----------
QSYS2       ASP_INFO    QSYS        QADBXRDBD   INDEX
QSYS2       ASPJ_INFO   QSYS        QADBXRDBD   INDEX
QSYS2       CHECK_CSTS  QSYS        QADBFCST    DATA
QSYS2       COLPRIV     QSYS        QADBXREF    INDEX
QSYS2       COLPRIV     QSYS        QADBXMQT    INDEX

I was surprised to find how many objects in QSYS2 are built over files in QSYS. Looking further into the results the was true for many objects in libraries that started with 'SYS'.

In this scenario I don't care about objects in IBM's libraries so I can modify my statement:

01  SELECT DBFLDP AS "DEPENDENT_LIBRARY",
02         DBFFDP AS "DEPENDENT_NAME",
03         DBFLIB AS "PARENT_LIBRARY",
04         DBFFIL AS "PARENT_NAME",
05         CASE DBFTDP
06           WHEN 'D' THEN 'DATA'
07           WHEN 'V' THEN 'VIEW'
08           WHEN 'I' THEN 'INDEX'
09           ELSE DBFTDP
10         END AS "DEPENDENCY"
11    FROM QSYS.QADBFDEP
12   WHERE DBFLDP NOT LIKE 'Q%' 
13     AND DBFLDP NOT LIKE 'SYS%'
14     AND DBFLIB <> DBFLDP  
15   ORDER BY 1,2

I added a couple of lines to exclude the IBM libraries:

Line 12: Exclude any rows from the results where dependent library that start with the letter 'Q'.

Line 13: Do the same for any rows where the dependent library start with 'SYS'.

My results are now:

DEPENDENT_  DEPENDENT_  PARENT_     PARENT_
LIBRARY     NAME        LIBRARY     NAME        DEPENDENCY
----------  ----------  ----------  ----------  ----------
OTHERLIB    SYSCHKCST   QSYS        QADBFCST    DATA
OTHERLIB    SYSCOLUMNS  QSYS2       SQLGETE...  F
OTHERLIB    SYSCST      QSYS        QADBFCST    DATA
OTHERLIB    SYSCSTCOL   QSYS        QADBCCST    DATA
OTHERLIB    SYSCSTDEP   QSYS        QADBFCST    DATA
OTHERLIB    SYSINDEXES  QSYS2       SQLGETW...  F
OTHERLIB    SYSKEYCST   QSYS        QADBCCST    DATA
OTHERLIB    SYSKEYS     QSYS        QADBKFLD    DATA
OTHERLIB    SYSPACKAGE  QSYS2       OBJECT_...  F
OTHERLIB    SYSPACKAGE  QSYS        QADBPKG     DATA
OTHERLIB    SYSREFCST   QSYS        QADBFCST    DATA
OTHERLIB    SYSTABDEP   QSYS        QADBXREF    DATA
OTHERLIB    SYSTABLES   QSYS        QADBXMQT    DATA
OTHERLIB    SYSTRIGCOL  QSYS        QADBXTRIGD  DATA
OTHERLIB    SYSTRIGDEP  QSYS        QADBXTRIGD  DATA
OTHERLIB    SYSTRIGGER  QSYS        QADBXTRIGB  DATA
OTHERLIB    SYSTRIGUPD  QSYS        QADBXTRIGB  DATA
OTHERLIB    SYSVIEWDEP  QSYS        QADBXREF    DATA
OTHERLIB    SYSVIEWS    QSYS        QADBXREF    DATA

I confess that I have not included all the results for the library OTHERLIB. Many of the dependent objects have more than one parent. I just wanted to list each dependent object once. And now I have a dependency type of 'F'.

These dependent objects are automatically created if I use the Create Schema SQL statement. I don't want to include those in my final results. I don't want to exclude all dependent objects that start with 'SYS', just these ones.

My revised SQL statement now looks like:

01  SELECT DBFLDP AS "DEPENDENT_LIBRARY",
02         DBFFDP AS "DEPENDENT_NAME",
03         DBFLIB AS "PARENT_LIBRARY",
04         DBFFIL AS "PARENT_NAME",
05         CASE DBFTDP
06           WHEN 'D' THEN 'DATA'
07           WHEN 'V' THEN 'VIEW'
08           WHEN 'I' THEN 'INDEX'
09           ELSE DBFTDP
10         END AS "DEPENDENCY"
11    FROM QSYS.QADBFDEP
12   WHERE DBFLDP NOT LIKE 'Q%' 
13     AND DBFLDP NOT LIKE 'SYS%'
14     AND DBFFDP NOT IN('SYSCHKCST','SYSCOLUMNS','SYSCST','SYSCSTCOL','SYSFIELDS',
15                       'SYSCSTDEP','SYSINDEXES','SYSKEYS','SYSKEYCST','SYSPACKAGE',
16                       'SYSPACKAGE','SYSREFCST','SYSTABDEP','SYSTABLES','SYSTRIGCOL',
17                       'SYSTRIGDEP','SYSTRIGGER','SYSTRIGUPD','SYSVIEWDEP','SYSVIEWS')
18     AND DBFLIB <> DBFLDP  
19   ORDER BY 1,2

I inserted the following:

Lines 14 – 17: The NOT IN means that I only want dependent object names that are not in the list that follows.

This produced the following result:

DEPENDENT_  DEPENDENT_  PARENT_     PARENT_
LIBRARY     NAME        LIBRARY     NAME        DEPENDENCY
----------  ----------  ----------  ----------  ----------
MYLIB2      TESTFILE1   MYLIB       TESTFILE    DATA

While I have the result for the logical file in MYLIB2 library, I do not have a result for QTEMP. As I suspected when I started this, like many other SQL views, QADBFDEP does not include data for objects in QTEMP. As far as this scenario goes any objects in QTEMP are not a cause for concern as they would be deleted when the job completes.

So now I have an answer for the question: Are there any dependent objects in a different library to its parent. While the answer is "Yes", fortunately in this partition there is just one object and I created it for this example.

 

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

3 comments:

  1. Fantastic and very powerful! Thanks, Simon! /Magnus Gullö, Apper, Sweden

    ReplyDelete
  2. This would be nice if there was a view that wasn't locked out based on it's authority. I don't have enough authority (as most programmers should) but I cannot run this query. It seems like there isn't a good view to see these. I can use SYSVEWDEP to see all views and DDS LFs but not indexes.

    ReplyDelete
  3. I recalled from another article wrote using the RELATED_OBJECTS system tool. When I ran this test, it showed me the one file that was out of place. We have limited authority, so I was thankful this worked.

    SELECT SQL_OBJECT_TYPE,SCHEMA_NAME,SQL_NAME,
    OBJECT_OWNER,SQL_OBJECT_TYPE
    FROM TABLE(SYSTOOLS.RELATED_OBJECTS('TESTLIB','TESTFILE'))
    where SCHEMA_NAME <> 'TESTLIB'

    https://www.rpgpgm.com/2020/12/new-sql-table-function-lists-all.html

    ReplyDelete

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.