SQLObjectDependencyCheckingOnDB2forIBMi.sql

--http://www.itjungle.com/fhg/fhg072115-story01.html

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 section below if using IBM i 7.1 or less */
/* START 7.2 only */
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
/* END 7.2 only */
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