CREATE VIEW
The CREATE VIEW statement creates a view on one or more tables or views at the current server.
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 privilege to create in the schema. For more information, see Privileges necessary to create in a schema.
- Database administrator authority
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
- *CHANGE to the data dictionary if the library into which the view is created is an SQL schema with a data dictionary
- Database administrator 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 fullselect,
or indirectly through views referenced in the fullselect:
- The SELECT privilege on the table or view, and
- The system authority *EXECUTE on the library containing the table or view
- Database administrator authority
To replace an existing view, the privileges held by the authorization ID of the statement must include at least one of the following:
- The following system authorities:
- The system authority of *OBJMGT on the view
- All authorities needed to DROP the view
- Database administrator authority
For information about the system authorities corresponding to SQL privileges, see Corresponding System Authorities When Checking Privileges to a Table or View and Corresponding System Authorities When Checking Privileges to a Distinct Type.
Syntax
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. The existing object cannot be a DDS-created logical file. This option is ignored if a definition for the view does not exist at the current server.
- RECURSIVE
- Indicates
that the view is potentially recursive.
If a fullselect of the view contains a reference to the view itself in a FROM clause, the view is a recursive view. Views using recursion are useful in supporting applications such as bill of materials (BOM), reservation systems, and network planning.
The restrictions that apply to a recursive view are similar to those for a recursive common table expression:
- A list of column-names must be specified following the view-name unless the result columns of the fullselect are already named.
- The UNION ALL set operator must be specified.
- The first fullselect of the first union (the initialization fullselect) must not include a reference to the view itself in any FROM clause.
- Each fullselect that is part of the recursion cycle must not include any aggregate functions, GROUP BY clauses, or HAVING clauses.
- The FROM clauses of each fullselect can include at most one reference to the view that is part of a recursion cycle.
- The table being defined in the common-table-expression cannot be referenced in a subquery of a fullselect that defines the common-table-expression.
- LEFT OUTER JOIN and FULL OUTER JOIN are not allowed if the common-table-expression is the right operand. RIGHT OUTER JOIN and FULL OUTER JOIN are not allowed if the common-table-expression is the left operand.
If a column name of the view is referred to in the iterative fullselect, the attributes of the result columns are determined using the rules for result columns. For more information see Rules for result data types.
Recursive views are not allowed if the query specifies:
- a distributed table,
- a table with a read trigger,
- a table referenced directly or indirectly in the fullselect must not be a DDS-created logical file, or
- a logical file built over multiple physical file members.
- view-name
- Names
the view. The name, including the implicit or explicit qualifier,
must not be the same as an alias, file, index, table, or view that
already exists at the current server.
If SQL names were specified, the view will be created in the schema specified by the implicit or explicit qualifier.
If system names were specified, the view will be created in the schema that is specified by the qualifier. If not qualified and there is no default schema, the view name will be created in the same schema as the first table specified on the first FROM clause (including FROM clauses in any common table expressions or nested table expression). If no tables are referenced in the fullselect, the view will be created in the same schema as the first user defined table function. If no table or user defined table function is referenced in the fullselect, the current library (*CURLIB) will be used.
If a view name is not a valid system name and the FOR SYSTEM NAME clause is not used, Db2® for i will generate a system name. For information about the rules for generating the name, see Rules for Table Name Generation.
- FOR SYSTEM NAME system-object-identifier
- Identifies the system-object-identifier of the view. system-object-identifier must
not be the same as a table, view, alias, or index that already exists
at the current server. The system-object-identifier must be
an unqualified system identifier.
When system-object-identifier is specified, view-name must not be a valid system object name.
- (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 and system-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 and system
names of the columns of the result table of the fullselect.
A list of column names (and system column names) must be specified 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 IBM® i 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 about how system column names are generated, see Rules for Column Name Generation.
- AS
- Defines the view.
- WITH common-table-expression
- Defines a common table expression for use with the fullselect that follows. For an explanation of common table expression, see common-table-expression.
- fullselect
- Defines the view. At any time, the view consists of the rows that
would result if the fullselect were executed.
fullselect must not reference variables, but may reference global variables.
The maximum number of columns allowed in a view is 8000. The column name lengths and the length of the WHERE clause also reduce this number. The maximum number of base tables allowed in the view is 256.
For an explanation of fullselect, see fullselect.
common-table-expression defines a common table expression for use with the fullselect that follows. For more information see common-table-expression.
A declared temporary table must not be referenced in the fullselect unless the view is created in schema QTEMP.
The ORDER BY, FETCH, and OFFSET clauses may not be specified in the outer fullselect of the view.
- WITH CASCADED CHECK OPTION or WITH LOCAL CHECK OPTION
- Specifies
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.
CHECK OPTION must not be specified if:
- the view is read-only
- the definition of the view includes a subquery other than a scalar fullselect in the outer select list of the view
- the definition of the view contains a non-deterministic, MODIFIES SQL DATA, or EXTERNAL ACTION function in other than the outer select list of the view
- the definition of the view contains a special register in other than the outer select list of the view
- the view references another view and that view has an INSTEAD OF trigger
- the view is recursive
If CHECK OPTION is specified for an updatable view that does not allow inserts, then the check option applies to updates only.
If 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 a 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 difference between the two forms of the CHECK OPTION clause, CASCADED and LOCAL, is meaningful only when a view is dependent on another view. The default is CASCADED. The view upon which another view is directly or indirectly defined is an underlying view.
- CASCADED
- The WITH CASCADED CHECK OPTION on a view V is inherited by any
updatable view that is directly or indirectly dependent on V. Thus,
if an updatable view is defined on V, the check option on V also applies
to that view, even if WITH CHECK OPTION is not specified on that view.
For example, consider the following updatable 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
SQL statement Description of result INSERT INTO V1 VALUES(5) Succeeds because V1 does not have a CHECK OPTION clause and it is not dependent on any other view that has a CHECK OPTION clause. INSERT INTO V2 VALUES(5) Results in an error because the inserted row does not conform to the search condition of V1 which is implicitly part of the definition of V2. INSERT INTO V3 VALUES(5) Results in an error because V3 is dependent on V2 which has a CHECK OPTION clause and the inserted row does not conform to the definition of V2. INSERT INTO V3 VALUES(200) Succeeds even though it does not conform to the definition of V3 (V3 does not have the view CHECK OPTION clause specified); it does conform to the definition of V2 (which does have the view CHECK OPTION clause specified). - LOCAL
- 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 the 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 the following underlying views are checked when a row is inserted or updated:
- views that specify WITH LOCAL CHECK OPTION
- views that specify WITH CASCADED CHECK OPTION
- all underlying views of a view that specifies WITH CASCADED CHECK OPTION
In contrast, WITH CASCADED CHECK OPTION specifies that the search conditions of all underlying views are checked when a row is inserted or updated.
The difference between CASCADED and LOCAL is best shown by example. Consider the following updatable views where x and y represent either LOCAL or CASCADED:
V1 defined on table 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 1. 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
- RCDFMT format-name
- An
unqualified name that designates the IBM i record format name
of the view. A format-name is a system identifier.
If a record format name is not specified, the format-name is the same as the system-object-name of the view.
Notes
View ownership: If SQL names were specified:
- If a user profile with the same name as the schema into which the view is created exists, the owner of the view is that user profile.
- Otherwise, the owner of the view is the user profile or group user profile of the thread executing the statement.
If system names were specified, the owner of the view is the user profile or group user profile of the thread executing the statement.
View authority: 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 schema.
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 WITH GRANT OPTION on the view and the authorization to drop the view.
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 fullselect. These privileges can be granted only if the privileges from which they are derived can also be granted.
- Any existing comment or label is discarded.
- Authorized users are maintained. The object owner could change.
- Current journal auditing is preserved. However, unlike other objects, REPLACE of a view will generate a ZC (change object) journal audit entry.
- Any INSTEAD OF triggers defined for the view are dropped (any triggers that reference the view are not dropped).
- Any views and materialized query tables dependent on the view will be recreated, if possible. If it is not possible to recreate a dependent view or materialized query table, an error is returned.
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 are true:
- The outer fullselect identifies only one base table or deletable view that is not a catalog table or view. It cannot be a nested table expression, table function, or a table with a FOR SYSTEM_TIME clause.
- 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 a UNION, UNION ALL, EXCEPT, or INTERSECT operator.
- The outer fullselect does not include the DISTINCT clause.
Updatable views: 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 are true:
- independent of an INSTEAD OF trigger for delete, the view is deletable
- at least one column of the view is updatable.
A column of a view is updatable if an INSTEAD OF trigger for the update operation has been defined for the view, or if the corresponding result column of the subselect is derived solely from a column of a table or an updatable column of another view (that is, it is not derived from an expression that contains an operator, scalar function, constant, or a column that itself is derived from such expressions).
Insertable views: A view is insertable if an INSTEAD OF trigger has been defined for the view, or if at least one column of the view is updatable.
Read-only views: A view is read-only if it is not deletable.
A read-only view cannot be the object of an INSERT, UPDATE, or DELETE statement.
Special registers for temporal support: The value of the CURRENT TEMPORAL SYSTEM_TIME special register has 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 value of the CURRENT TEMPORAL SYSTEM_TIME special register for the session processing the SQL statement is applied to the view if the value of the SYSTIME option is YES.
Unqualified table names: If the CREATE VIEW statement refers to an unqualified table name, the following rules are applied to determine which table is actually being referenced:
- If the unqualified name corresponds to one or more common table expression table-identifiers that are specified in the fullselect, the name identifies the common table expression that is in the innermost scope.
- Otherwise, the name identifies a persistent table, a temporary table, or a view that is present in the default schema.
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.
Collating sequence: The view is created with the collating sequence in effect at the time the CREATE VIEW statement is executed. The collating sequence of the view applies to all comparisons involving SBCS data and mixed data in the view fullselect. When the view is included in a query, an intermediate result table is generated from the view fullselect. The collating sequence in effect when the query is executed applies to any selection specified in the query.
View attributes: Views are created as nonkeyed logical files. 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 date and time format used for date and time result columns is ISO.
A view created over a distributed table is created on all of the 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 about distributed tables, see the DB2® Multisystem topic collection.
Identity and row change timestamp columns: A column of a view is considered an identity or row change timestamp column if the element of the corresponding column in the fullselect of the view definition is the name of an identity or row change timestamp column of a table, or the name of a column of a view which directly or indirectly maps to the name of an identity or row change timestamp column of a base table. In all other cases, the columns of a view will not get the identity or row change timestamp 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 or INTERSECT
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_PROJ
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'
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
Example 6: Create a recursive view that returns a similar result as a common table expression, see Example 1: Single level explosion.
CREATE RECURSIVE VIEW RPL (PART, SUBPART, QUANTITY) AS
SELECT ROOT.PART, ROOT.SUBPART, ROOT.QUANTITY
FROM PARTLIST ROOT
WHERE ROOT.PART = '01'
UNION ALL
SELECT CHILD.PART, CHILD.SUBPART, CHILD.QUANTITY
FROM RPL PARENT, PARTLIST CHILD
WHERE PARENT.SUBPART = CHILD.PART
SELECT DISTINCT *
FROMRPL
ORDER BY PART, SUBPART, QUANTITY