Previous Topic Next topic Print topic


CREATE VIEW

The CREATE VIEW command is used to define a view or virtual table that is based on one or more tables or views. The view definition is stored at the current location. While a view does not actually contain data, it provides indirect access to data contained in the tables upon which the view is based. Views may be used to define subsets of tables, or to combine data from several tables into a logical view. Views may contain expressions that are computed from other columns, including aggregate functions such as averages, totals, etc.

Invocation

This statement can be embedded in an application program or issued interactively.

Authorization

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:

  • If the AuthID for the application process possesses SYSADM authority, the view owner can be any AuthID.
  • If the view satisfies the rule in the previous bullet, and if the CREATE VIEW statement is error-free, the view is created, whether or not the owner has privileges on the tables and views identified in the view subselect.
  • If the AuthID for the application process does not possess SYSADM or SYSCTRL authority, only the AuthIDs of the process can own the view. In these circumstances, the view privileges match those held by the AuthID selected for ownership.

Syntax

CREATE VIEW view-name [(view-col-name[,...])] 
    AS subselect [WITH [CASCADED | LOCAL] CHECK OPTION]

Parameters:

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.

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.

Note:

If you modify the underlying tables used by a view, you should drop the view and recreate it.

Updatable Views

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:

  • The query cannot contain DISTINCT.
  • The SELECT clause can contain only column names (no expressions or functions, including aggregate functions.)
  • The FROM clause can contain only one table name.
  • GROUP BY, ORDER BY or HAVING clauses are not permitted.

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:

  • The view is read-only.
  • The search condition of the view includes a subquery, or the search condition of an underlying view includes a subquery.
  • The search condition of the view includes a user-defined function that is nondeterministic or has an external action.
  • The subselect refers to a created temporary table.

Expressions

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.

Joins

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.

GROUP BY and HAVING

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

ORDER BY clauses are not permitted in the SELECT statement that defines a view.

UNION

The UNION operator cannot be used in the definition of a view.

WITH CHECK OPTION

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.

  • CASCADED

    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.

  • LOCAL

    Update and insert operations must satisfy the search conditions of the view and underlying views that are defined with a check option.

View Definitions

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:

  • The first FROM clause identifies more than one table or view, or identifies a table function
  • The first SELECT clause specifies the keyword DISTINCT
  • The outer subselect contains a GROUP BY clause
  • The outer subselect contains a HAVING clause
  • The first SELECT clause contains a column function
  • It contains a subquery such that the base object of the outer subselect, and of the subquery, is the same table
  • The first FROM clause identifies a read-only view

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.

Examples:

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
Previous Topic Next topic Print topic