Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

DB2

Posted Jan 28, 2004

Understanding DB2 Nested Views - Page 2

By DatabaseJournal.com Staff

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.



DB2 Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.