How to display dependencies for a view (source tables)

[Seba]

For db2 UDB I need to get dependency information for views.

What I'm looking for is the tables that have the source information for
the view.
i.e. : if the following view was defined in my database:
create view my_view as
select parent_id, son_id
from parent A, son B
where A.parent_id = B.parent_id
Is there any way that starting from my_view I can get the information
that parent and son are the tables that are actually the source tables
for the data that make up the view?
Another *different* question is how can I get the code that generates
the view (the select query above). But please note that this will *not*

solve my problem, I am working in a programmatic environment so I can't

"parse" a bunch of SQL to find out which ones are the source tables.

What I need is a result set that contains the table names of 'parent'
and 'son' (referring to the example). Ideally, something like:
SQL> [magic query involving my_view];
TABLE
------
parent
son

What is the magic query?

Any help would be greatly appreciated. :)

S

[Serge Rielau]

Seba wrote:

For db2 UDB I need to get dependency information for views.

What I'm looking for is the tables that have the source information for
the view.
i.e. : if the following view was defined in my database:

Take a look at SYSCAT.VIEWS for the view text.
Check out SYSCAT.VIEWDEP for immediate objects the view depends on.
To find all the objects recursively check out the DB2LK_DEP_OF()
function in SYSCAT.ROUTINES. That one looks up. You can use it as a
template to get one that looks down.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Ian]

Seba wrote:

For db2 UDB I need to get dependency information for views.

What I'm looking for is the tables that have the source information for
the view.
i.e. : if the following view was defined in my database:
create view my_view as
select parent_id, son_id
from parent A, son B
where A.parent_id = B.parent_id
Is there any way that starting from my_view I can get the information
that parent and son are the tables that are actually the source tables
for the data that make up the view?
Another *different* question is how can I get the code that generates
the view (the select query above). But please note that this will *not*

solve my problem, I am working in a programmatic environment so I can't

"parse" a bunch of SQL to find out which ones are the source tables.

What I need is a result set that contains the table names of 'parent'
and 'son' (referring to the example). Ideally, something like:
SQL> [magic query involving my_view];
TABLE
------
parent
son

What is the magic query?

See SYSCAT.TABDEP

select bschema, bname
from syscat.tabdep
where tabschema = ? and tabname = ?

[BobTheDatabaseBoy]

Seba wrote:

For db2 UDB I need to get dependency information for views.

What I'm looking for is the tables that have the source information for the view.
i.e. : if the following view was defined in my database:
create view my_view as
select parent_id, son_id
from parent A, son B
where A.parent_id = B.parent_id
Is there any way that starting from my_view I can get the information
that parent and son are the tables that are actually the source tables for the data that make up the view?
Another *different* question is how can I get the code that generates
the view (the select query above). But please note that this will *not*
solve my problem, I am working in a programmatic environment so I can't
"parse" a bunch of SQL to find out which ones are the source tables.

What I need is a result set that contains the table names of 'parent'
and 'son' (referring to the example). Ideally, something like:
SQL> [magic query involving my_view];
TABLE
------
parent
son

What is the magic query?

Any help would be greatly appreciated. :)

S

if you're going to be spending a significant amount of time in
databases (DB2 or otherwise), i advise getting AQT (www.querytool.com).

if provides that specifically and a boat load of other info that
is more or less a pain to get manually.

btdb