Defining a view

A view is a named specification of a result table. Use views to control which users have access to certain data or to simplify writing SQL statements.

About this task

Use the CREATE VIEW statement to define a view and give the view a name, just as you do for a table. The view that is created with the following statement shows each department manager's name with the department data in the DSN8C10.DEPT table.

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

When a program accesses the data that is defined by a view, Db2 uses the view definition to return a set of rows that the program can access with SQL statements.

To see the departments that are administered by department D01 and the managers of those departments, run the following statement, which returns information from the VDEPTM view:

SELECT DEPTNO, LASTNAME
  FROM VDEPTM
    WHERE ADMRDEPT = 'DO1';

When you create a view, you can reference the SESSION_USER and CURRENT SQLID special registers in the CREATE VIEW statement. When referencing the view, Db2 uses the value of the SESSION_USER or CURRENT SQLID special register that belongs to the user of the SQL statement (SELECT, UPDATE, INSERT, or DELETE) rather than the creator of the view. In other words, a reference to a special register in a view definition refers to its run time value.

You can specify a period specification for a view, subject to certain restrictions. Also, for a view that references an application-period temporal table or a bitemporal table, you can specify a period clause for an update or delete operation on the view.

A column in a view might be based on a column in a base table that is an identity column. The column in the view is also an identity column,except under any of the following circumstances:

The column appears more than once in the view.
The view is based on a join of two or more tables.
The view is based on the union of two or more tables.
Any column in the view is derived from an expression that refers to an identity column.

You can use views to limit access to certain kinds of data, such as salary information. Alternatively, you can use the IMPLICITLY HIDDEN clause of a CREATE TABLE statement define a column of a table to be hidden from some operations.

You can also use views for the following actions:

Make a subset of a table's data available to an application. For example, a view based on the employee table might contain rows only for a particular department.
Combine columns from two or more tables and make the combined data available to an application. By using a SELECT statement that matches values in one table with those in another table, you can create a view that presents data from both tables. However, you can only select data from this type of view. You cannot update, delete, or insert data using a view that joins two or more tables.
Combine rows from two or more tables and make the combined data available to an application. By using two or more subselects that are connected by a set operator such as UNION, you can create a view that presents data from several tables. However, you can only select data from this type of view. You cannot update, delete, or insert data using a view that contains UNION operations.
Present computed data, and make the resulting data available to an application. You can compute such data using any function or operation that you can use in a SELECT statement.