|
View previous topic :: View next topic
|
| Author |
Message |
Bhuvanesh Subburajan
New User
Joined: 06 Jan 2012
Posts: 2
Location: India
|
|
|
|
Hi
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
| Code: |
SQL_STMT_VDF2 = "SELECT A.TEXT FROM SYSIBM.SYSVIEWS A",
"INNER JOIN SYSIBM.SYSVIEWDEP B",
"ON A.NAME = B.DNAME AND B.BTYPE = 'V'",
"INNER JOIN SYSIBM.SYSVIEWDEP C",
"ON B.BNAME = C.DNAME",
"WHERE C.BNAME = ?",
"AND C.BCREATOR = ?"
|
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? |
|
| Back to top |
|
 |
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
write a recursive SQL :
| Code: |
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 |
|
|
| Back to top |
|
|
Bhuvanesh Subburajan
New User
Joined: 06 Jan 2012
Posts: 2
Location: India
|
|
|
|
Its working 
Thank you so much |
|
| Back to top |
|
|
|
|
  |
All times are GMT + 6 Hours |
|
