Wednesday, December 2, 2020

New SQL table function lists all dependent objects

related_objects for pf and sql table dependent objects

This is one of my favorites in the latest round of Technology Refreshes, IBM i 7.4 TR3 and 7.3 TR9. The RELATED_OBJECTS table function lists the objects dependent upon the one given in its parameters.

I am not going to give the source code for all the objects I mention here. I will give you links to other posts where I describe how to create and use these different objects types.

I can get lists of dependent objects on a physical files using various CL commands, but this gives me a complete set of results in one place. And I am all for keeping things simple, KISS.

RELATED_OBJECTS has two mandatory parameters that need to be passed to it:

  1. Library: Must be the library's name. Cannot use "*LIBL".
  2. File name: Only physical files and SQL DDL tables can be used.

When I enter the SQL Select statement I can them with either the parameter names or without. You regular readers will know which version I will use.

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

02  SELECT * FROM TABLE(SYSTOOLS.RELATED_OBJECTS('MYLIB','TESTFILE')) ;

When you see the results you will understand why I think this is something I am going to use a lot.

SOURCE_SCHEMA  SOURCE_   SQL_OBJECT_TYPE     SCHEMA   SQL_NAME
_NAME          SQL_NAME                      _NAME
-------------  --------  ------------------  -------  --------------------
MYLIB          TESTFILE  KEYED LOGICAL FILE   MYLIB   TESTFILEL0
MYLIB          TESTFILE  LOGICAL FILE         MYLIB   TESTFILEL1
MYLIB          TESTFILE  INDEX	              MYLIB   TESTFILE_SQL_INDEX
MYLIB          TESTFILE  VIEW                 MYLIB   TESTFILE_SQL_VIEW
MYLIB          TESTFILE  ALIAS                MYLIB   TESTFILE_SQL_ALIAS
MYLIB          TESTFILE  TRIGGER              MYLIB   TESTFILE_SQL_TRIGGER


LIBRARY  SYSTEM_NAME  OBJECT  LONG_COMMENT           OBJECT_TEXT
_NAME                 _OWNER
-------  -----------  ------  ---------------------  ---------------------
MYLIB    TESTFILEL0   SIMON   TESTFILE logical file  TESTFILE logical file
MYLIB    TESTFILEL1   SIMON   TESTFILE logical file  TESTFILE logical file
MYLIB    TESTINDEX    SIMON   TESTFILE index         TESTFILE index
MYLIB    TESTVIEW     SIMON   TESTFILE view          TESTFILE view
MYLIB    TESTF00001   SIMON   -                      TESTFILE alias
MYLIB    TESTF00001   SIMON   -	                     -


LAST_ALTERED
--------------------------
2020-11-31 13:39:13.095000
2020-11-31 13:52:02.921000
2020-11-31 13:50:34.074000
2020-11-31 13:49:59.059000
2020-11-31 13:59:09.308000
2020-11-31 14:08:36.000000

I have wrapped some of the column names to help make this fit in the width of this page.

The first two columns show the library and the system name of the file passed to RELATED_OBJECTS.

This has returned more than just the dependent files or triggers on this file. The third column, SQL_OBJECT_TYPE, shows it returns all of dependent objects on the physical file. This is not a definitive list as this table function returns more objects types. These are just the ones I commonly use. You can see list of object types in the IBM documentation, which you can read by clicking on the link at the end of this post.

The explanation of the values shown in this column are:

SCHEMA_NAME if I had created the library the dependent objects is in as a schema its name would be here. Otherwise it is the object's library name.

SQL_NAME this is what I call the long object name.

LIBRARY_NAME is the system library name that the dependent object is in.

SYSTEM_NAME is the what I call the short object name.

OBJECT_OWNER I think the column's name explains the function of this column.

LONG_COMMENT seeing the results I am not sure where this comes from. For logical files, indexes, and view it is the same as the object's description.

OBJECT_TEXT another column whose purpose we well described by its name.

LAST_ALTERED is the date and time the object was created or the objected was changed.

In my last example I am going to show that I can get the name of the history file from a SQL temporal table.

SELECT * FROM TABLE(SYSTOOLS.RELATED_OBJECTS('MYLIB2','TMPRLTAB'))

The results from the above statement are:

SOURCE_SCHEMA  SOURCE_   SQL_OBJECT_TYPE     SCHEMA        SQL_NAME
_NAME          SQL_NAME                      _NAME
-------------  --------  ------------------  ------------  ----------
MYLIB          TMPRLTAB  HISTORY TABLE       SIMON_SCHEMA  TMPRLTAB_H


LIBRARY  SYSTEM_NAME  OBJECT  LONG_    OBJECT_TEXT
_NAME                 _OWNER  COMMENT
-------  -----------  ------  -------  ----------------------
MYLIB2   TMPRLTAB_H   SIMON   -        Temporal table history


LAST_ALTERED
--------------------------
2020-11-31 14:57:35.510000

SQL_OBJECT_TYPE this is the name of the SQL table that contains the history data from the temporal table.

SCHEMA_NAME I created this library as a schema with the following SQL statement:

CREATE SCHEMA SIMON_SCHEMA FOR SCHEMA "MYLIB2"

LIBRARY_NAME is the library name derived from the CREATE SCHEMA statement.

I am certainly going to be using this as a better alternative to find object dependencies. Maybe I will share the program I create in a future post.

 

You can learn more about the RELATED_OBJECTS command from the IBM website here.

 

This article was written for IBM i 7.4 TR3 and 7.3 TR9.

6 comments:

  1. I'm really curious about how the "last altered date" has a value of November 31, since November only has 30 days in it???

    ReplyDelete
    Replies
    1. That is a deliberate mistake to see if anyone really looks at the results. Thank you letting me know as I now know that you are thorough to look at the results.

      Delete
    2. Sneaky! ;-)

      Thanks for the article.

      Delete
  2. 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.