Object Dependency Tracking In DB2 For i

The DB2 for i catalog tables and views present a plethora of valuable information about the various SQL objects defined on your system and their relationship to one another. In this tip, I discuss the dependency tracking views. If you ever wanted to make changes to one of your application’s primary SQL objects (such as a table, view or procedure) but feared the unknown (what related objects are affected?), then it’s time to investigate these special catalog views.

What Are The Dependency Catalog Views?

The “dependency” catalog views under consideration are:

SYSROUTINEDEP–Records the objects used by stored procedures and functions. The types of objects includes ALIAS, FUNCTION, INDEX, MATERIALIZED QUERY TABLE (MQT), PROCEDURE, SCHEMA, SEQUENCE, TABLE, TYPE, VARIABLE, and VIEW.

SYSTRIGDEP–Records the objects needed by triggers. The types of objects includes ALIAS, FUNCTION, INDEX, MATERIALIZED QUERY TABLE (MQT), PACKAGE, PROCEDURE, SCHEMA, SEQUENCE, TABLE, TYPE, VARIABLE, and VIEW.

SYSVARIABLEDEP (available in IBM i 7.2)–Indicates the objects that a global variable utilizes including ALIAS, FUNCTION, MQT, SCHEMA, SEQUENCE, TABLE, TYPE, VARIABLE, and VIEW.

SYSVIEWDEP–Reports the objects that views depend on including FUNCTION, MQT, TABLE, TYPE, VARIABLE, and VIEW.

SYSTABLEDEP–Indicates the objects required by a materialized query table definition including FUNCTION, TABLE, TYPE, and VIEW.

All of these views are in the QSYS2 schema.

Using The Dependency Catalog Views

To begin, any of the dependency catalogs can reveal what objects an SQL object depends on. For example, to find out what objects the function GET_CUSTOMER_GROSS_SALES depends on, just query the SYSROUTINEDEP view:

SELECT OBJECT_SCHEMA,OBJECT_NAME,OBJECT_CATALOG,OBJECT_TYPE
  FROM QSYS2.SYSROUTINEDEP 
 WHERE SPECIFIC_SCHEMA='ADVWORKS'
   AND SPECIFIC_NAME='GET_CUSTOMER_GROSS_SALES'

The results indicate this function depends on five SQL objects:

Of course, you can also query a catalog view for the opposite relationship. For example, say you want to change function GET_CUSTOMER_GROSS_SALES and you want to know what routines depend on it. Checking if another routine uses this function is as easy as:

SELECT SPECIFIC_SCHEMA,SPECIFIC_NAME
  FROM SYSROUTINEDEP
 WHERE OBJECT_SCHEMA='ADVWORKS'
   AND OBJECT_NAME='GET_CUSTOMER_GROSS_SALES'

The same querying principle can be applied to any of the dependency views. If you want to find out what an MQT depends on or what depends on a given MQT, then query SYSTABLEDEP. To find out what a view depends on or what depends on a view, query SYSVIEWDEP.

Following The Dependency Chain

“What depends on object x?” The one drawback with asking that question is that any number of object types may depend on “object x”. For example, the above query against SYSROUTINEDEP can tell us what routines depend on function GET_CUSTOMER_GROSS_SALES, but it won’t tell us what views or variables, if any, use it. Therefore, you have to query each dependency catalog looking for dependencies on a given object. Then another question comes up: “If I change those dependent objects or want to test them, how do I know what uses them?” A recursive query can answer that question by following the dependency chain.

The recursive common table expression below is my first attempt at recursively querying all of the dependency catalogs for a given object:

WITH DEPENDENCY_CHAIN_BASE 
(REQ_OBJECT_SCHEMA,REQ_OBJECT_NAME,REQ_OBJECT_TYPE,
DEP_OBJECT_SCHEMA,DEP_OBJECT_NAME,DEP_OBJECT_TYPE,       
DEP_OBJECT_SYSTEM_SCHEMA,DEP_OBJECT_SYSTEM_NAME,    -- Not populated for routines
REQ_OBJECT_CATALOG,DEP_OBJECT_DEFINER,DEP_OBJECT_PARM_SIGNATURE,REQ_OBJECT_PARM_SIGNATURE)
AS (
/* Remove this section prior to IBM i 7.2 */
SELECT D.OBJECT_SCHEMA AS REQ_OBJECT_SCHEMA,D.OBJECT_NAME AS 
REQ_OBJECT_NAME,D.OBJECT_TYPE AS REQ_OBJECT_TYPE,
       D.VARIABLE_SCHEMA AS DEP_OBJECT_SCHEMA,D.VARIABLE_NAME AS DEP_OBJECT_NAME,
	   'VARIABLE' AS DEP_OBJECT_TYPE,       
       SYSTEM_VAR_SCHEMA AS DEP_OBJECT_SYSTEM_SCHEMA,SYSTEM_VAR_NAME AS 
	   DEP_OBJECT_SYSTEM_NAME,
       '' AS REQ_OBJECT_CATALOG,V.VARIABLE_DEFINER AS DEP_OBJECT_DEFINER,
       D.PARM_SIGNATURE AS DEP_OBJECT_PARM_SIGNATURE,CAST(NULL AS VARCHAR(10000) 
	   FOR BIT DATA) AS REQ_OBJECT_PARM_SIGNATURE
  FROM SYSVARIABLEDEP D
  JOIN SYSVARIABLES V ON V.VARIABLE_NAME=D.VARIABLE_NAME
                     AND V.VARIABLE_SCHEMA=D.VARIABLE_SCHEMA
UNION ALL
SELECT D.OBJECT_SCHEMA AS REQ_OBJECT_SCHEMA,D.OBJECT_NAME AS 
REQ_OBJECT_NAME,D.OBJECT_TYPE AS REQ_OBJECT_TYPE,
       D.TABLE_SCHEMA AS DEP_OBJECT_SCHEMA,D.TABLE_NAME AS DEP_OBJECT_NAME,
	   'MATERIALIZED QUERY TABLE' AS DEP_OBJECT_TYPE,       
       D.SYSTEM_TABLE_SCHEMA AS DEP_OBJECT_SYSTEM_SCHEMA,D.SYSTEM_TABLE_NAME 
	   AS DEP_OBJECT_SYSTEM_NAME,
       '' AS REQ_OBJECT_CATALOG,T.TABLE_DEFINER AS DEP_OBJECT_DEFINER,
       D.PARM_SIGNATURE AS DEP_OBJECT_PARM_SIGNATURE,CAST(NULL AS VARCHAR(10000)
	   FOR BIT DATA) AS REQ_OBJECT_PARM_SIGNATURE
  FROM SYSTABLEDEP D
  JOIN SYSTABLES T ON T.TABLE_NAME=D.TABLE_NAME
                  AND T.TABLE_SCHEMA=D.TABLE_SCHEMA
UNION ALL
SELECT D.EVENT_OBJECT_SCHEMA AS REQ_OBJECT_SCHEMA,D.EVENT_OBJECT_TABLE 
AS REQ_OBJECT_NAME,
       CASE T.TABLE_TYPE WHEN 'A' THEN 'ALIAS'
            WHEN 'L' THEN 'LF'
            WHEN 'M' THEN 'MATERIALIZED QUERY TABLE'
            WHEN 'P' THEN 'PF'
            WHEN 'T' THEN 'TABLE'
            WHEN 'V' THEN 'VIEW'
            ELSE 'OTHER' END AS REQ_OBJECT_TYPE,
       D.TRIGGER_SCHEMA AS DEP_OBJECT_SCHEMA,D.TRIGGER_NAME AS DEP_OBJECT_NAME,
	   'TRIGGER' AS DEP_OBJECT_TYPE,
       SYSTEM_TRIGGER_SCHEMA AS DEP_OBJECT_SYSTEM_SCHEMA,TRIGGER_PROGRAM_NAME 
	   AS DEP_OBJECT_SYSTEM_NAME,
       BASE_TABLE_CATALOG AS REQ_OBJECT_CATALOG,D.TRIGGER_DEFINER AS 
	   DEP_OBJECT_DEFINER,
       CAST(NULL AS VARCHAR(10000) FOR BIT DATA) AS 
	   DEP_OBJECT_PARM_SIGNATURE,CAST(NULL AS VARCHAR(10000) FOR BIT DATA) AS 
	   REQ_OBJECT_PARM_SIGNATURE
  FROM SYSTRIGGERS D
  JOIN SYSTABLES T ON T.TABLE_NAME=D.EVENT_OBJECT_TABLE
                  AND T.TABLE_SCHEMA=D.EVENT_OBJECT_SCHEMA
UNION ALL
SELECT D.OBJECT_SCHEMA AS REQ_OBJECT_SCHEMA,D.OBJECT_NAME AS 
REQ_OBJECT_NAME,D.OBJECT_TYPE AS REQ_OBJECT_TYPE,
       D.TRIGGER_SCHEMA AS DEP_OBJECT_SCHEMA,D.TRIGGER_NAME AS 
	   DEP_OBJECT_NAME,'TRIGGER' AS DEP_OBJECT_TYPE,       
       D.SYSTEM_TRIGGER_SCHEMA,T.TRIGGER_PROGRAM_NAME,
       '' OBJECT_CATALOG,T.TRIGGER_DEFINER,
       D.PARM_SIGNATURE AS DEP_OBJECT_PARM_SIGNATURE,CAST(NULL AS VARCHAR(10000) 
	   FOR BIT DATA) AS REQ_OBJECT_PARM_SIGNATURE
  FROM SYSTRIGDEP D
  JOIN SYSTRIGGERS T ON T.TRIGGER_SCHEMA=D.TRIGGER_SCHEMA AND 
  T.TRIGGER_NAME=D.TRIGGER_NAME
UNION ALL
SELECT D.OBJECT_SCHEMA AS REQ_OBJECT_SCHEMA,D.OBJECT_NAME AS 
REQ_OBJECT_NAME,D.OBJECT_TYPE AS REQ_OBJECT_TYPE,
       D.VIEW_SCHEMA AS DEP_OBJECT_SCHEMA,VIEW_NAME AS DEP_OBJECT_NAME,'VIEW' 
	   AS DEP_OBJECT_TYPE,
       D.SYSTEM_VIEW_SCHEMA,D.SYSTEM_VIEW_NAME,
       '' OBJECT_CATALOG,V.VIEW_DEFINER,
       D.PARM_SIGNATURE AS DEP_OBJECT_PARM_SIGNATURE,CAST(NULL AS VARCHAR(10000) 
	   FOR BIT DATA) AS REQ_OBJECT_PARM_SIGNATURE
  FROM SYSVIEWDEP D
  JOIN SYSVIEWS V ON V.TABLE_SCHEMA=D.VIEW_SCHEMA 
                 AND V.TABLE_NAME=D.VIEW_NAME
UNION ALL
SELECT D.OBJECT_SCHEMA AS REQ_OBJECT_SCHEMA,D.OBJECT_NAME AS 
REQ_OBJECT_NAME,D.OBJECT_TYPE AS REQ_OBJECT_TYPE,
       R.ROUTINE_SCHEMA AS DEP_OBJECT_SCHEMA,R.ROUTINE_NAME AS 
	   DEP_OBJECT_NAME,R.ROUTINE_TYPE AS DEP_OBJECT_TYPE,
       '' SYSTEM_VIEW_NAME,
       '' SYSTEM_VIEW_SCHEMA,
       OBJECT_CATALOG AS REQ_OBJECT_CATALOG,R.ROUTINE_DEFINER,
       D.PARM_SIGNATURE AS DEP_OBJECT_PARM_SIGNATURE,R.PARM_SIGNATURE AS 
	   REQ_OBJECT_PARM_SIGNATURE
  FROM SYSROUTINEDEP D
  JOIN SYSROUTINES R ON R.SPECIFIC_NAME=D.SPECIFIC_NAME 
                    AND R.SPECIFIC_SCHEMA=D.SPECIFIC_SCHEMA
),
DEPENDENCY_CHAIN_TOP AS (
SELECT REQ_OBJECT_SCHEMA,REQ_OBJECT_NAME,REQ_OBJECT_TYPE,
DEP_OBJECT_SCHEMA,DEP_OBJECT_NAME,DEP_OBJECT_TYPE,       
DEP_OBJECT_SYSTEM_SCHEMA,DEP_OBJECT_SYSTEM_NAME,
REQ_OBJECT_CATALOG,DEP_OBJECT_DEFINER,
DEP_OBJECT_PARM_SIGNATURE,REQ_OBJECT_PARM_SIGNATURE,
1 AS LEVEL
  FROM DEPENDENCY_CHAIN_BASE
 WHERE REQ_OBJECT_SCHEMA IN ('*LIBL','ADVWORKS')
   AND REQ_OBJECT_NAME='PRODUCT'  
   AND REQ_OBJECT_TYPE='TABLE'  -- Optional
),
DEPENDENCY_CHAIN (REQ_OBJECT_SCHEMA,REQ_OBJECT_NAME,REQ_OBJECT_TYPE,
DEP_OBJECT_SCHEMA,DEP_OBJECT_NAME,DEP_OBJECT_TYPE,       
DEP_OBJECT_SYSTEM_SCHEMA,DEP_OBJECT_SYSTEM_NAME,    -- Not populated for routines
REQ_OBJECT_CATALOG,DEP_OBJECT_DEFINER,DEP_OBJECT_PARM_SIGNATURE,
REQ_OBJECT_PARM_SIGNATURE,LEVEL)
AS (
SELECT REQ_OBJECT_SCHEMA,REQ_OBJECT_NAME,REQ_OBJECT_TYPE,
DEP_OBJECT_SCHEMA,DEP_OBJECT_NAME,DEP_OBJECT_TYPE,       
DEP_OBJECT_SYSTEM_SCHEMA,DEP_OBJECT_SYSTEM_NAME,
REQ_OBJECT_CATALOG,DEP_OBJECT_DEFINER,DEP_OBJECT_PARM_SIGNATURE,
REQ_OBJECT_PARM_SIGNATURE,LEVEL
  FROM DEPENDENCY_CHAIN_TOP
UNION ALL
SELECT d.REQ_OBJECT_SCHEMA,d.REQ_OBJECT_NAME,d.REQ_OBJECT_TYPE,
d.DEP_OBJECT_SCHEMA,d.DEP_OBJECT_NAME,d.DEP_OBJECT_TYPE,       
d.DEP_OBJECT_SYSTEM_SCHEMA,d.DEP_OBJECT_SYSTEM_NAME,
d.REQ_OBJECT_CATALOG,d.DEP_OBJECT_DEFINER,d.DEP_OBJECT_PARM_SIGNATURE,
d.REQ_OBJECT_PARM_SIGNATURE,b.LEVEL+1 AS LEVEL
  FROM DEPENDENCY_CHAIN b
  JOIN DEPENDENCY_CHAIN_BASE d ON d.REQ_OBJECT_SCHEMA IN (b.DEP_OBJECT_SCHEMA,'*LIBL')
                              AND d.REQ_OBJECT_NAME=b.DEP_OBJECT_NAME
                              AND (d.DEP_OBJECT_PARM_SIGNATURE=b.REQ_OBJECT_
                              PARM_SIGNATURE OR b.REQ_OBJECT_PARM_SIGNATURE IS NULL)
)
SEARCH DEPTH FIRST BY DEP_OBJECT_SCHEMA,DEP_OBJECT_NAME SET SortOrder
CYCLE DEP_OBJECT_SCHEMA,DEP_OBJECT_NAME,DEP_OBJECT_TYPE
SET DuplicateError To '*' Default ' '
SELECT d.*,DuplicateError
  FROM DEPENDENCY_CHAIN d
ORDER BY SortOrder

This query was tested on IBM i 7.2 but should also work for prior versions of IBM i. The blue lines in the query for global variables require IBM i 7.2; so if you’re on an OS level prior to 7.2, this section of code will need to be removed. Also, the lines highlighted in red contain the filter criteria (object schema, name and type) that should be changed when querying dependencies on various objects.

The results of the query (without a few of the columns) are shown below:

This query is big and ugly. Admittedly, it is my first attempt and I probably missed some things. So while it’s not guaranteed 100% accurate, it should at least get you started on locating SQL object dependencies. Also, if you have a large number of dependencies this query may be slow.

Limitations Of The Dependency Catalogs

Here are a few things to note:

• When a routine is created with system naming, the OBJECT_SCHEMA column for the dependent objects will contain the special value *LIBL (library list).

• Routines can be overloaded (i.e., same user-defined name but different parameter list) such that ambiguity can arise over the name of an overloaded procedure. Each routine also has a specific (i.e., unique) name that the developer can assign or is generated by DB2. For example, consider the following two functions with the same name but different parameter signatures:

CREATE OR REPLACE FUNCTION ADVWORKS.PRODUCT_PRICE (
@PRODUCT_ID INT,
@IN_DATE DATE)
RETURNS DEC(19,4)

CREATE OR REPLACE FUNCTION ADVWORKS.PRODUCT_PRICE (
@PRODUCT_ID INT,
@IN_DATE DEC(8,0))
RETURNS DEC(19,4)

Each of these routines will be assigned a unique “specific” name. The dependency catalogs do not store the specific name of a routine, rather they store the potentially duplicate “object” name. To resolve this ambiguity, a column called PARM_SIGNATURE is used to resolve the specific function.

• While external routines such as external RPG functions are tracked when used by an SQL routine, the objects within the external routines are not recorded in the dependency catalogs. In other words, if external RPG user-defined function utilizes TABLE1 and TABLE2, these dependencies are not explicitly recorded in a dependency view. However, the catalog views SYSPROGRAMSTMTSTAT and SYSPACKAGESTMTSTAT contain the pre-compiled statements within an embedded SQL program or SQL package such that a manual text search for a particular object can be performed with relative ease.

• Dependencies based on dynamic SQL statements are not recorded in a catalog view.

• When three part naming is specified, the “catalog” column of the views are populated with the name of the relational database entry. For example, if a procedure references table PARTITION2.ADVWORKS.SALESORDER, the SYSROUTINEDEP view’s OBJECT_CATALOG column for this table will contain “PARTITION2”.

• There are other dependencies such as foreign key and CHECK constraints that are not included in this tip.

• The LIBRARY LIST concept can throw a wrench in the sample recursive query shown above. If a copy of a table exists in multiple libraries, it may show up several times in the results if, for instance, a routine users the library list.

Limitations Of The Dependency Catalogs

The dependency views can give quick insight into the relationships among various SQL objects on a given partition.

Michael Sansoterra is a DBA for Broadway Systems in Grand Rapids, Michigan. Send your questions or comments for Mike via the IT Jungle Contact page.

RELATED STORY

Recursive Queries on the iSeries and System i