DB2 10.5 for Linux, UNIX, and Windows

CREATE VIEW statement

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

Authorization

The privileges held by the authorization ID of the statement must include at least one of the following authorities:
  • IMPLICIT_SCHEMA authority on the database, if the implicit or explicit schema name of the view does not exist
  • CREATEIN privilege on the schema, if the schema name of the view refers to an existing schema
  • DBADM authority
and at least one of the following authorities for each table, view, or nickname identified in any fullselect:
  • CONTROL privilege on that table, view, or nickname
  • SELECT privilege on that table, view, or nickname
  • DATAACCESS authority
If creating a subview:
  • The authorization ID of the statement must be the same as the definer of the root table of the table hierarchy, or
  • The privileges held by the authorization ID must include DBADM authority
and
  • The authorization ID of the statement must have SELECT WITH GRANT privilege on the underlying table of the subview, or the superview must not have SELECT privilege granted to any user other than the view definer, or
  • ACCESSCTRL authority and one of the following authorities:
    • SELECT privilege on the underlying table of the subview
    • DATAACCESS authority
If WITH ROW MOVEMENT is specified, the privileges held by the authorization ID of the statement must include at least one of the following authorities:
  • UPDATE privilege on that table or view
  • DATAACCESS authority

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

Read syntax diagramSkip visual syntax diagram
>>-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-------------|

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

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

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

Examples

Related reference:
CREATE FUNCTION (SQL scalar, table, or row) statement
fullselect
SQL queries
subselect
WRAP function - Obfuscate a DDL statement