Understanding DB2 Nested Views

Wednesday Jan 28th 2004 by DatabaseJournal.com Staff
Share:

Understanding Nested Views is an introduction to the world of views. Using working examples, it walks you through the view classifications and explains how data that is to be updated or inserted into a view can be validated. Finally, it shows how such validation is handled in the special case of nested views.

by 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:

ID

EMPLOYEE

EXT

BIRTHDATE

SALARY

SALES

000340

JASON GOUNOT

5698

05/17/1926

23840.00

50

000330

WING LEE

2103

07/18/1941

25370.00

91

000110

VINCENZO LUCCHESSI

3490

11/05/1929

46500.00

14

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
by Roman B. Melnyk and Paul C. Zikopoulos

Updatable Views



An updatable view contains one or more columns that can be updated with new values. For example:




db2 create view my_emp_view1
 (id, employee, ext, birthdate, salary)
 as select
  empno,
  concat(concat(firstnme, ' '), lastname),
  phoneno,
  birthdate,
  salary
 from employee



Every column in MY_EMP_VIEW1 is updatable, except one: the EMPLOYEE column. Values in this column are the result of the concatenation of values from two columns in the table on which the view is based. The EMPLOYEE column is unique to this view and, as such, is not updatable. MY_EMP_VIEW1 is an updatable view, but not an insertable view:




db2 update my_emp_view1 set salary = 35000 
  where id = '000140'

DB20000I  The SQL command completed successfully.


db2 insert into my_emp_view1 
  (id, employee, ext, birthdate, salary)
 values
  ('000400', 'TALLERICO', '1234', '11/25/1983', 50000.00)
 
DB21034E  The command was processed as an 
 SQL statement because it was not a
valid Command Line Processor command.
  During SQL processing it returned:
SQL0151N  The column "EMPLOYEE" cannot be updated.
  SQLSTATE=42808

Insertable Views

All of the columns in an insertable view must be updatable. For example:

db2 create view my_emp_view2
(id, firstname, initial, lastname, department, ext, education, birthdate, salary)
 as select
  empno,
  firstnme,
  midinit,
  lastname,
  workdept,
  phoneno,
  edlevel,
  birthdate,
  salary
 from employee
 where workdept = 'C01'

Every column in MY_EMP_VIEW2 has a matching column in the table on which the view is based (EMPLOYEE). The view columns are therefore all updatable. An insertable view must also contain all the columns in the original table that are not nullable. This makes sense when you consider the fact that, if one or more such columns were missing from the view, trying to insert a new row into the view would mean attempting to impose null values on columns that are not nullable. Remember that an INSERT statement that does not explicitly assign a value to a column implicitly assigns a null value to it.

db2 insert into my_emp_view2 (id, firstname, initial, lastname,
  department, ext, education, birthdate, salary)
 values ('000410', 'JOE', 'E', 'SHMOE', 'C01', '9999', 8, '01/01/1950', 10000.00)

DB20000I  The SQL command completed successfully.

Nested Views

A view that is based on another view is known as a nested view. For example, we might want to define a new view called MY_EMP_VIEW3 that restricts some of the personal data included in MY_EMP_VIEW2:

db2 create view my_emp_view3
 (id, firstname, initial, lastname, department, ext, education)
 as select
  id,
  firstname,
  initial,
  lastname,
  department,
  ext,
  education
 from my_emp_view2
with cascaded check option

MY_EMP_VIEW3 does not include birth date or salary information. The view definition does, however, include the WITH CASCADED CHECK OPTION clause (see Figure 2).

Figure 2. The principal clauses of the CREATE VIEW statement.

You can create a view by simply specifying a view name and a SELECT statement that defines the columns for the view. You can optionally specify new names for the view columns. By specifying the WITH CHECK OPTION, you can instruct DB2 UDB to enforce any restrictions on the values that can be used during insert or update operations; these restrictions can be part of the current view definition (when a WHERE clause is specified in the fullselect, for example), or they can be inherited from any insertable or updatable views on which the current view depends.

by Roman B. Melnyk and Paul C. Zikopoulos

Views with Check Option



Data that is to be updated or inserted into a view can be validated if the view definition includes conditions (such as a WHERE clause) and if the view is defined using the WITH CHECK OPTION clause. An error is returned if these conditions are not met during an attempted insert or update operation.



MY_EMP_VIEW3 is an example of a view definition using the WITH CASCADED CHECK OPTION clause. If a view is defined with this clause (or the WITH CHECK OPTION clause), the view definition is used to check the validity of data involved in any insert or update operation. Moreover, the view inherits checking behavior from any updatable views on which it depends. It inherits this behavior even if those views were not defined using the WITH CHECK OPTION clause.



In the case of MY_EMP_VIEW3, any row that is to be inserted into this view must include the value 'C01' for the DEPARTMENT column, even though the view definition for MY_EMP_VIEW3 does not have a WHERE clause to that effect. The WITH CASCADED CHECK OPTION clause, however, ensures that this condition, which is part of MY_EMP_VIEW2, applies to the dependent view MY_EMP_VIEW3 as well.



db2 insert into my_emp_view3 (id, firstname, initial, lastname, department, education)
 values ('000420', 'TOM', ' ', 'THUMB', 'D11', 8)

DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0161N  The resulting row of the INSERT or UPDATE does not conform to the
view definition.  SQLSTATE=44000

db2 insert into my_emp_view3 (id, firstname, initial, lastname, department, education)
 values ('000420', 'TOM', ' ', 'THUMB', 'C01', 8)

DB20000I  The SQL command completed successfully.

It is important to remember that no data validation during insert or update operations occurs if the WITH CHECK OPTION is not specified in the definition of the dependent view. For example, given the definition of MY_EMP_VIEW2 shown earlier, and a definition of MY_EMP_VIEW3 that does not include the WITH CHECK OPTION, an insert operation specifying a department other than C01 completes successfully:

db2 create view my_emp_view3
  (id, firstname, initial, lastname, department, ext, education)
 as select
  id,
  firstname,
  initial,
  lastname,
  department,
  ext,
  education
 from my_emp_view2

DB20000I  The SQL command completed successfully.

db2 insert into my_emp_view3 (id, firstname, initial, lastname, education)
 values ('000420', 'TOM', ' ', 'THUMB', 8)

DB20000I  The SQL command completed successfully.

In this case, an insert operation with no department specification is acceptable: a value of 'C01' is not enforced and, because the WORKDEPT column in the EMPLOYEE table is nullable, a null value for the corresponding DEPARTMENT column in MY_EMP_VIEW3 is not rejected.

If a view depends on another view that was defined using the WITH CHECK OPTION clause, data validation occurs during insert or update operations involving the dependent view:

db2 create view my_emp_view2
  (id, firstname, initial, lastname, department, ext, education, birthdate, salary)
 as select
  empno,
  firstnme,
  midinit,
  lastname,
  workdept,
  phoneno,
  edlevel,
  birthdate,
  salary
 from employee
 where workdept = 'C01'
with cascaded check option

DB20000I  The SQL command completed successfully.

db2 create view my_emp_view3
  (id, firstname, initial, lastname, department, ext, education)
 as select
  id,
  firstname,
  initial,
  lastname,
  department,
  ext,
  education
 from my_emp_view2

DB20000I  The SQL command completed successfully.

db2 insert into my_emp_view3 (id, firstname, initial, lastname, department, education)
 values ('000420', 'TOM', ' ', 'THUMB', 'D11', 8)

DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0161N  The resulting row of the INSERT or UPDATE does not conform to the
view definition.  SQLSTATE=44000

You can also specify the WITH LOCAL CHECK OPTION when creating a view. If a view is defined using the WITH LOCAL CHECK OPTION, the view definition is used to check the validity of data involved in any insert or update operation. However, in this case, the view does not inherit search conditions from any insertable or updatable views on which it depends.

by Roman B. Melnyk and Paul C. Zikopoulos


Using the DB2 Control Center to Manage Views



The DB2 Control Center lets you conveniently access database objects, including views. Figure 3 shows the four views that we have created. They appear in the contents pane when Views is selected in the object tree.



Click for larger image



Figure 3. The views that are associated with a particular database appear in the contents pane of the DB2 Control Center with Views selected in the object tree.



If we select MY_EMP_VIEW2, we can open the Show Related window to see the view dependencies (Figure 4), or the Alter View window to see the view definition and add or alter a comment that will be associated with the view (Figure 5).



Figure 4. The Show Related window displays MY_EMP_VIEW2's dependency relationships. Clicking the Tables tab shows the table on which the view is based. Clicking the Views tab shows MY_EMP_VIEW3, the view that depends on MY_EMP_VIEW2.

Figure 5. The Alter View window (which only allows you to alter the comment associated with a view) provides a convenient way to see the view definition (the SQL statement that was used to create the view). The same information can be obtained by querying SYSCAT.VIEWS in the database catalog.

Conclusion

Views are an effective way of limiting user access to data. Some views can be used to update data in the tables on which the views are based. Some views, called nested views, are based on other views. Updatable or insertable views can be defined to validate incoming data. In some cases, data validation can be made to cascade through nested views to give you maximum flexibility and integrity when managing your data.

About the Authors

Roman B. Melnyk, PhD, is with IBM Canada Ltd., specializing in database administration, DB2 utilities, and SQL. Roman has written numerous DB2 books, articles, and other related materials. Roman co-authored DB2 Version 8: The Official Guide, DB2: The Complete Reference, DB2 Fundamentals Certification for Dummies, and DB2 for Dummies. You can reach him at roman_b_melnyk at hotmail.com.

Paul C. Zikopoulos, BA, MBA, is with IBM Canada Ltd. Paul has written numerous magazine articles and books about DB2. Paul has co-authored the books: DB2 Version 8: The Official Guide, DB2: The Complete Reference, DB2 Fundamentals Certification for Dummies, DB2 for Dummies, and A DBA's Guide to Databases on Linux. Paul is a DB2 Certified Advanced Technical Expert (DRDA and Cluster/EEE) and a DB2 Certified Solutions Expert (Business Intelligence and Database Administration). You can reach him at: paulz_ibm at msn.com.

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved