More info about DB2 VIEWS

Shobana Bhaskar · Posted: Mon Apr 09, 2007 2:44 pm

Hi All,

Am aware of what views are and their use; And about views, i can find -
1) View is a virtual table residing in temporary memory.
2) Only the view definition itself is actually stored in the database.

Please help me to know something more than that...
----> Suppose we are creating view DEPT_VIEW for DEPARTMENT table as below, what would be the content of DEPT_VIEW?

CREATE VIEW DEPT_VIEW
AS SELECT * FROM DEPARTMENT
WHERE DEPT_SIZE > 25

Would it be holding all the rows (data) that statifies WHERE clause? If so where these data will be stored (as views are not stored in physical storage)? Also how does the data gets updated when rows are processed (inserted/deleted) in the table?

---> Please let me know whether my understanding is correct - Views will NOT hold data and each time DEPT_VIEW is referred (For eg. SELECT * FROM DEPT_VIEW), the query defined in CREATE VIEW statement (SELECT * FROM DEPARTMENT
WHERE DEPT_SIZE > 25) will be executed and resultant temporary table would be used for further processing. Hence Views contains or refer ONLY the Query we have defined in CREATE VIEW statement and is not directly related to holding table data.

Please correct me if am wrong. Thanks in advance!!!

William Thompson · Posted: Mon Apr 09, 2007 2:49 pm

I think (if I understand you correctly) you are right....
In you example, just picture DEPT_VIEW as a shorthand way of saying
SELECT * FROM DEPARTMENT
WHERE DEPT_SIZE > 25

Shobana Bhaskar · Posted: Mon Apr 09, 2007 3:11 pm

Thanks William.
Then why all the notes i have referred are not pointing out this but simply saying as "Views look like and often behaves like base table". If it is referring just the query, then its behavior is unrelated with table behavior. Only the resulatant temporary table created through view would behave like a table. Also no one are projecting these points. Anyway thanks.

William Thompson · Posted: Mon Apr 09, 2007 3:18 pm