The CREATE VIEW statement creates a view on one or more tables, views or nicknames.
Invocation
This statement can be embedded in an application program or issued through the use of dynamic SQL statements. It is an executable statement that can be dynamically prepared. However, if the bind option DYNAMICRULES BIND applies, the statement cannot be dynamically prepared (SQLSTATE 42509).
Authorization
The privileges held by the authorization ID of the statement must include at least one of the following:
and at least one of the following:
If creating a subview, the authorization ID of the statement must:
Group privileges are not considered for any table or view specified in the CREATE VIEW statement.
Privileges are not considered when defining a view on federated database nickname. Authorization requirements of the data source for the table or view referenced by the nickname are applied when the query is processed. The authorization ID of the statement may be mapped to a different remote authorization ID.
If a view definer can only create the view because the definer has SYSADM authority, then the definer is granted explicit DBADM authority for the purpose of creating the view.
Syntax
>>-CREATE--+-----------+---VIEW--view-name---------------------->
'-FEDERATED-'
>-----+---------------------------------------------+--AS------->
| .-,--------------. |
| V | |
+-(-----column-name---+---)-------------------+
'-OF--type-name--+-| root-view-definition |-+-'
'-| subview-definition |---'
>----+---------------------------------------+--fullselect------>
| .-,--------------------------. |
| V | |
'-WITH-----common-table-expression---+--'
>-----+--------------------------------------+-----------------><
| .-CASCADED--. |
'-WITH--+-----------+---CHECK OPTION---'
'-LOCAL-----'
root-view-definition
|---MODE DB2SQL------------------------------------------------->
>----(--| oid-column |--+----------------------+---)------------|
'-,--| with-options |--'
subview-definition
|---MODE DB2SQL--| under-clause |------------------------------->
>-----+--------------------------+---+---------+----------------|
'-(--| with-options |--)---' '-EXTEND--'
oid-column
|---REF IS--oid-column-name--USER GENERATED----+------------+---|
'-UNCHECKED--'
with-options
.-,----------------------------------------------------------------.
| .-,--------------------------------. |
V V | |
|--------column-name--WITH OPTIONS----+-SCOPE--+-typed-table-name-+-+--+--+--->
| '-typed-view-name--' |
'-READ ONLY-------------------'
>---------------------------------------------------------------|
under-clause
|---UNDER--superview-name--INHERIT SELECT PRIVILEGES------------|
| Note: | See Queries for the syntax of common-table-expression and fullselect. |
Description
Conversely, if an OLEDB table function or a nickname is not directly, or indirectly, referenced in the fullselect and the FEDERATED keyword is specified, an error will be issued (SQLSTATE 429BA) when the CREATE VIEW statement is submitted. The view will not be created.
The name can be the same as the name of an inoperative view (see Inoperative views). In this case the new view specified in the CREATE VIEW statement will replace the inoperative view. The user will get a warning (SQLSTATE 01595) when an inoperative view is replaced. No warning is returned if the application was bound with the bind option SQLWARN set to NO.
A list of column names must be specified if the result table of the fullselect has duplicate column names or an unnamed column (SQLSTATE 42908). An unnamed column is a column derived from a constant, function, expression, or set operation that is not named using the AS clause of the select list.
The columns of the view include the object identifier column of the superview with its type modified to be REF(type-name), followed by columns based on the attributes of type-name (remember that the type includes the attributes of its supertype).
Specifying the scope for a reference type column may be deferred to a subsequent ALTER VIEW statement (if the scope is not inherited) to allow the target table or view to be defined, usually in the case of mutually referencing views and tables. If no scope is specified for a reference type column of the view and the underlying table or view column was scoped, then the underlying column's scope is inherited by the reference type column. The column remains unscoped if the underlying table or view column did not have a scope. See Notes for more information about scope and reference type columns.
For Typed Views and Subviews: The fullselect must conform to the following rules otherwise an error is returned (SQLSTATE 428EA unless otherwise specified).
For a hierarchy of views and subviews: Let BR1 and BR2 be any branches that appear in the definitions of views in the hierarchy. Let T1 be the underlying table or view of BR1, and let T2 be the underlying table or view of BR2. Then:
For typed subviews defined using EXTEND AS: For every branch in the body of the subview:
For typed subviews defined using AS without EXTEND:
WITH CHECK OPTION must not be specified if the view is read-only (SQLSTATE 42813). If WITH CHECK OPTION is specified for an updatable view that does not allow inserts, then the constraint applies to updates only.
WITH CHECK OPTION must not be specified if the view references the NODENUMBER or PARTITION function, a non-deterministic function, or a function with external action (SQLSTATE 42997).
WITH CHECK OPTION must not be specified if the view is a typed view (SQLSTATE 42997).
WITH CHECK OPTION must not be specified if a nickname is the update target of the view.
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 might be inserted or updated through the view that do not conform to the definition of the view.
The difference between CASCADED and LOCAL is shown in the following example. Consider the following updatable views (substituting for Y from column headings of the table that follows):
V1 defined on table T V2 defined on V1 WITH Y CHECK OPTION V3 defined on V2 V4 defined on V3 WITH Y CHECK OPTION V5 defined on V4
The following table shows the search conditions against which inserted or
updated rows are checked:
|
| Y is LOCAL | Y is CASCADED |
|---|---|---|
| V1 checked against: | no view | no view |
| V2 checked against: | V2 | V2, V1 |
| V3 checked against: | V2 | V2, V1 |
| V4 checked against: | V2, V4 | V4, V3, V2, V1 |
| V5 checked against: | V2, V4 | V4, V3, V2, V1 |
Consider the following updatable view which shows the impact of the WITH CHECK OPTION using the default CASCADED option:
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 (SQLSTATE 44000).
INSERT INTO V3 VALUES(5)
The following INSERT statement using V3 will succeed 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)
A view is updatable if ANY column of the view is updatable.
A view is insertable if ALL columns of the view are updatable and the fullselect of the view does not include UNION ALL.
The READONLY column in the SYSCAT.VIEWS catalog view indicates if a view is read-only.
In practical terms, an inoperative view is one in which the view definition has been unintentionally dropped. For example, when an alias is dropped, any view defined using that alias is made inoperative. All dependent views also become inoperative and packages dependent on the view are no longer valid.
Until the inoperative view is explicitly recreated or dropped, a statement using that inoperative view cannot be compiled (SQLSTATE 51024) with the exception of the CREATE ALIAS, CREATE VIEW, DROP VIEW, and COMMENT ON TABLE statements. Until the inoperative view has been explicitly dropped, its qualified name cannot be used to create another table or alias (SQLSTATE 42710).
An inoperative view may be recreated by issuing a CREATE VIEW statement using the definition text of the inoperative view. This view definition text is stored in the TEXT column of the SYSCAT.VIEWS catalog. When recreating an inoperative view, it is necessary to explicitly grant any privileges required on that view by others, due to the fact that all authorization records on a view are deleted if the view is marked inoperative. Note that there is no need to explicitly drop the inoperative view in order to recreate it. Issuing a CREATE VIEW statement with the same view-name as an inoperative view will cause that inoperative view to be replaced, and the CREATE VIEW statement will return a warning (SQLSTATE 01595).
Inoperative views are indicated by an X in the VALID column of the SYSCAT.VIEWS catalog view and an X in the STATUS column of the SYSCAT.TABLES catalog view.
The definer of a view always receives the SELECT privilege on the view as well as the right to drop the view. The definer of a view will get CONTROL privilege on the view only if the definer has CONTROL privilege on every base table, view or nickname identified in the fullselect, or if the definer has SYSADM or DBADM authority.
The definer of the view is granted INSERT, UPDATE, column level UPDATE or DELETE privileges on the view if the view is not read-only and the definer has the corresponding privileges on the underlying objects.
The definer of a view only acquires privileges if the privileges from which they are derived exist at the time the view is created. The definer must have these privileges either directly or because PUBLIC has the privilege. Privileges are not considered when defining a view on federated server nickname. However, when using a view on a nickname, the user's authorization ID must have valid select privileges on the table or view that the nickname references at the data source. Otherwise, an error is returned. Privileges held by groups of which the view definer is a member, are not considered.
When a subview is created, the SELECT privileges held on the immediate superview are automatically granted on the subview.
When selecting a reference type column in the fullselect of a view definition, consider the target type and scope that is required.
CAST(CAST(Y AS VARCHAR(16) FOR BIT DATA) AS REF(VTYP1) SCOPE VIEW1)
In all other cases, the columns of a view will not get the identity property. For example:
When inserting into a view for which the select list of the view definition directly or indirectly includes the name of an identity column of a base table, the same rules apply as if the INSERT statement directly referenced the identity column of the base table.
A nickname has no associated DML privileges and therefore when the view is created, no privilege checking is done to determine whether the view definer has access to the nickname or to the underlying data source table or view. Privilege checking of references to tables or views at the federated database are handled as usual, requiring the view definer to have at least SELECT privilege on such objects.
When a federated view is subsequently referenced in a query, the nicknames result in queries against the data source and authorization ID that issued the query (or the remote authorization ID to which it maps) must have the necessary privileges to access the data source table or view. The authorization ID that issues the query referencing the federated view is not required to have any additional privileges on tables or views (non-federated) that exist at the federated server.
Examples
Example 1: Create a view named MA_PROJ upon 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_PROJ AS SELECTPROJNO, 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 SELECTPROJNO, PROJNAME, RESPEMP FROM PROJECT WHERE SUBSTR(PROJNO, 1, 2) = 'MA'
Note: Even though only one of the column names is being changed, 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 RESPEMP 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, RESPEMP, 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
Specifying the column name list could be avoided by naming the expression SALARY+BONUS+COMM as TOTAL_PAY in the fullselect.
CREATE VIEW PRJ_LEADER
AS SELECT PROJNO, PROJNAME, DEPTNO, RESPEMP,
LASTNAME, SALARY+BONUS+COMM AS TOTAL_PAY
FROM PROJECT, EMPLOYEE
WHERE RESPEMP = EMPNO AND PRSTAFF > 1
Example 6: Given the set of tables and views shown in the following figure:
Figure 12. Tables and Views for Example 6
User ZORPIE (who does not have either DBADM or SYSADM authority) has been granted the privileges shown in brackets below each object:
CREATE VIEW VA AS SELECT * FROM S1.V1
because she has CONTROL on S1.V1. 86 It it does not matter which, if any, privileges she has on the underlying base table.
CREATE VIEW VB AS SELECT * FROM S1.V2
because she has neither CONTROL nor SELECT on S1.V2. It does not matter that she has CONTROL on the underlying base table (S1.T2).
CREATE VIEW VC (COLA, COLB, COLC, COLD)
AS SELECT * FROM S1.V1, S1.T2
WHERE COLA = COLC
because the fullselect of ZORPIE.VC references view S1.V1 and table S1.T2 and she has CONTROL on both of these. Note that the view VC is read-only, so ZORPIE does not get INSERT, UPDATE or DELETE privileges.
CREATE VIEW VD (COLA,COLB, COLE, COLF)
AS SELECT * FROM S1.V1, S1.V3
WHERE COLA = COLE
because the fullselect of ZORPIE.VD references the two views S1.V1 and S1.V3, one on which she has only SELECT privilege, and one on which she has CONTROL privilege. She is given the lesser of the two privileges, SELECT, on ZORPIE.VD.
CREATE VIEW VE
AS SELECT * FROM S1.V1
WHERE COLA > ANY
(SELECT COLE FROM S1.V3)
ZORPIE's privileges on VE are determined primarily by her privileges on S1.V1. Since S1.V3 is only referenced in a subquery, she only needs SELECT privilege on S1.V3 to create the view VE. The definer of a view only gets CONTROL on the view if they have CONTROL on all objects referenced in the view definition. ZORPIE does not have CONTROL on S1.V3, consequently she does not get CONTROL on VE.