DB2 Views Classification

Views are classified by the operations they allow. There are four classes of views:

• Deleteable views
• Updatable views
• Insertable views
• Read-only views

In the SYSCAT.VIEWS catalog table, when the value of the column READ-ONLY is Y, this indicates that the view is read-only; otherwise, it is either a deleteable, updatable, or insertable view. 

Deleteable Views

A deleteable view allows you to execute the DELETE statement against it.

All of the following must be true.

• Each FROM clause of the outer fullselect identifies only one base table (with no OUTER clause), a deleteable view (with no OUTER clause), a deleteable nested table expression, or a deleteable common table expression (cannot identify a NICKNAME used with federated support).
• The outer fullselect does not include a VALUES clause.
• The outer fullselect does not include a GROUP BY clause or a HAVING clause.
• The outer fullselect does not include column functions in the SELECT list.
• The outer fullselect does not include SET operations (UNION, EXCEPT, or INTERSECT), with the exception of UNION ALL.
• The base tables in the operands of a UNION ALL must not be the same table, and each operand must be deleteable.
• The select list of the outer fullselect does not include DISTINCT.

Updatable Views

An updatable view is a special case of a deleteable view. A view is updatable when at least one of its columns is updatable. All of the following must be true.

• The view is deleteable.
• The column resolves to a column of a base table (not using a dereference operation), and the READ ONLY option is not specified
• All the corresponding columns of the operands of a UNION ALL have exactly matching data types (including length or precision and scale) and matching default values if the fullselect of the view includes a UNION ALL.

You can update view1 using the UPDATE statement, and the changes will be applied to its base table. For example, the following statement changes the value of column employee_id to 100 for records with the name value of Mary in table1.

UPDATE view1 SET id='100' WHERE name = 'Mary';

Insertable Views

An insertable view allows you to execute the INSERT statement against it. A view is insertable when all of its columns are updatable. For example, view1 fits this rule. The following statement will insert a row into table1, which is the base table of view1.

 INSERT INTO view1 VALUES ('200', 'Ben');

Read-Only Views

A read-only view is not deleteable. Its read-only property is also stored in the SYSCAT.VIEWS table. Even if a view is read-only, INSERT, UPDATE, and DELETE operations are still possible by using an INSTEAD OF trigger.

Using the WITH CHECK OPTION

You can define a view to selectively display a subset of rows of its base table by using the WHERE clause in the CREATE VIEW statement. To ensure that all the INSERT and UPDATE operations conform to the criteria specified in the WHERE clause of the view, you can use the WITH CHECK OPTION clause. For example, let's create the view view3 derived from table table1.

CREATE VIEW view3 (id, name,deptno) AS
          SELECT employee_id, name, deptno
             FROM table1
           WHERE deptno = 101
             WITH CHECK OPTION

  If you issue a SELECT * FROM view3 statement, you will obtain the following result:

                   ID NAME             DEPTNO
                   --- -------------------- -----------
                  001    John           101
                  002    Mary           101

Only two rows are retrieved because these are the only rows that satisfy the WHERE clause. What happens if you issue the following statement?

INSERT INTO view3 VALUES ('007','Shawn',201)

This statement fails because 201 does not conform to the criteria of the WHERE clause used in the CREATE VIEW definition, which is enforced because of WITH CHECK OPTION. If view3 had not been defined with this clause, the INSERT statement would have succeeded.

Nested Views

Nested views are ones based on other views. 

CREATE VIEW view4 AS SELECT * FROM view3

In this example, view4 has been created based on view3, which was used in earlier examples. The WITH CHECK OPTION clause specified in view3 is still in effect for view4; therefore, the following INSERT statement fails for the same reason it fails when inserting into view3.

INSERT INTO view4 VALUES ('007','Shawn',201)

Created with Artisteer