Recursive query for view dependencies and lineage

Question

I want to see all of the dependencies for all views, and have an anchor field for the path as well.

I've gotten it to this point, where TEST_RECURSIVE_SEARCH is created by scraping the view definitions.

CREATE MULTISET VOLATILE TABLE TEST_RECURSIVE_SEARCH

  ( Databasename VARCHAR(100),

    EntityName VARCHAR(100),

    RLTD_Databasename VARCHAR(100),

    RLTD_EntityName VARCHAR(100),

    RLTD_REASON VARCHAR(100)

  )

PRIMARY INDEX (databasename, entityName)

ON COMMIT PRESERVE ROWS;

INSERT INTO TEST_RECURSIVE_SEARCH VALUES ('DB1','TopLevelEntity','DB1','SecondLevelEntity','READS FROM');

INSERT INTO TEST_RECURSIVE_SEARCH VALUES ('DB1','SecondLevelEntity','DB1','ThirdLevelEntity','READS FROM');

INSERT INTO TEST_RECURSIVE_SEARCH VALUES ('DB1','ThirdLevelEntity','DB1','FourthLevelEntity','READS FROM');

INSERT INTO TEST_RECURSIVE_SEARCH VALUES ('DB1','FourthLevelEntity','DB1','FifthLevelEntity','READS FROM');



WITH RECURSIVE REC_SUR

  ( DatabaseName,

    EntityName,

    NestDependentDB,

    NestedDependentEntity

  ) AS

  ( SELECT TRS1.Databasename,

           TRS1.EntityName,

           TRS2.databasename,

           TRS2.EntityName

      FROM TEST_RECURSIVE_SEARCH AS TRS1

     INNER

      JOIN TEST_RECURSIVE_SEARCH AS TRS2

        ON TRS1.Rltd_databasename = TRS2.Databasename

       AND TRS1.Rltd_entityName = TRS2.entityName

     UNION

       ALL

    SELECT REC_SUR.Databasename,

           REC_SUR.EntityName,

           TRSN.rltd_databasename,

           TRSN.Rltd_EntityName

      FROM REC_SUR

     INNER

      JOIN TEST_RECURSIVE_SEARCH AS TRSN

        ON REC_SUR.NestDependentDB = TRSN.Databasename

       AND REC_SUR.NestedDependentEntity = TRSN.entityName

  )

SELECT * 

  FROM REC_SUR

-- Pick up the last level which won't have a relationship

 UNION

   ALL

 SELECT TRS1.databasename,

        TRS1.entityName,

        TRS1.rltd_databasename,

        TRS1.rltd_entityName

   FROM TEST_RECURSIVE_SEARCH AS TRS1

   LEFT

   JOIN TEST_RECURSIVE_SEARCH AS TRS2

     ON TRS1.Rltd_databasename = TRS2.Databasename

    AND TRS1.Rltd_entityName = TRS2.entityName   

  WHERE TRS2.databasename IS NULL);

Which gives me all of the entities dependent on the view, but no context or way to retrace the path.

I'm trying to get this as the output:

DatabaseName    EntityName      NestDependentDB NestedDependentEntity DependentThroughEntity  

DB1     FourthLevelEntity       DB1     FifthLevelEntity  FourthLevelEntity           

DB1     SecondLevelEntity       DB1     ThirdLevelEntity  SecondLevelEntity      

DB1     SecondLevelEntity       DB1     FourthLevelEntity ThirdLevelEntity      

DB1     ThirdLevelEntity        DB1     FourthLevelEntity ThirdLevelEntity      

DB1     ThirdLevelEntity        DB1     FifthLevelEntity  FourthLevelEntity      

DB1     SecondLevelEntity       DB1     FifthLevelEntity  FourthLevelEntity      

DB1     TopLevelEntity          DB1     FifthLevelEntity  FourthLevelEntity       

DB1     TopLevelEntity          DB1     FourthLevelEntity ThirdLevelEntity      

DB1     TopLevelEntity          DB1     SecondLevelEntity TopLevelEntity        

DB1     TopLevelEntity          DB1     ThirdLevelEntity  SecondLevelEntity

Also, if you have any thoughts on how to eliminate that UNION to pick up the bottom tier records I'd appreciate that too.

user_120504 · Answer

This should accomplish something similar with the DBC hierarchy:

WITH RECURSIVE cte (DatabaseName, Path, Parent, Level) AS
(
 SELECT TRIM(d1.DatabaseName)
      , d1.DatabaseName (VARCHAR(625))
      , TRIM(d1.DatabaseName)
      , 0 (BYTEINT)
  FROM DBC.DatabasesV d1
  WHERE DatabaseName = DBC

 UNION ALL

 SELECT TRIM(d2.DatabaseName)
      , cte.Path || '.' || TRIM(d2.DatabaseName)
      , cte.Path
      , cte.Level + 1
   FROM DBC.DatabasesV d2
      , cte
  WHERE d.OwnerName = cte.DatabaseName
    AND d.DatabaseName <> d.OwnerName
    AND cte.Level < 20 -- This is a failsafe for the recursion
)
SELECT Level
     , SUBSTRING(CAST('' AS CHAR(60) FROM 1 FOR Level * 2) || DatabaseName AS Hierarchy
     , Path
     , Parent
  FROM cte
 ORDER BY Path;

Recursive query for view dependencies and lineage

1 Answers