New SQL table function lists all 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:

• KEYED LOGICAL FILE:  DDS logical file with a key
• LOGICAL FILE:  DDS logical file without a key
• INDEX:  SQL index
• VIEW:  SQL view
• ALIAS:  SQL alias
• TRIGGER:  SQL trigger

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.