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.