Creating typed views

   CREATE TYPE VBusinessUnit_t AS (Name VARCHAR(20))
      MODE DB2SQL;

   CREATE VIEW VBusinessUnit OF VBusinessUnit_t MODE DB2SQL
      (REF IS VObjectID USER GENERATED)
      AS SELECT VBusinessUnit_t(VARCHAR(Oid)), Name FROM BusinessUnit;

The OF clause in the CREATE VIEW statement tells Db2® to base the columns of the view on the attributes of the indicated structured type. In this case, Db2 bases the columns of the view on the VBusinessUnit_t structured type.

The VObjectID column of the view has a type of REF(VBusinessUnit_t). Since you cannot cast from a type of REF(BusinessUnit_t) to REF(VBusinessUnit_t), you must first cast the value of the Oid column from table BusinessUnit to data type VARCHAR, and then cast from data type VARCHAR to data type REF(VBusinessUnit_t).

The MODE DB2SQL clause specifies the mode of the typed view. This is the only mode currently supported.

The REF IS... clause is identical to that of the typed CREATE TABLE statement. It provides a name for the object identifier column of the view (VObjectID in this case), which is the first column of the view. If you create a root view, you must specify an object identifier column for the view. If you create a subview, it inherits the object identifier column.

The USER GENERATED clause specifies that the value for the object identifier column must be provided by the user when inserting a row. Once inserted, the object identifier column cannot be updated.

The body of the view, which follows the keyword AS, is a SELECT statement that determines the content of the view. The column types returned by this SELECT statement must be compatible with the column types of the typed view, including the object identifier column.

To illustrate the creation of a typed view hierarchy, the following example defines a view hierarchy that omits some sensitive data and eliminates some type distinctions from the Person table hierarchy:

   CREATE TYPE VPerson_t AS (Name VARCHAR(20))
      MODE DB2SQL;

   CREATE TYPE VEmployee_t UNDER VPerson_t
      AS (Salary INT, Dept REF(VBusinessUnit_t))
      MODE DB2SQL;

   CREATE VIEW VPerson OF VPerson_t MODE DB2SQL
      (REF IS VObjectID USER GENERATED)
      AS SELECT VPerson_t (VARCHAR(Oid)), Name FROM ONLY(Person);

   CREATE VIEW VEmployee OF VEmployee_t MODE DB2SQL
      UNDER VPerson INHERIT SELECT PRIVILEGES
      (Dept WITH OPTIONS SCOPE VBusinessUnit)
      AS SELECT VEmployee_t(VARCHAR(Oid)), Name, Salary,
         VBusinessUnit_t(VARCHAR(Dept))
      FROM Employee;

The two CREATE TYPE statements create the structured types that are needed to create the object view hierarchy for this example.

The first typed CREATE VIEW statement in the previous example, creates the root view of the hierarchy, VPerson, and is very similar to the VBusinessUnit view definition. The difference is the use of ONLY(Person) to ensure that only the rows in the Person table hierarchy that are in the Person table, and not in any subtable, are included in the VPerson view. This ensures that the Oid values in VPerson are unique compared with the Oid values in VEmployee. The second CREATE VIEW statement creates a subview VEmployee under the view VPerson. As was the case for the UNDER clause in the CREATE TABLE...UNDER statement, the UNDER clause establishes the view hierarchy. You must create a subview in the same schema as its superview. Like typed tables, subviews inherit columns from their superview. Rows in the VEmployee view inherit the columns VObjectID and Name from VPerson and have the additional columns Salary and Dept associated with the type VEmployee_t.

The INHERIT SELECT PRIVILEGES clause has the same effect when you issue a CREATE VIEW statement as when you issue a typed CREATE TABLE statement. The WITH OPTIONS clause in a typed view definition also has the same effect as it does in a typed table definition. The WITH OPTIONS clause enables you to specify column options such as SCOPE. The READ ONLY clause forces a superview column to be marked as read-only, so that subsequent subview definitions can specify an expression for the same column that is also read-only.

If a view has a reference column, like the Dept column of the VEmployee view, you must associate a scope with the column to use the column in SQL dereference operations. If you do not specify a scope for the reference column of the view and the underlying table or view column is scoped, then the scope of the underlying column is passed on to the reference column of the view. You can explicitly assign a scope to the reference column of the view by using the WITH OPTIONS clause. In the previous example, the Dept column of the VEmployee view receives the VBusinessUnit view as its scope. If the underlying table or view column does not have a scope, and no scope is explicitly assigned in the view definition, or no scope is assigned with an ALTER VIEW statement, the reference column remains unscoped.