Skip to content

Instantly share code, notes, and snippets.

@jsranko

jsranko/VIEWDEPS.sql

Last active Jun 8, 2020
Embed
What would you like to do?
Get View dependencies (DB2 for IBM i)
-- UDTF:VIEW_DEPENDENCIES determines view dependencies
Create or Replace Function SI_VIEW_DEPENDENCIES(File Varchar(10),
Library Varchar(10))
Returns Table (Level Integer,
File Varchar(10),
Library Varchar(10))
Language Sql
Specific SIVWDEPS
Not Deterministic
No External Action
Returns Null On Null Input
Set Option Dbgview = *Source
Begin Atomic
Return
With base As (
Select dbffil , dbflib , dbffdp , dbfldp , dbftdp
From qadbfdep
Group By dbffil , dbflib , dbffdp , dbfldp , dbftdp
), Dependents (calllevel, dbffil, dbflib, dbffdp, dbfldp) As (
Select 1 As calllevel , dbffil , dbflib , dbffdp , dbfldp
From base
Where dbffil = File And dbflib = Library
Union All
Select calllevel + 1 As calllevel, b.dbffil, b.dbflib, b.dbffdp, b.dbfldp
From Dependents a
Join base b
On (a.dbffdp, a.dbfldp) = (b.dbffil, b.dbflib)
), dependencies As (
Select calllevel, dbffdp, dbfldp
From Dependents
Group By calllevel, dbffdp, dbfldp
)
Select *
From dependencies
Order By calllevel, dbffdp Asc
;
End
;
-- Run it
select * from table(SI_VIEW_DEPENDENCIES('SYSVIEWS', 'QSYS2')) as a
;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment