CREATE VIEW statement

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 only if DYNAMICRULES run behavior is in effect for the package (SQLSTATE 42509).

Authorization

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

Syntax

root-view-definition

subview-definition

oid-column

with-options

under-clause

Description

OR REPLACE

Specifies to replace the definition for the view if one exists at the current server. The existing definition is effectively dropped before the new definition is replaced in the catalog, with the exception that privileges that were granted on the view are not affected. This option is ignored if a definition for the view does not exist at the current server. This option can be specified only by the owner of the object.

view-name

Names the view. The name, including the implicit or explicit qualifier, must not identify a table, view, nickname or alias described in the catalog. The qualifier must not be SYSIBM, SYSCAT, SYSFUN, or SYSSTAT (SQLSTATE 42939). If the statement is executed in a tenant other than the SYSTEM tenant, the schema cannot start with ‘SYS’ (SQLSTATE 42939).

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.

column-name

Names the columns in the view. If a list of column names is specified, it must consist of as many names as there are columns in the result table of the fullselect. Each column-name must be unique and unqualified. If a list of column names is not specified, the columns of the view inherit the names of the columns of the result table of the fullselect.

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.

OF type-name

Specifies that the columns of the view are based on the attributes of the structured type identified by type-name. If type-name is specified without a schema name, the type name is resolved by searching the schemas on the SQL path (defined by the FUNCPATH preprocessing option for static SQL and by the CURRENT PATH register for dynamic SQL). The type name must be the name of an existing user-defined type (SQLSTATE 42704) and it must be a structured type that is instantiable (SQLSTATE 428DP).

MODE DB2SQL

This clause is used to specify the mode of the typed view. This is the only valid mode currently supported.

UNDER superview-name

Indicates that the view is a subview of superview-name. The superview must be an existing view (SQLSTATE 42704) and the view must be defined using a structured type that is the immediate supertype of type-name (SQLSTATE 428DB). The schema name of view-name and superview-name must be the same (SQLSTATE 428DQ). The view identified by superview-name must not have any existing subview already defined using type-name (SQLSTATE 42742).

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

INHERIT SELECT PRIVILEGES

Any user or group holding a SELECT privilege on the superview will be granted an equivalent privilege on the newly created subview. The subview definer is considered to be the grantor of this privilege.

OID-column

Defines the object identifier column for the typed view.

REF IS OID-column-name USER GENERATED

Specifies that an object identifier (OID) column is defined in the view as the first column. An OID is required for the root view of a view hierarchy (SQLSTATE 428DX). The view must be a typed view (the OF clause must be present) that is not a subview (SQLSTATE 42613). The name for the column is defined as OID-column-name and cannot be the same as the name of any attribute of the structured type type-name (SQLSTATE 42711). The first column specified in fullselect must be of type REF(type-name) (you may need to cast it so that it has the appropriate type). If UNCHECKED is not specified, it must be based on a not nullable column on which uniqueness is enforced through an index (primary key, unique constraint, unique index, or OID-column). This column will be referred to as the object identifier column or OID column. The keywords USER GENERATED indicate that the initial value for the OID column must be provided by the user when inserting a row. Once a row is inserted, the OID column cannot be updated (SQLSTATE 42808).

UNCHECKED

Defines the object identifier column of the typed view definition to assume uniqueness even though the system can not prove this uniqueness. This is intended for use with tables or views that are being defined into a typed view hierarchy where the user knows that the data conforms to this uniqueness rule but it does not comply with the rules that allow the system to prove uniqueness. UNCHECKED option is mandatory for view hierarchies that range over multiple hierarchies or legacy tables or views By specifying UNCHECKED, the user takes responsibility for ensuring that each row of the view has a unique OID. If the user fails to ensure this property, and a view contains duplicate OID values, then a path-expression or DEREF operator involving one of the non-unique OID values may result in an error (SQLSTATE 21000).

with-options

Defines additional options that apply to columns of a typed view.

column-name WITH OPTIONS

Specifies the name of the column for which additional options are specified. The column-name must correspond to the name of an attribute defined in (not inherited by) the type-name of the view. The column must be a reference type (SQLSTATE 42842). It cannot correspond to a column that also exists in the superview (SQLSTATE 428DJ). A column name can only appear in one WITH OPTIONS SCOPE clause in the statement (SQLSTATE 42613).

SCOPE

Identifies the scope of the reference type column. A scope must be specified for any column that is intended to be used as the left operand of a dereference operator or as the argument of the DEREF function.

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.

typed-table-name

The name of a typed table. The table must already exist or be the same as the name of the table being created (SQLSTATE 42704). The data type of column-name must be REF(S), where S is the type of typed-table-name (SQLSTATE 428DM). No checking is done of any existing values in column-name to ensure that the values actually reference existing rows in typed-table-name.

typed-view-name

The name of a typed view. The view must already exist or be the same as the name of the view being created (SQLSTATE 42704). The data type of column-name must be REF(S), where S is the type of typed-view-name (SQLSTATE 428DM). No checking is done of any existing values in column-name to ensure that the values actually reference existing rows in typed-view-name.

READ ONLY

Identifies the column as a read-only column. This option is used to force a column to be read-only so that subview definitions can specify an expression for the same column that is implicitly read-only.

AS

Identifies the view definition.

WITH common-table-expression

Defines a common table expression for use with the fullselect that follows. A common table expression cannot be specified when defining a typed view.

fullselect

Defines the view. At any time, the view consists of the rows that would result if the SELECT statement were executed. The data type of the columns of the view cannot be a distinct type with data type constraints, array type, cursor type, or row type. The fullselect must not reference host variables, parameter markers, or declared temporary tables. However, a parameterized view can be created as an SQL table function. The fullselect cannot include an SQL data change statement in the FROM clause (SQLSTATE 428FL). The fullselect also cannot reference a transient external table (SQLSTATE 428I).

If a view is created by using a 'SELECT *' statement, the view is not updated when a new column is added to the base table.

For Typed Views and Subviews: The fullselect must conform to the following rules otherwise an error is returned (SQLSTATE 428EA unless otherwise specified).

• The fullselect must not include references to the DBPARTITIONNUM or HASHEDVALUE functions, non-deterministic functions, or functions defined to have external action.
• The body of the view must consist of a single subselect, or a UNION ALL of two or more subselects. Let each of the subselects participating directly in the view body be called a branch of the view. A view may have one or more branches.
• The FROM-clause of each branch must consist of a single table or view (not necessarily typed), called the underlying table or view of that branch.
• The underlying table or view of each branch must be in a separate hierarchy (that is, a view cannot have multiple branches with their underlying tables or views in the same hierarchy).
• None of the branches of a typed view definition may specify GROUP BY or HAVING.
• If the view body contains UNION ALL, the root view in the hierarchy must specify the UNCHECKED option for its OID column.

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:

• If T1 and T2 are not in the same hierarchy, then the root view in the view hierarchy must specify the UNCHECKED option for its OID column.
• If T1 and T2 are in the same hierarchy, then BR1 and BR2 must contain predicates or ONLY-clauses that are sufficient to guarantee that their row-sets are disjoint.

For typed subviews defined using EXTEND AS: For every branch in the body of the subview:

• The underlying table of each branch must be a (not necessarily proper) subtable of some underlying table of the immediate superview.
• The expressions in the SELECT list must be assignable to the non-inherited columns of the subview (SQLSTATE 42854).

For typed subviews defined using AS without EXTEND:

• For every branch in the body of the subview, the expressions in the SELECT-list must be assignable to the declared types of the inherited and non-inherited columns of the subview (SQLSTATE 42854).
• The OID-expression of each branch over a given hierarchy in the subview must be equivalent (except for casting) to the OID-expression in the branch over the same hierarchy in the root view.
• The expression for a column not defined (implicitly or explicitly) as READ ONLY in a superview must be equivalent in all branches over the same underlying hierarchy in its subviews.

WITH 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 does not satisfy the search conditions of the view.

WITH CHECK OPTION must not be specified if any of the following conditions is true:

• The view is read-only (SQLSTATE 42813). If WITH CHECK OPTION is specified for an updatable view that does not allow inserts, the constraint applies to updates only.
• The view references the DBPARTITIONNUM or HASHEDVALUE function, a non-deterministic function, or a function with external action (SQLSTATE 42997).
• A nickname is the update target of the view.
• A view that has an INSTEAD OF trigger defined on it is the update target of the view (SQLSTATE 428FQ).

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.

CASCADED

The WITH CASCADED CHECK OPTION constraint on a view V means that V inherits the search conditions as constraints from any updatable view on which V is dependent. Furthermore, every updatable view that is dependent on V is also subject to these constraints. Thus, the search conditions of V and each view on which V is dependent are ANDed together to form a constraint that is applied for an insert or update of V or of any view dependent on V.

LOCAL

The WITH LOCAL CHECK OPTION constraint on a view V means the search condition of V is applied as a constraint for an insert or update of V or of any view that is dependent on V.

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)

WITH NO ROW MOVEMENT or WITH ROW MOVEMENT

Specifies the action to take for an updatable UNION ALL view when a row is updated in a way that violates a check constraint on the underlying table. The default is WITH NO ROW MOVEMENT.

WITH NO ROW MOVEMENT

Specifies that an error (SQLSTATE 23513) is to be returned if a row is updated in a way that violates a check constraint on the underlying table.

WITH ROW MOVEMENT

Specifies that an updated row is to be moved to the appropriate underlying table, even if it violates a check constraint on that table.

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 defined using the WITH ROW MOVEMENT clause, cannot contain any references to a system-period temporal table, application-period temporal table, or bitemporal table.

Notes

• Creating a view with a schema name that does not already exist will result in the implicit creation of that schema provided the authorization ID of the statement has IMPLICIT_SCHEMA authority. The schema owner is SYSIBM. The CREATEIN privilege on the schema is granted to PUBLIC.
• View columns inherit the NOT NULL WITH DEFAULT attribute from the base table or view except when columns are derived from an expression. When a row is inserted or updated into an updatable view, it is checked against the constraints (primary key, referential integrity, and check) if any are defined on the base table.
• A new view cannot be created if it uses an inoperative view in its definition. (SQLSTATE 51024).
• If an object referenced in the view body does not exist or is marked invalid, or the definer temporarily doesn't have privileges to access the object, and if the database configuration parameter auto_reval is set to DEFERRED_FORCE, then the view will still be created successfully. The view will be marked invalid and will be revalidated the next time it is referenced.
• This statement does not support declared temporary tables (SQLSTATE 42995).
• Views based on column-organized tables:
  • Creating a typed view on column-organized tables is not supported.
  • The WITH CHECK OPTION clause cannot be specified if a column-organized table is part of the view definition.
• Deletable views: A view is deletable if an INSTEAD OF trigger for the delete operation has been defined for the view, or if all of the following conditions are true:
  • Each FROM clause of the outer fullselect identifies only one base table (with no OUTER clause), deletable view (with no OUTER clause), deletable nested table expression, or deletable common table expression (cannot identify a nickname). Also, any period-specification specified for the base table or deletable view does not reference the SYSTEM_TIME period.
  • The outer fullselect does not include a VALUES clause
  • The outer fullselect does not include a GROUP BY clause or HAVING clause
  • The outer fullselect does not include aggregate functions in the select list
  • The outer fullselect does not include SET operations (UNION, EXCEPT or INTERSECT) with the exception of UNION ALL
  • The base tables in the operands of a UNION ALL must not be the same table and each operand must be deletable
  • The select list of the outer fullselect does not include DISTINCT
• Updatable views: A column of a view is updatable if an INSTEAD OF trigger for the update operation has been defined for the view, or if all of the following conditions are true:
  • The view is deletable (independent of an INSTEAD OF trigger for delete), the column resolves to a column of a base table (not using a dereference operation), and the READ ONLY option is not specified
  • All the corresponding columns of the operands of a UNION ALL have exactly matching data types (including length or precision and scale) and matching default values if the fullselect of the view includes a UNION ALL

  A view is updatable if any column of the view is updatable.

• Insertable views: A view is insertable if an INSTEAD OF trigger for the insert operation has been defined for the view, or at least one column of the view is updatable (independent of an INSTEAD OF trigger for update), and the fullselect of the view does not include UNION ALL.

  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.

• Read-only views: A view is read-only if it is not deletable, updatable, or insertable.

  The READONLY column in the SYSCAT.VIEWS catalog view indicates if a view is read-only without considering period specifications or INSTEAD OF triggers.

• Common table expressions and nested table expressions follow the same set of rules for determining whether they are deletable, updatable, insertable, or read-only.
• Special registers for temporal support: The values of the CURRENT TEMPORAL SYSTEM_TIME and CURRENT TEMPORAL BUSINESS_TIME special registers have no impact on the query expression that defines a view while it is being defined. When a view is used in an SQL statement, the values of the CURRENT TEMPORAL SYSTEM_TIME and CURRENT TEMPORAL BUSINESS_TIME special registers for the session processing the SQL statement are applied to the view.
• Inoperative views:  An inoperative view is a view that is no longer available for SQL statements. A view becomes inoperative if:
  • A privilege, upon which the view definition is dependent, is revoked.
  • An object such as a table, nickname, alias or function, upon which the view definition is dependent, is dropped.
  • A view, upon which the view definition is dependent, becomes inoperative.
  • A view that is the superview of the view definition (the subview) becomes inoperative.

  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 re-created 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 re-created 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 re-create 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.

• Privileges: 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 each of the following authorities:
  • ACCESSCTRL or SECADM on the database or ACCESSCTRL on the schema containing every base table, view, or nickname identified in the fullselect
  • DATAACCESS on the database or DATAACCESS on the schema containing every base table, view, or nickname identified in the fullselect
  • DBADM or SCHEMAADM on the schema containing every base table, view, or nickname identified in the fullselect

  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.

• Scope and REF columns: When selecting a reference type column in the fullselect of a view definition, consider the target type and scope that is required.
  • If the required target type and scope is the same as the underlying table or view, the column can simply be selected.
  • If the scope needs to be changed, use the WITH OPTIONS SCOPE clause to define the required scope table or view.
  • If the target type of the reference needs to be changed, the column must be cast first to the representation type of the reference and then to the new reference type. The scope in this case can be specified in the cast to the reference type or using the WITH OPTIONS SCOPE clause. For example, assume you select column Y defined as REF(TYP1) SCOPE TAB1. You want this to be defined as REF(VTYP1) SCOPE VIEW1. The select list item would be as follows:

       CAST(CAST(Y AS VARCHAR(16) FOR BIT DATA) AS REF(VTYP1) SCOPE VIEW1)

• Identity columns: A column of a view is considered an identity column, if the element of the corresponding column in the fullselect of the view definition is the name of an identity column of a table, or the name of a column of a view which directly or indirectly maps to the name of an identity column of a base table.
  In all other cases, the columns of a view will not get the identity property. For example:

  • the select-list of the view definition includes multiple instances of the name of an identity column (that is, selecting the same column more than once)
  • the view definition involves a join
  • a column in the view definition includes an expression that refers to an identity column
  • the view definition includes a UNION

  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.

• Federated views: A federated view is a view that includes a reference to a nickname somewhere in the fullselect. The presence of such a nickname changes the authorization model used for the view when the view is subsequently referenced in a query.

  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.

• ROW MOVEMENT, triggers and constraints: When a view that is defined using the WITH ROW MOVEMENT clause is updated, the sequence of trigger and constraints operations is as follows:
  1. BEFORE UPDATE triggers are activated for all rows being updated, including rows that will eventually be moved.
  2. The update operation is processed.
  3. Constraints are processed for all updated rows.
  4. AFTER UPDATE triggers (both row-level and statement-level) are activated in creation order, for all rows that satisfy the constraints after the update operation. Because this is an UPDATE statement, all UPDATE statement-level triggers are activated for all underlying tables.
  5. BEFORE DELETE triggers are activated for all rows that did not satisfy the constraints after the update operation (these are the rows that are to be moved).
  6. The delete operation is processed.
  7. Constraints are processed for all deleted rows.
  8. AFTER DELETE triggers (both row-level and statement-level) are activated in creation order, for all deleted rows. Statement-level triggers are activated for only those tables that are involved in the delete operation.
  9. BEFORE INSERT triggers are activated for all rows being inserted (that is, the rows being moved). The new transition tables for the BEFORE INSERT triggers contain the input data provided by the user. Such triggers cannot contain an UPDATE, a DELETE, or an INSERT operation, or invoke any routine containing such operations (SQLSTATE 42987).
  10. The insert operation is processed.
  11. Constraints are processed for all inserted rows.
  12. AFTER INSERT triggers (both row-level and statement-level) are activated in creation order, for all inserted rows. Statement-level triggers are activated for only those tables that are involved in the insert operation.
• Nested UNION ALL views: A view defined with UNION ALL and based, either directly or indirectly, on a view that is also defined with UNION ALL cannot be updated if either view is defined using the WITH ROW MOVEMENT clause (SQLSTATE 429BK).
• Considerations for implicitly hidden columns: It is possible that the result table of the fullselect will include a column of the base table that is defined as implicitly hidden. This can occur when the implicitly hidden column is explicitly referenced in the fullselect of the view definition. However, the corresponding column of the view does not inherit the implicitly hidden attribute. Columns of a view cannot be defined as hidden.
• Subselect: The isolation-clause cannot be specified in the fullselect (SQLSTATE 42601).
• Obfuscation: The CREATE VIEW statement can be submitted in obfuscated form. In an obfuscated statement, only the view name is readable. The rest of the statement is encoded in such a way that is not readable but can be decoded by the database server. Obfuscated statements can be produced by calling the DBMS_DDL.WRAP function.
• Syntax alternatives: The following syntax alternatives are supported for compatibility with previous versions of Db2® and with other database products.
  • The FEDERATED keyword can be specified between the keywords CREATE and VIEW. The FEDERATED keyword is ignored, however, because a warning is no longer returned if federated objects are used in the view definition.

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:

  

  User ZORPIE (who does not have ACCESSCTRL, DATAACCESS, or DBADM authority) has the privileges shown in parentheses for each object:
  1. ZORPIE will get CONTROL privilege on the view that she creates with:

        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.
  2. ZORPIE will not be allowed to create the view:

        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).
  3. ZORPIE will get CONTROL privilege on the view that she creates with:

        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.
  4. ZORPIE will get SELECT privilege on the view that she creates with:

        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.
  5. ZORPIE will get INSERT, UPDATE and DELETE privilege WITH GRANT OPTION and SELECT privilege on the view VE in the following view definition.

        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.