The CREATE VIEW statement defines a view on one or more tables, views or nicknames.
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 only if DYNAMICRULES run behavior is in effect for the package (SQLSTATE 42509).
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 a 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 can be mapped to a different remote authorization ID.
To replace an existing view, the authorization ID of the statement must be the owner of the existing view (SQLSTATE 42501).
>>-CREATE--+------------+--VIEW--view-name----------------------> '-OR REPLACE-' >--+---------------------------------------------+--AS----------> | .-,-----------. | | V | | +-(----column-name-+--)-----------------------+ '-OF--type-name--+-| root-view-definition |-+-' '-| subview-definition |---' >--+-----------------------------------+--fullselect--●---------> | .-,-----------------------. | | V | | '-WITH----common-table-expression-+-' >--+----------------------------------+--●----------------------> | .-CASCADED-. | '-WITH--+----------+--CHECK OPTION-' '-LOCAL----' .-WITH NO ROW MOVEMENT-. >--+----------------------+--●--------------------------------->< '-WITH ROW MOVEMENT----' 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-------------|
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.
The fullselect cannot include an SQL data change statement in the FROM clause (SQLSTATE 428FL).
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
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 |
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
INSERT INTO V1 VALUES(5)
INSERT INTO V2 VALUES(5)
INSERT INTO V3 VALUES(5)
INSERT INTO V3 VALUES(200)
Row movement involves deletion of the rows that violate the check constraint, and insertion of those rows back into the view. The WITH ROW MOVEMENT clause can only be specified for UNION ALL views whose columns are all updatable (SQLSTATE 429BJ). If a row is inserted (perhaps after trigger activation) into the same underlying table from which it was deleted, an error is returned (SQLSTATE 23524). A view defined using the WITH ROW MOVEMENT clause must not contain nested UNION ALL operations, except in the outermost fullselect (SQLSTATE 429BJ).
A view is updatable if any column of the view is updatable.
A given row can be inserted into a view (including a UNION ALL) if, and only if, it fulfills the check constraints of exactly one of the underlying base tables.
To insert into a view that includes non-updatable columns, those columns must be omitted from the column list.
The READONLY column in the SYSCAT.VIEWS catalog view indicates if a view is read-only without considering INSTEAD OF triggers.
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 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.
For a view defined WITH ROW MOVEMENT, the definer acquires the UPDATE privilege on the view only if the definer has the UPDATE privilege on all columns of the view, as well as INSERT and DELETE privileges on all underlying tables or views.
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 these privilege. Privileges are not considered when defining a view on a 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.
CAST(CAST(Y AS VARCHAR(16) FOR BIT DATA) AS REF(VTYP1) SCOPE VIEW1)
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.
When the view is created, no privilege checking is done to determine whether the view definer has access to the underlying data source table or view of a nickname. 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 the 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.
CREATE VIEW MA_PROJ AS SELECT *
FROM PROJECT
WHERE SUBSTR(PROJNO, 1, 2) = 'MA'
CREATE VIEW MA_PROJ
AS SELECTPROJNO, PROJNAME, RESPEMP
FROM PROJECT
WHERE SUBSTR(PROJNO, 1, 2) = 'MA'
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.
CREATE VIEW PRJ_LEADER
AS SELECT PROJNO, PROJNAME, DEPTNO, RESPEMP, LASTNAME
FROM PROJECT, EMPLOYEE
WHERE RESPEMP = EMPNO
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
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
CREATE VIEW VA AS SELECT * FROM S1.V1
because
she has CONTROL on S1.V1. (CONTROL on S1.V1 must have been granted
to ZORPIE by someone with ACCESSCTRL or SECADM authority.) 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.