I am developing a tool to retrieve the DDL statements used for creating the views of the given table. Initially, I was extracting the view scripts of the given Table by retrieving the TEXT column of the SYSIBM.SYSVIEWS table by joining with the SYSIBM.SYSVIEWDEP table using the given table name and schema.
Now I have a requirement in which I have to retrieve the texts all the dependent views on this table i.e., the views which are dependent on the views created using the original tables. I have even coded a SQL statement for that too. PFB the code for that statement
My query is what if I want to extract the views that are dependent on the view's view or view's view's view and so on.
Let me rephrase it
I want to extract the create text of the all the views that are associated with the given table irrespective of whether it is dependent of the table directly or to the views of the table. What can I do?
with CTE(cnt, creator, name, depcreator, depname) as
(select 1 , DCREATOR ,DNAME, bcreator, bname
from sysibm.sysviewdep
where bname = ? and BCREATOR = ?
union all
select cte.cnt + 1 , dcreator, dname, bcreator, bname
from sysibm.sysviewdep, cte
where BCREATOR= cte.creator and BNAME = cte.name
and cte.cnt < 5)
select cte.cnt, v.creator, v.name
, cast(v.text as varchar(10000))
from cte
, sysibm.sysviews V
where v.name = cte.name and v.creator = cte.creator
