CREATE VIEW

The CREATE VIEW statement creates a view on one or more tables or views.

Invocation

This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared.

Authorization

The privileges held by the authorization ID of the statement must include at least one of the following:

The following system authorities:
- *USE to the Create Logical File (CRTLF) CL command
- *EXECUTE and *ADD to the library into which the view is created
- *CHANGE to the data dictionary if the library into which the table is created is an SQL collection with a data dictionary
Administrative authority

The privileges held by the authorization ID of the statement must also include at least one of the following:

For each table and view referenced directly through the subselect, or indirectly through views referenced in the subselect:
- The SELECT privilege on the table or view, and
- The system authority *EXECUTE on the library containing the table or view
Administrative authority

The authorization ID of the statement has the SELECT privilege on a table when:

It is the owner of the table,
It has been granted the SELECT privilege on the table, or
It has been granted the system authorities of *OBJOPR and *READ on the table.

The authorization ID of the statement has the SELECT privilege on a view when:

It is the owner of the view,
It has been granted the SELECT privilege on the view, or
It has been granted the system authorities of *OBJOPR and *READ on the view and the system authority *READ on all tables and views that this view is directly or indirectly dependent on. That is, all tables and views referenced in the view definition, and if a view is referenced, all tables and views referenced in its definition, and so forth.

Syntax

>>-CREATE VIEW--view-name--------------------------------------->
 
>-----+--------------------------------------------------------------------+>
      |    .-,------------------------------------------------------.      |
      |    V                                                        |      |
      '-(-----column-name--+-------------------------------------+--+---)--'
                           |     .-COLUMN-.                      |
                           '-FOR-+--------+--system-column-name--'
 
>----AS--+---------------------------------------+---subselect-->
         |       .-,--------------------------.  |
         |       V                            |  |
         '-WITH-----common-table-expression---+--'
 
>-----+-------------------------------------+------------------><
      |       .-CASCADED--.                 |
      '-WITH--+-----------+---CHECK OPTION--'
              '-LOCAL-----'

Description

view-name

Names the view. The name, including the implicit or explicit qualifier, must not be the same as any table, view, index, alias, or file that already exists at the application server.

If SQL names were specified, the view will be created in the collection or library specified by the implicit or explicit qualifier. The qualifier is the owner of the view if a user profile with that name exists. Otherwise, the owner of the view is the user profile or group user profile of the job executing the statement.

If system names were specified, the view will be created in the collection or library that is specified by the qualifier. If not qualified, the view name will be created in the same collection or library as the first table specified on the first FROM clause (including FROM clauses in any common table expressions or nested table expressoin) . The owner of the view is the user profile or group user profile of the job executing the statement.

If the owner of the view is a member of a group profile (GRPPRF keyword) and group authority is specified (GRPAUT keyword), that group profile will also have authority to the view.

The owner always acquires the SELECT privilege on the view and the authorization to drop the view. The SELECT privilege can be granted to others only if the owner also has the authority to grant the SELECT privilege on every table or view identified in the subselect.

The owner can also acquire the INSERT, UPDATE, and DELETE privileges on the view. If the view is not read-only, then the same privileges will be acquired on the new view as the owner has on the table or view identified in the first FROM clause of the subselect. These privileges can be granted only if the privileges from which they are derived can also be granted.

If a view name is not a valid system name, DB2 UDB for AS/400 SQL will generate a system name. For information on the rules for generating the name, see "Rules for Table Name Generation".

(column-name, ... )

Names the columns in the view. If you specify a list of column names, it must consist of as many names as there are columns in the result table of the subselect. Do not qualify column-name and do not use the same name for more than one column of the view or for a system-column-name of the view. If you do not specify a list of column names, the columns of the view inherit the names of the columns and system names of the columns of the result table of the subselect.

You must specify a list of column names if the result table of the subselect has duplicate column names, duplicate system column names, or an unnamed column. For more information about unnamed columns, see "Names of result columns".

FOR COLUMN system-column-name

Provides an OS/400 name for the column. Do not use the same name for more than one column of the view or for a column-name of the view.

If the system-column-name is not specified, and the column-name is not a valid system-column-name, a system column name is generated. For more information on how system column names are generated, see "Rules for Column Name Generation".

AS subselect

Defines the view. At any time, the view consists of the rows that would result if the subselect were executed.

common-table-expression defines a common table expression for use with the subselect that follows. For more information see common-table-expression.

Subselect must not reference host variables. For an explanation of subselect, see "Queries".

WITH CASCADED CHECK OPTION

Specifies the constraint that every row that is inserted or updated through the view must conform to the definition of the view. A row that does not conform to the definition of the view is a row that cannot be retrieved using that view.

WITH CHECK OPTION must not be specified if the view is read-only or if the definition of the view includes a subquery. If WITH CHECK OPTION is specified for an updateable view that does not allow inserts, then the constraint applies to updates only.

If WITH CHECK OPTION is omitted, the definition of the view is not used in the checking of any insert or update operations that use the view. Some checking might still occur during insert or update operations if the view is directly or indirectly dependent on another view that includes WITH CHECK OPTION. Because the definition of the view is not used, rows that do not conform to the definition of the view might be inserted or updated through the view.

The WITH CHECK OPTION constraint on a view V is inherited by any updateable view that is directly or indirectly dependent on V. Thus, if an updateable view is defined on V, the constraint on V also applies to that view, even if WITH CHECK OPTION is not specified on that view. For example, consider the following updateable views:

CREATE VIEW V1 AS SELECT COL1 FROM T1 WHERE COL1 > 10
 
CREATE VIEW V2 AS SELECT COL1 FROM V1 WITH CHECK OPTION
 
CREATE VIEW V3 AS SELECT COL1 FROM V2 WHERE COL1 < 100

The following INSERT statement using V1 will succeed because V1 does not have a WITH CHECK OPTION and V1 is not dependent on any other view that has a WITH CHECK OPTION.

INSERT INTO V1 VALUES(5)

The following INSERT statement using V2 will result in an error because V2 has a WITH CHECK OPTION and the insert would produce a row that did not conform to the definition of V2.

INSERT INTO V2 VALUES(5)

The following INSERT statement using V3 will result in an error even though it does not have WITH CHECK OPTION because V3 is dependent on V2 which does have a WITH CHECK OPTION.

INSERT INTO V3 VALUES(5)

The following INSERT statement using V3 will succeed because even though it does not conform to the definition of V3 (V3 does not have a WITH CHECK OPTION), it does conform to the definition of V2 (which does have a WITH CHECK OPTION).

INSERT INTO V3 VALUES(200)

WITH LOCAL CHECK OPTION

WITH LOCAL CHECK OPTION is identical to WITH CASCADED CHECK OPTION except that it is still possible to update a row so that it no longer conforms to the definition of the view when the view is defined with WITH LOCAL CHECK OPTION. This can only happen when the view is directly or indirectly dependent on a view that was defined without either WITH CASCADED CHECK OPTION or WITH LOCAL CHECK OPTION clauses.

WITH LOCAL CHECK OPTION specifies that the search conditions of only those dependent views that have the WITH LOCAL CHECK OPTION or WITH CASCADED CHECK OPTION are checked when a row is inserted or updated. In contrast, WITH CASCADED CHECK OPTION specifies that the search conditions of all dependent views are checked when a row is inserted or updated.

The difference between CASCADED and LOCAL is best shown by example. Consider the following updateable views where x and y represent either LOCAL or CASCADED:

V1 defined on T0
V2 defined on V1 WITH x CHECK OPTION
V3 defined on V2
V4 defined on V3 WITH y CHECK OPTION
V5 defined on V4

The following table describes which views search conditions are checked during an INSERT or UPDATE operation:

Table 24. Views whose search conditions are checked during INSERT and UPDATE

View used in INSERT or UPDATE x = LOCAL
y = LOCAL
x = CASCADED
y = CASCADED
x = LOCAL
y = CASCADED
x = CASCADED
y = LOCAL
V1 none none none none
V2 V2 V2 V1 V2 V2 V1
V3 V2 V2 V1 V2 V2 V1
V4 V4 V2 V4 V3 V2 V1 V4 V3 V2 V1 V4 V2 V1
V5 V4 V2 V4 V3 V2 V1 V4 V3 V2 V1 V4 V2 V1

View used in INSERT or UPDATE	x = LOCAL y = LOCAL	x = CASCADED y = CASCADED	x = LOCAL y = CASCADED	x = CASCADED y = LOCAL
V1	none	none	none	none
V2	V2	V2 V1	V2	V2 V1
V3	V2	V2 V1	V2	V2 V1
V4	V4 V2	V4 V3 V2 V1	V4 V3 V2 V1	V4 V2 V1
V5	V4 V2	V4 V3 V2 V1	V4 V3 V2 V1	V4 V2 V1

Notes

Views are created as nonkeyed logical files. If SQL names are used, views are created with the system authority of *EXCLUDE on *PUBLIC. If system names are used, views are created with the authority to *PUBLIC as determined by the create authority (CRTAUT) parameter of the collection or library.

When a view is created, the file wait time and record wait time attributes are set to the default that is specified on the WAITFILE and WAITRCD keywords of the Create Logical File (CRTLF) command.

The view is created with the sort sequence in effect at the time the CREATE VIEW statement is executed. The sort sequence of the view applies to all comparisons involving SBCS data and mixed data in the view subselect. When the view is included in a query, an intermediate result table is generated from the view subselect. The sort sequence in effect when the query is executed applies to any selection specified in the query.

A view cannot be the object table in an UPDATE statement unless the first SELECT clause contains at least one result column that is derived solely from a column. That is, at least one result column must not be derived from an expression that contains an operator, scalar function, constant, or a column that itself is derived from such expressions.

A view cannot refer to more than 32 real tables, including real tables referred to by underlying views.

A view cannot address more than 8000 columns. The number of tables referred to in the view, the column name lengths, and the length of the WHERE clause also reduce this number.

A view created over a distributed table is created on all of the AS/400 systems across which the table is distributed. If a view is created over more than one distributed table, and those tables are not distributed using the same nodegroup, then the view is created only on the system that performs the CREATE VIEW statement. For more information on distributed tables, see the DB2 Multisystem book.

Read-only views

A view is read-only if any of the following appear in its definition:

The first FROM clause identifies more than one table or view.
The first FROM clause identifies a read-only view.
The first SELECT clause specifies the keyword DISTINCT.
The outer subselect contains a GROUP BY clause.
The outer subselect contains a HAVING clause.
The first SELECT clause contains a column function.
The subselect contains a subquery such that the base object of the outer subselect, and of the subquery, is the same table.

A read-only view cannot be the object of an INSERT, UPDATE, or DELETE statement.

Testing a view definition

You can test the semantics of your view definition by executing SELECT * FROM view-name.

Examples

Example 1

Create a view named MA_PROJ over the PROJECT table that contains only those rows with a project number (PROJNO) starting with the letters 'MA'.

CREATE VIEW MA_PROJ
  AS SELECT * FROM PROJECT
        WHERE SUBSTR(PROJNO, 1, 2) = 'MA'

Example 2

Create a view as in example 1, but select only the columns for project number (PROJNO), project name (PROJNAME) and employee in charge of the project (RESPEMP).

CREATE VIEW MA_PROJ2
  AS SELECT PROJNO, PROJNAME, RESPEMP FROM PROJECT
       WHERE SUBSTR(PROJNO, 1, 2) = 'MA'

Example 3

Create a view as in example 2, but, in the view, call the column for the employee in charge of the project IN_CHARGE.

CREATE VIEW MA_PROJ (PROJNO, PROJNAME, IN_CHARGE)
  AS SELECT PROJNO, PROJNAME, RESPEMP FROM PROJECT
       WHERE SUBSTR(PROJNO, 1, 2) = 'MA'

Note: Even though you are changing only one of the column names, the names of all three columns in the view must be listed in the parentheses that follow MA_PROJ.

Example 4

Create a view named PRJ_LEADER that contains the first four columns (PROJNO, PROJNAME, DEPTNO, RESPEMP) from the PROJECT table together with the last name (LASTNAME) of the person who is responsible for the project (RESPEMP). Obtain the name from the EMPLOYEE table by matching EMPNO in EMPLOYEE to RESEMP in PROJECT.

CREATE VIEW PRJ_LEADER
  AS SELECT PROJNO, PROJNAME, DEPTNO, RESPEMP, LASTNAME
       FROM PROJECT, EMPLOYEE
       WHERE RESPEMP = EMPNO

Example 5

Create a view as in example 4, but in addition to the columns PROJNO, PROJNAME, DEPTNO, RESEMP and LASTNAME, show the total pay (SALARY + BONUS +COMM) of the employee who is responsible. Also select only those projects with mean staffing (PRSTAFF) greater than one.

CREATE VIEW PRJ_LEADER (PROJNO, PROJNAME, DEPTNO, RESPEMP, LASTNAME, TOTAL_PAY)
  AS SELECT PROJNO, PROJNAME, DEPTNO, RESPEMP, LASTNAME, SALARY+BONUS+COMM
       FROM PROJECT, EMPLOYEE
       WHERE RESPEMP = EMPNO AND PRSTAFF > 1

[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]