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: None required.
- 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.
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.
|
SCHEMA_NAME | VARCHAR(128) | The SQL schema containing SQL_NAME. |
SQL_NAME | VARCHAR(128) | The SQL name of the dependent object.
|
LIBRARY_NAME | VARCHAR(10) | The system library name of the dependent object.
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.
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. |
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;