RELATED_OBJECTS table function

The RELATED_OBJECTS table function returns a list of all objects that depend on the specified database file, either directly or indirectly.

The list contains all objects that are directly dependent on the input database file. Each identified view and global variable is processed recursively to obtain its dependents until no more dependents are found.

If the input file is an SQL alias, a program described file, or the file is not found, no rows are returned.

The dependency information is collected from the database cross reference tables and the SQL catalog. Some dependencies might not be returned based on how the dependent object was specified in the original statement. For example, if a CREATE FUNCTION statement references an unqualified table, the dependency will not be complete in the SQL catalog view (SYSROUTINEDEP) and subsequently will not be returned by this function.

Authorization: See Note below.

The schema is SYSTOOLS.

library-name: A character or graphic string expression that identifies the library that contains file-name. It must exist on the current server.
file-name: A character or graphic string expression that identifies the database file to list related objects for. This must be the system name of the file.

The result of the function is a table containing rows with the format shown in the following table. All columns are nullable.

Table 1. RELATED_OBJECTS table function
Column Name	Data Type	Description
SOURCE_SCHEMA_NAME	VARCHAR(128)	The name of the schema that contains SOURCE_SQL_NAME.
SOURCE_SQL_NAME	VARCHAR(128)	The name of the object that this row is dependent on.
SQL_OBJECT_TYPE	VARCHAR(24)	The SQL type of the dependent object. ALIAS An SQL alias defined for SOURCE_SQL_NAME FOREIGN KEY The child table in a referential constraint with SOURCE_SQL_NAME FUNCTION A function that references SOURCE_SQL_NAME HISTORY TABLE A history table associated with the temporal table defined by SOURCE_SQL_NAME INDEX An SQL index defined on SOURCE_SQL_NAME KEYED LOGICAL FILE A native keyed logical file defined on SOURCE_SQL_NAME LOGICAL FILE A native logical file defined on SOURCE_SQL_NAME MASK A column mask on a column in SOURCE_SQL_NAME or that references SOURCE_SQL_NAME in its definition MATERIALIZED QUERY TABLE A materialized query table that references SOURCE_SQL_NAME PERMISSION A row permission that is defined on SOURCE_SQL_NAME or that references SOURCE_SQL_NAME in its definition PROCEDURE A procedure that references SOURCE_SQL_NAME TEXT INDEX An OmniFind text index built over SOURCE_SQL_NAME TRIGGER An SQL trigger that is defined on SOURCE_SQL_NAME or that references SOURCE_SQL_NAME in its definition VARIABLE A global variable that references SOURCE_SQL_NAME in its default expression VIEW A view that references SOURCE_SQL_NAME in its definition XML SCHEMA An XML schema that uses SOURCE_SQL_NAME
SCHEMA_NAME	VARCHAR(128)	The SQL schema containing SQL_NAME.
SQL_NAME	VARCHAR(128)	The SQL name of the dependent object. When SQL_OBJECT_TYPE is FOREIGN KEY, this is the name of the child table
LIBRARY_NAME	VARCHAR(10)	The system library name of the dependent object. When SQL_OBJECT_TYPE is TRIGGER, the library containing the trigger program Contains the null value when SQL_OBJECT_TYPE is FOREIGN KEY, FUNCTION, PROCEDURE, and TEXT INDEX.
SYSTEM_NAME	VARCHAR(279)	The related system name of the dependent object. When SQL_OBJECT_TYPE is FOREIGN KEY, the constraint name When SQL_OBJECT_TYPE is FUNCTION or PROCEDURE, the external name When SQL_OBJECT_TYPE is MASK or PERMISSION, the mask or permission name When SQL_OBJECT_TYPE is TRIGGER, the name of the trigger program Contains the null value when SQL_OBJECT_TYPE is TEXT INDEX.
OBJECT_OWNER	VARCHAR(10)	The owner of the object. When SQL_OBJECT_TYPE is FUNCTION, MASK, PERMISSION, PROCEDURE, TRIGGER, or VARIABLE this is the object's definer. Contains the null value when SQL_OBJECT_TYPE is FOREIGN KEY or TEXT INDEX.
LONG_COMMENT	VARGRAPHIC(2000) CCSID 1200	The SQL long comment for the object. Contains the null value when the comment is not available.
OBJECT_TEXT	VARGRAPHIC(50) CCSID 1200	The system text description for the object. Contains the null value when the text is not available.
LAST_ALTERED	TIMESTAMP	The timestamp when the object was last altered. For objects that cannot be altered, this is the create timestamp. Contains the null value when SQL_OBJECT_TYPE is FOREIGN KEY.

Note

This procedure is provided in the SYSTOOLS schema as an example of how to use the SQL catalog to find dependency information using an SQL function. Similar to other Db2® for i provided tools within SYSTOOLS, the SQL source can be extracted and used as a model for building similar functions, or to create a customized version within a user-specified schema.

Services provided in SYSTOOLS have authorization requirements that are determined by the interfaces used to implement the service. To understand the authority requirements, extract the SQL for the service and examine the implementation.

Examples

List all the objects that are dependent on the ORDERS file in APPLIB.

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

List all the objects that are dependent on the ORDERS file in APPLIB. For each object, return how many recursive steps were required to find the object.

SELECT LEVEL, RO.* 
  FROM (VALUES('APPLIB', 'ORDERS')) I(IN_LIB, IN_FILE), 
        TABLE (QSYS2.OBJECT_STATISTICS(IN_LIB, '*FILE', IN_FILE)),
        TABLE(SYSTOOLS.RELATED_OBJECTS(IN_LIB, IN_FILE)) RO
  START WITH SOURCE_SCHEMA_NAME = OBJLONGSCHEMA AND  
             SOURCE_SQL_NAME = OBJLONGNAME
  CONNECT BY SOURCE_SQL_NAME = PRIOR SQL_NAME AND
             SOURCE_SCHEMA_NAME = PRIOR SCHEMA_NAME
  ORDER BY 1, 2, 3;