Defining views

When you design your database, you might need to give users access to only certain pieces of data. You can give users access by designing and using views. “Using views to customize what data a user sees” on page 104 explains the issues to consider when you design views. This section provides examples of defining views on one or more tables and the effects of modifying view information.

Coding the view definitions

The name for a view is an identifier of up to 128 characters. The following example shows a view name:

Use the CREATE VIEW statement to define and name a view. Unless you specifically list different column names after the view name, the column names of the view are the same as those of the underlying table. (An example of this is in “Defining a view that combines information from several tables.”) When creating different column names for your view, remember the naming conventions that you established when designing the relational database.

As the examples in this section illustrate, a SELECT statement describes the information in the view. The SELECT statement can name other views and tables, and it can use the WHERE, GROUP BY, and HAVING clauses. It cannot use the ORDER BY clause or name a host variable.

Defining a view on a single table

Example: Assume that you want to create a view on the DEPT table. Of the four columns in the table, the view needs only three: DEPTNO, DEPTNAME, and MGRNO. The order of the columns that you specify in the SELECT clause is the order in which they appear in the view:

CREATE VIEW MYVIEW AS
 SELECT DEPTNO,DEPTNAME,MGRNO
 FROM DEPT;

In this example, no column list follows the view name, MYVIEW. Therefore, the columns of the view have the same names as those of the DEPT table on which it is based. You can execute the following SELECT statement to see the view contents:

SELECT * FROM MYVIEW;

The result table looks like this:

DEPTNO     DEPTNAME                  MGRNO
======     =====================     ======
A00        CHAIRMANS OFFICE          000010
B01        PLANNING                  000020
C01        INFORMATION CENTER        000030
D11        MANUFACTURING SYSTEMS     000060
E21        SOFTWARE SUPPORT          ------

Defining a view that combines information from several tables

You can create a view that contains a union of more than one table. “Merging lists of values: UNION” on page 150 describes how to create a union in an SQL operation.

As “Joining data from more than one table” on page 152 explains, DB2 provides two types of joins—an outer join and an inner join. An outer join includes rows in which the values in the join columns don't match, and rows in which the values match. An inner join includes only rows in which matching values in the join columns are returned.

Example: The following example is an inner join of columns from the DEPT and EMP tables. The WHERE clause limits the view to just those columns in which the MGRNO in the DEPT table matches the EMPNO in the EMP table:

CREATE VIEW MYVIEW  AS
 SELECT DEPTNO, MGRNO, LASTNAME, ADMRDEPT
 FROM DEPT, EMP
 WHERE EMP.EMPNO = DEPT.MGRNO;

The result of executing this CREATE VIEW statement is an inner join view of two tables, which is shown below:

DEPTNO     MGRNO      LASTNAME     ADMRDEPT
======     ======     ========     ========
A00        000010     HAAS         A00
B01        000020     THOMPSON     A00
C01        000030     KWAN         A00
D11        000060     STERN        D11

Example: Suppose that you want to create the view in the preceding example, but you want to include only those departments that report to department A00. Suppose also that you prefer to use a different set of column names. Use the following CREATE VIEW statement:

CREATE VIEW MYVIEWA00
 (DEPARTMENT, MANAGER, EMPLOYEE_NAME, REPORT_TO_NAME)
 AS
 SELECT DEPTNO, MGRNO, LASTNAME, ADMRDEPT
 FROM EMP, DEPT
 WHERE EMP.EMPNO = DEPT.MGRNO
 AND ADMRDEPT = 'A00';

You can execute the following SELECT statement to see the view contents:

SELECT * FROM MYVIEWA00;

When you execute this SELECT statement, the result is a view of a subset of the same data, but with different column names, as follows:

DEPARTMENT  MANAGER  EMPLOYEE_NAME  REPORT_TO_NAME
==========  =======  =============  ==============
A00         000010   HAAS           A00
B01         000020   THOMPSON       A00
C01         000030   KWAN           A00

Inserting and updating data through views

If you define a view on a single table, you can refer to the name of a view in an INSERT, UPDATE, or DELETE statement. This section explains how DB2 makes an insert or update to the base table.

To ensure that the insert or update conforms to the view definition, specify the WITH CHECK OPTION clause. The following example illustrates some undesirable results of omitting that check.

Example: Suppose that you define a view, V1, as follows:

CREATE VIEW V1 AS
 SELECT * FROM EMP
 WHERE DEPT LIKE 'D%';

A user with the SELECT privilege on view V1 can see the information from the EMP table for employees in departments whose IDs begin with D. The EMP table has only one department (D11) with an ID that satisfies the condition.

Assume that a user has the INSERT privilege on view V1. A user with both SELECT and INSERT privileges can insert a row for department E01, perhaps erroneously, but cannot select the row that was just inserted.

The following example shows an alternative way to define view V1.

Example: You can avoid the situation in which a value that does not match the view definition is inserted into the base table. To do this, instead define view V1 to include the WITH CHECK OPTION clause:

CREATE VIEW V1 AS SELECT * FROM EMP
 WHERE DEPT LIKE 'D%' WITH CHECK OPTION;

With the new definition, any insert or update to view V1 must satisfy the predicate that is contained in the WHERE clause: DEPT LIKE 'D%'. The check can be valuable, but it also carries a processing cost; each potential insert or update must be checked against the view definition. Therefore, you must weigh the advantage of protecting data integrity against the disadvantage of performance degradation.