MVSFORUMS.com

nadh · Intermediate Joined: 08 Oct 2004 Posts: 192 Topics: 89

Hi

I have a view. how to know on which base tables this view is created.

thanks
nadh

kolusu · Posted: Wed Jul 13, 2005 6:23 am Post subject:

nadh,

For every view you create, DB2 stores descriptive information in several catalog tables. The following actions occur in the catalog after the execution of CREATE VIEW:

A row is inserted into SYSIBM.SYSTABLES.

A row is inserted into SYSIBM.SYSTABAUTH to record the owner's privileges on the view.

For each column of the view, a row is inserted into SYSIBM.SYSCOLUMNS.

One or more rows are inserted into the SYSIBM.SYSVIEWS table to record the text of the CREATE VIEW statement.

For each table or view on which the view is dependent, a row is inserted into SYSIBM.SYSVIEWDEP to record the dependency. You need to select the BNAME column from the table.

Check this link for SYSIBM.SYSVIEWDEP table explanation.

http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/DSNSQH11/D.80?DT=20010718164132

A row is inserted into SYSIBM.SYSVTREE, and possibly into SYSIBM.SYSVLTREE, to record the parse tree of the view (an internal representation of its logic).

Hope this helps...

Cheers

Kolus
_________________
Kolusu
www.linkedin.com/in/kolusu

nadh · Intermediate Joined: 08 Oct 2004 Posts: 192 Topics: 89

thank you very much kolusu.

regards
nadh

videlord · Beginner Joined: 09 Dec 2004 Posts: 147 Topics: 19

If DB2ADM installed in your system, then it's a easy thing.
type t before the view listed, show you all the table related.

nadh · Intermediate Joined: 08 Oct 2004 Posts: 192 Topics: 89

Hi

As kolus said i have written the followin sql and got the result.

SELECT BNAME,BTYPE FROM SYSIBM.SYSVIEWDEP WHERE DNAME = 'VW00001A'

There is another way to find the same. In FADB2 OPTION 3.4 WILL GIVE THE BASE TABLE IF WE GIVE THE VIEW NAME DIRECTLY.

THANKS
NADH