Description

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:

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:

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, 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 a view name is not a valid system name, DB2 UDB for iSeries SQL will generate a system name. For information on the rules for generating the name, see Rules for Table Name Generation.

(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 i5/OS 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 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.

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.

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:

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.

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 53. 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 i5/OS 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.