Wednesday, August 24, 2022

Using something better than DSPDBR

RELATED_OBJECTS better than DSPDBR

I am sure we have all used the Display Database Relations command, DSPDBR, to establish any file's or table's dependent objects. We can also use a SQL Table Function to give us more (better) columns of data than the DSPSBR command does.

But before I start giving examples of using this Table function I need something to work with. Let me start with a very simple DDL table:

01  CREATE TABLE MYLIB.TABLE1
02  (FIRST VARCHAR(20))

I do not need to insert data into the table.

Next I will create an Index over my Table:

01  CREATE INDEX MYLIB.TABLE1INDEX
02     FOR SYSTEM NAME "TABLE1I"
03      ON TABLE1 (FIRST) ;

04  LABEL ON INDEX TABLE1INDEX IS 'Index built over TABLE1' ;

Line 2: I have given the Index a system name as "TABLE1INDEX" is more than ten characters.

Line 4: I have labeled the index, this is the same as using the TEXT parameter in Create Physical file, CRTPF, or Create Logical file, CRTLF.

After the Index I will create a View over the Table:

01  CREATE VIEW MYLIB.TABLE1VIEW
02      AS (SELECT * FROM TABLE1) ;

03  LABEL ON TABLE TABLE1VIEW IS 'View built over TABLE1' ;

And finally I will create an Alias.

01  CREATE ALIAS MYLIB.TABLE1ALIAS
02     FOR MYLIB.TABLE1 ;

I can use the DSPDBR command to see the relationships:

DSPDBR TABLE1

Which shows me:

  MM/DD/YY             Display Data Base Relations
DSPDBR Command Input
  File  . . . . . . . . . . . . . . . . . . . : FILE       TABLE1
    Library . . . . . . . . . . . . . . . . . :            *LIBL
  Member  . . . . . . . . . . . . . . . . . . : MBR        *NONE
  Record format . . . . . . . . . . . . . . . : RCDFMT     *NONE
  Output  . . . . . . . . . . . . . . . . . . : OUTPUT     *
Specifications
  Type of file  . . . . . . . . . . . . . . . :            Physical
  File  . . . . . . . . . . . . . . . . . . . :            TABLE1
    Library . . . . . . . . . . . . . . . . . :            MYLIB
    Member  . . . . . . . . . . . . . . . . . :            *NONE
    Record format . . . . . . . . . . . . . . :            *NONE
    Number of dependent files . . . . . . . . :                2
Files Dependent On Specified File
  Dependent File         Library       Dependency   JREF    Constraint
      TABLE1I            MYLIB         Data
      TABLE1VIEW         MYLIB         Data

All it returns is the system name library of the dependent objects. Notice that the Alias does not appear in the results.

The Table function RELATED_OBJECTS, resides in the SYSTOOLS library, and requires just two parameters:

  1. LIBRARY_NAME:  Name of the library that contains the Table or file
  2. FILE_NAME:  Name of the Table or file

If I wanted to list all the information for the objects related to TABLE1 I would use the following Select statement:

SELECT * FROM TABLE(SYSTOOLS.RELATED_OBJECTS(LIBRARY_NAME=>'MYLIB', 
                                             FILE_NAME =>'TABLE1'));

I recommend you run the above statement for one of your Tables or files just to see what is contained in all of the columns. As I am limited by the width of this page the statement I am going to use is:

01  SELECT SQL_OBJECT_TYPE,SCHEMA_NAME,SQL_NAME,LIBRARY_NAME,SYSTEM_NAME,
02         OBJECT_OWNER,LAST_ALTERED
03    FROM TABLE(SYSTOOLS.RELATED_OBJECTS('MYLIB','TABLE1'));

I am not going to explain what each column contains as in my opinion the columns' names do just that.

Line 3: I do not have to use the parameters' names, I can just give the library and Table names.

My results for TABLE1 look like:

SQL_OBJECT  SCHEMA  SQL_         LIBRARY   SYSTEM      OBJECT
TYPE        _NAME   NAME         _NAME     _NAME       _OWNER
----------  ------  -----------  --------  ----------  ------
VIEW        MYLIB   TABLE1VIEW   MYLIB     TABLE1VIEW  SIMON
INDEX       MYLIB   TABLE1INDEX  MYLIB     TABLE1I     SIMON
ALIAS       MYLIB   TABLE1ALIAS  MYLIB     TABLE00001  SIMON


LAST_ALTERED
--------------------------
YYYY-MM-DD 08:44:21.452000
YYYY-MM-DD 08:44:21.420000
YYYY-MM-DD 09:26:40.493000

I have the following which is not shown on the DSPDBR display:

  • The dependent object's type
  • Dependent object's long, SQL, name
  • Dependent object's owner user profile
  • The last time the dependent object was altered

And the Alias is present! I normally build my Aliases in the library QTEMP, if I do that then RELATED_OBJECTS cannot retrieve that object's data.

The results are good information and give me more than I get from the DSPDBR command.

The next question I know we all have is: Does this work for DDS physical files too?

I need a physical file, whose definition is pretty much the same as TABLE1:

A          R PFILE1R
A            FIRST         20          VARLEN

VARLEN in the field's definition will make this field the equivalent of SQL DDL's VARCHAR, variable length character column.

And then a logical file:

A          R PFILE1R                   PFILE(PFILE1)
A          K FIRST

When I use the DSPDBR for PFILE1 I can see that I have a dependent object, but I do not know which object types it is:

  MM/DD/YY              Display Data Base Relations
DSPDBR Command Input
  File  . . . . . . . . . . . . . . . . . . . : FILE       PFILE1
    Library . . . . . . . . . . . . . . . . . :            *LIBL
  Member  . . . . . . . . . . . . . . . . . . : MBR        *NONE
  Record format . . . . . . . . . . . . . . . : RCDFMT     *NONE
  Output  . . . . . . . . . . . . . . . . . . : OUTPUT     *
Specifications
  Type of file  . . . . . . . . . . . . . . . :            Physical
  File  . . . . . . . . . . . . . . . . . . . :            PFILE1
    Library . . . . . . . . . . . . . . . . . :            MYLIB
    Member  . . . . . . . . . . . . . . . . . :            *NONE
    Record format . . . . . . . . . . . . . . :            *NONE
    Number of dependent files . . . . . . . . :                1
Files Dependent On Specified File
  Dependent File         Library       Dependency   JREF    Constraint
      PFILE1LF           MYLIB         Data

When I use the RELATED_OBJECTS Table function I can see what kind of object type that dependent object is:

SQL_OBJECT          SCHEMA  SQL_      LIBRARY   SYSTEM      OBJECT
TYPE                _NAME   NAME      _NAME     _NAME       _OWNER
------------------  ------  --------  --------  ----------  ------
KEYED LOGICAL FILE  MYLIB   PFILE1LF  MYLIB     PFILE1LF    SIMON

This is another good improvement offered, via a Table function, by IBM.

 

You can learn more about the RELATED_OBJECTS SQL Table function from the IBM website here.

 

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

4 comments:

  1. Excellent article. Way better than using QSYS.QADBFDEP.

    ReplyDelete
  2. Many thanks Simon. Worthy article as are all others written by you. Just one thing to mention. When I first tried it I got empty results. I was entering object names in lower case (which the SQL scripts in Access Client allow). I tried all names upper case and it worked. So use upper case always when using this method.

    ReplyDelete
  3. This is awesome. Thanks for sharing.

    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.