This statement can be embedded in an application program or issued interactively.
For every table or view identified in the subselect of the CREATE VIEW statement, you must possess either DBADM authority for the database, SELECT privilege on the table or view, overall SYSADM or SYSCTRL authority for the location, or be the owner of the source table or view.
Authority requirements for using the view depend on the owner assigned to the view. (See the view-name parameter description under Syntax.) In order to use a view you must have the appropriate SELECT, UPDATE, INSERT, or DELETE privilege on the view.
The following authorization rules apply with using CREATE VIEW with an XDB Server:
CREATE VIEW view-name [(view-col-name[,...])]
AS subselect [WITH [CASCADED | LOCAL] CHECK OPTION]
| view-name | Is any long identifier naming the view. This view name cannot have the same name as another view, table or synonym in the
same location. If qualified, the name can be two-part or three-part name. If the name has three-parts, the first part must
match the value of the CURRENT LOCATION register. The AuthID that qualifies the view name becomes the view owner. If the view
name is unqualified in the CREATE VIEW statement, then the view owner is the SQL AuthID of the application process.
The view owner always acquires the authority to drop the view, as well as the SELECT privilege on the view. The owner must already possess grantable SELECT privileges on every table or view identified in the first FROM clause of the view subselect statement, in order to obtain a grantable SELECT privilege on the view. These grantable privileges must be acquired by the owner before the creation of the view. INSERT, UPDATE and DELETE privileges on the view are also acquired by the owner if the view is not "read only." In these cases, the first FROM clause of the subselect identifies a single table or view only. If the owner already has either INSERT, UPDATE or DELETE privileges on this table or view, the owner acquires that privilege on the newly created view. These privileges are grantable only if the object privileges from which the view was derived are also grantable. These privileges must be acquired by the owner before the new view is created. |
| view-col-name | Any long identifier specifying a column in the view. Column names for views must satisfy the same naming criteria as table column names. If multiple view-col-name items are specified, the number of these columns must match the number of columns in the result table specified in the subselect. If the view-col-name list is missing, the view columns take the names of the columns specified in the subselect result table. If the result table of the subselect has duplicate column names or unnamed columns derived from constants, functions or expressions, a view-col-name list must be specified. |
| subselect | A SELECT command statement (following the keyword AS) producing a results table constituting the data contained in the view. |
When creating views with subselects all referenced table(s) and all subselects in a single SQL statement must be from the same location -- which must also be the current location. subselect must not refer to any declared temporary tables. It must also not refer to host variables or include parameter markers (question marks). CREATE VIEW is not a DUW or RUW capable command.
When data in a view is updated, the changes are reflected in the underlying database table. For views to be updatable, the subselect used to define the view must satisfy the following criteria:
A simple way to determine whether a view can be defined as an updatable view is to attempt to create it using the WITH CHECK OPTION clause. If the view cannot be defined as updatable view, an error message is displayed.
CHECK OPTION must not be specified if any of the following conditions are true:
Expressions, including those involving aggregate functions, are permitted in views. If expressions are selected, you must explicitly name all columns of the view. Such views are read-only views.
You may create views by joining other tables and views. The SELECT statement must explicitly name all columns if the tables or views being joined contain duplicate names. Views containing joins are read-only views.
Views defined using GROUP BY cannot be joined with another table or view. These views cannot be updated. HAVING clauses are allowed in view definitions containing GROUP BY.
ORDER BY clauses are not permitted in the SELECT statement that defines a view.
The UNION operator cannot be used in the definition of a view.
The search condition of a view is specified in the first WHERE clause of the subselect that defines the view. The WITH CHECK OPTION specifies that every row of the view must conform to the search condition of this WHERE clause. The XDB Server enforces this constraint whenever rows of the view are inserted or updated. If an updatable view is created using the WITH CHECK OPTION clause, the system automatically tests whether inserts or updates to the view will satisfy the WHERE clause of the view definition. If they do not, the system signals an error, and any changes to the underlying tables are aborted.
To prevent the search condition of the view from checking insert or update operations, omit the WITH CHECK OPTION clause. This omission allows the insertion of "nonconforming" rows of data into the underlying table, as well as the updating of table rows in such a way that these rows will no longer be retrievable by the original view.
The two forms of the check option (CASCADED and LOCAL) are meaningful only when views are defined on each other. The view upon which another view is directly or indirectly defined is an underlying view.
Update and insert operations must satisfy the search conditions of the view and underlying views, regardless of whether the underlying views were defined with a check option. This value is the default.
Update and insert operations must satisfy the search conditions of the view and underlying views that are defined with a check option.
Creating or dropping a view causes the system to automatically update the SYSIBM.SYSTABLES, SYSIBM.SYSCOLUMNS and SYSIBM.SYSVIEWS catalog tables. Dropping a table or view causes all views dependent on that table or view to be dropped as well. You can query the system catalog tables for table, view, column and index information. The query results can be formatted into your own reports. See XDB Server System Tables for a list of the system catalog tables used for this purpose.
You can use SQLWizard to examine table definitions interactively. In addition, the Catalog Browser function of SQLWizard lists table, view and index definitions.
Read-only views
A view is read-only if one or more of the following statements is true of its definition:
A read-only view cannot be the object of an INSERT, UPDATE, or DELETE statement. A view that includes GROUP BY or HAVING cannot be referred to in a subquery of a basic predicate.
The following example defines a view that is a subset of the PART table. The optional view column list is omitted. The view column names will be the same as the PART table column names:
CREATE VIEW redparts AS
SELECT *
FROM part
WHERE color = 'RED'
WITH CHECK OPTION
If you retrieve all records from the REDPARTS view, the result would be:
| pno | pname | color | weight | city |
|---|---|---|---|---|
| P1 | NUT | RED | 12 | LONDON |
| P4 | SCREW | RED | 14 | LONDON |
| P6 | COG | RED | 19 | LONDON |
This view is not updatable. However, since the view was created using the WITH CHECK OPTION clause, you cannot update the view or insert any records into the view that do not satisfy the condition color = 'RED'.
The next example shows a read-only view defined by joining three tables, PART, SUPPLIER and PARTSUPP:
CREATE VIEW s_ps_p
(s#, supplier, p#, part, quantity) AS
SELECT sno, sname, pno, pname, qty
FROM supplier s, partsupp ps, part p
WHERE s.sno = ps.sno
AND p.pno = ps.pno
If you retrieve all records from the S_PS_P view, the result would be:
| s# | supplier | p# | part | quantity |
|---|---|---|---|---|
| S1 | SMITH | P1 | NUT | 300 |
| S1 | SMITH | P3 | CAM | 400 |
| S2 | JONES | P2 | BOLT | 400 |
| S3 | BLAKE | P2 | BOLT | 200 |
| S1 | SMITH | P2 | BOLT | 200 |
| S1 | SMITH | P4 | SCREW | 200 |
| S1 | SMITH | P5 | CAM | 100 |
| S1 | SMITH | P6 | COG | 100 |
| S2 | JONES | P1 | NUT | 300 |
| S4 | CLARK | P2 | BOLT | 200 |
| S4 | CLARK | P4 | SCREW | 300 |
| S4 | CLARK | P5 | CAM | 400 |
The following is an example of a read-only view containing aggregate functions and a GROUP BY clause:
CREATE VIEW order_statistics
(o_no, total_items, total_order) AS
SELECT o_no, COUNT(p_no), SUM(quantity * price)
FROM items
GROUP BY o_no
Selecting all records from the view would produce the following result:
| o_no | tot_items | total_order |
|---|---|---|
| 1 | 1 | $120.00 |
| 2 | 2 | $985.00 |
| 3 | 1 | $240.00 |
| 4 | 2 | $830.00 |
| 5 | 2 | $2,070.00 |
Description
When a view is created or dropped, the system automatically updates the appropriate system catalog tables containing the definitions of all views in the current location. When a user queries the view, the system reads the view definition, and performs the corresponding query to obtain the result.
If you modify the underlying tables used by a view, you should drop the view and recreate it.