Start of change

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.

Read syntax diagramSkip visual syntax diagram RELATED_OBJECTS ( LIBRARY_NAME =>  library-name , FILE_NAME =>  file-name )
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;
End of change