Understanding DB2 Nested Views
January 28, 2004by Roman B. Melnyk and Paul C. Zikopoulos
A view is a virtual table, meaning that no permanent storage is associated with the data in the view; rather, the permanent storage is associated with the table or tables on which the view is based. A view allows you to look at (and in some cases to update) data in specific rows and columns contained in one or more tables. Views are classified by the operations they allow; there are, for example, insertable, updatable and read-only views. Nested views have view definitions that are based on other views. Views can be created and dropped (deleted), but not altered. If a view definition needs to be changed, the view must be dropped and then recreated using a new definition. A view becomes inoperative if any of its referenced database objects are dropped.
Views are most useful in limiting users' access to data. They also provide a great deal of flexibility in the way that users look at table data. This paper introduces you to the world of views. Using working examples, we walk you through the view classifications and explain how data that is to be updated or inserted into a view can be validated. Finally, we show you how such validation is handled in the special case of nested views.
An Introduction to Views
Think of a view as simply the named specification of a result table, where the specification is a SELECT statement that is run whenever the view is referenced in an SQL statement. In fact, a SELECT statement is the major component of the CREATE VIEW statement that is used to define a view. For example:
db2 create view sales_by_emp (id, employee, ext, birthdate, salary, sales) as select e.empno, concat(concat(e.firstnme, ' '), e.lastname), e.phoneno, e.birthdate, e.salary, sum(s.sales) from employee e, sales s where e.lastname = s.sales_person group by e.salary, concat(concat(e.firstnme, ' '), e.lastname), s.sales_person, e.empno, e.phoneno, e.birthdate
This statement creates a view named SALES_BY_EMP. The view contains data from two different tables in the SAMPLE database, EMPLOYEE and SALES. These table names are given the short names E and S, respectively, so that subsequent column references can be made unambiguous. (This is especially useful in cases where two or more referenced tables have columns with the same names.) This particular view is defined with eight columns, whose names are enclosed by the parentheses that immediately follow the view name. A view can have column names that are different from the names of corresponding columns in the tables on which the view is based. Here we have a view column (EMPLOYEE) that is derived from two table columns (FIRSTNME and LASTNAME). In this case, view column values are the result of an IBM DB2 Universal Database (DB2 UDB) built-in function (CONCAT) that is operating on the corresponding table column values.
The SALES_BY_EMP view is a read-only view. If you try to update a value in one of its columns or try to insert a new row, the operation will fail. Looking at the view definition, it is easy to see why this can only be a read-only view: the values in one of its columns are the result of a scalar function (CONCAT) that is operating on two separate columns in one of the tables on which the view is based; the values in another view column are the result of an aggregate function (SUM) operating on a column in the other table on which the view is based; and there is a GROUP BY clause in the defining SELECT statement. Because this view returns summary data, it must be a read-only view.
The new view definition is stored in the database catalog (and hence will be part of any future database backup images). It can be retrieved by querying SYSCAT.VIEWS, which is itself a view based on a table in the database catalog called SYSIBM.SYSVIEWS:
db2 select text from syscat.views where viewname = 'SALES_BY_EMP'
You can also retrieve the value of the READONLY column in SYSCAT.VIEWS to quickly determine whether a view is read-only. In the case of the SALES_BY_EMP view:
db2 select readonly from syscat.views where viewname = 'SALES_BY_EMP'
returns the value Y, meaning yes, the view is read-only.
Having created the SALES_BY_EMP view, we can now reference it in SQL statements. For example, typing
db2 select * from sales_by_emp
returns the following result set:
Figure 1. The SALES_BY_EMP view is based on columns from two tables in the SAMPLE database: EMPLOYEE (shaded yellow) and SALES (shaded green).
We stated earlier that a view is simply the named specification of a result table, where the specification is a SELECT statement that is run whenever the view is referenced in an SQL statement. The SQL statement that returns the same result set shown in Figure 1 is:
db2 select e.empno as id, concat(concat(e.firstnme, ' '), e.lastname) as employee, e.phoneno as ext, e.birthdate, e.salary, sum(s.sales) as sales from employee e, sales s where e.lastname = s.sales_person group by e.salary, concat(concat(e.firstnme, ' '), e.lastname), s.sales_person, e.empno, e.phoneno, e.birthdate