DB2 Views

A view is a virtual table derived from one or more tables or other views. It is virtual because it does not contain any data, but a definition of a table based on the result of a SELECT statement. Refer below figure. 

A view does not need to contain all the columns of the base table.

Its columns do not need to have the same names as the base table, either. This is illustrated in above Figure, where the view consists of only two columns, and the first column of the view has a different name than the corresponding column in the base table.

This is particularly useful for hiding confidential information from users. You can create a view using the CREATE VIEW statement. 

Example : 

CREATE VIEW EMP_VIEW (EMP_NO, FIRST_NME, LAST_NAME)
         SELECT EMPNO, FIRSTNME, LASTNAME
           FROM EMPLOYEE

To display the contents of view1, use the following statement.

SELECT * FROM EMP_VIEW

You can also create views based on multiple tables. Refer below figure.

When you create a view, its definition is stored in the system catalog table SYSCAT.VIEWS. This table contains information about each view such as its name, schema, whether or not it is read-only, and the SQL statement used to create the view. 

When a view is referenced in a query, DB2 reads and executes the view definition from the SYSCAT.VIEWS table, pulls the data from the base table, and presents it to the users.

To remove a view, use the DROP VIEW statement. For example, to remove the view view1 use:

    DROP VIEW EMP_VIEW

If any of the base tables or views is dropped, the views that are dependent on them will be marked invalid and the value in the VALID column will be set to X instead of Y. When this happens, you will not be able to use these views. This is true even if you have recreated the base table or view afterward.

Created with Artisteer