Creating Db2 views

You can create a view on tables or on other views at the current server.

Before you begin

Before you create different column names for your view, remember the naming conventions that you established when designing the relational database.

Procedure

Begin general-use programming interface information. To define a view:

Issue the CREATE VIEW SQL statement.

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. End general-use programming interface information.

Example

Example of defining a view on a single table: 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:

Begin general-use programming interface information.

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

End general-use programming interface information.

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

Example of defining a view that combines information from several tables: You can create a view that contains a union of more than one table. 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.

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

If you want to include only those departments that report to department A00 and want 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