Creating typed tables

Typed tables are used to actually store instances of objects whose characteristics are defined with the CREATE TYPE statement. You can create a typed table using a variant of the CREATE TABLE statement. You can also create a hierarchy of typed tables that is based on a hierarchy of structured types. To store instances of subtypes in typed tables, you must create a corresponding table hierarchy.

The figure below illustrates a typed table hierarchy. The example that follows the figure illustrates the creation of this hierarchy.

Figure 1. Typed table hierarchy

Here is the SQL to create the BusinessUnit typed table:

   CREATE TABLE BusinessUnit OF BusinessUnit_t
      (REF IS Oid USER GENERATED);

Here is the SQL to create the tables in the Person table hierarchy:

   CREATE TABLE Person OF Person_t
      (REF IS Oid USER GENERATED);

   CREATE TABLE Employee OF Employee_t UNDER Person
      INHERIT SELECT PRIVILEGES
      (SerialNum WITH OPTIONS NOT NULL,
      Dept WITH OPTIONS SCOPE BusinessUnit );

   CREATE TABLE Student OF Student_t UNDER Person
      INHERIT SELECT PRIVILEGES;

   CREATE TABLE Manager OF Manager_t UNDER Employee
      INHERIT SELECT PRIVILEGES;

   CREATE TABLE Architect OF Architect_t UNDER Employee
      INHERIT SELECT PRIVILEGES;

Defining the Type of the Table

The first typed table created in the previous example is BusinessUnit. This table is defined to be OF type BusinessUnit_t, so it will hold instances of that type. This means that it will have a column corresponding to each attribute of the structured type BusinessUnit_t, and one additional column called the object identifier column.

Naming the Object Identifier

Because typed tables contain objects that can be referenced by other objects, every typed table has an object identifier column as its first column. In this example, the type of the object identifier column is REF(BusinessUnit_t). You can name the object identifier column using the REF IS … USER GENERATED clause. In this case, the column is named Oid. The USER GENERATED part of the REF IS clause indicates that you must provide the initial value for the object identifier column of each newly inserted row. It is common practice in object-oriented design to completely separate the data from the object identifier. For that reason, you cannot update the value of the object identifier after you insert the object identifier. If you want DB2® to generate the OID values,you can use a a SEQUENCE or the GENERATE_UNIQUE() function.

Specifying the Position in the Table Hierarchy

The Person typed table is of type Person_t. To store instances of the subtypes of employees and students, it is necessary to create the subtables of the Person table, Employee and Student. The two additional subtypes of Employee_t also require tables. Those subtables are named Manager and Architect. Just as a subtype inherits the attributes of its supertype, a subtable inherits the columns of its supertable, including the object identifier column.

Rows in the Employee subtable, therefore, will have a total of seven columns: Oid, Name, Age, Address, SerialNum, Salary, and Dept.

A SELECT, UPDATE, or DELETE statement that operates on a supertable by default automatically operates on all its subtables as well. For example, an UPDATE statement on the Employee table might affect rows in the Employee, Manager, and Architect tables, but an UPDATE statement on the Manager table can only affect Manager rows.

If you want to restrict the actions of the SELECT, INSERT, or DELETE statement to just the specified table, use the ONLY option.

Indicating That SELECT Privileges Are Inherited

The mandatory INHERIT SELECT PRIVILEGES clause of the CREATE TABLE statement specifies that the resulting subtable, such as Employee, is initially accessible by the same users and groups as the supertable, such as Person, from which it is created using the UNDER clause. Any user or group currently holding SELECT privileges on the supertable is granted SELECT privileges on the newly created subtable. The creator of the subtable is the grantor of the SELECT privileges. To specify privileges such as DELETE and UPDATE on subtables, you must issue the same explicit GRANT or REVOKE statements that you use to specify privileges on regular tables.

Privileges can be granted and revoked independently at every level of a table hierarchy. If you create a subtable, you can also revoke the inherited SELECT privileges on that subtable. Revoking the inherited SELECT privileges from the subtable prevents users with SELECT privileges on the supertable from seeing any columns that appear only in the subtable. Revoking the inherited SELECT privileges from the subtable limits users who only have SELECT privileges on the supertable to seeing the supertable columns of the rows of the subtable. Users can only operate directly on a subtable if they hold the necessary privilege on that subtable. So, to prevent users from selecting the bonuses of the managers in the subtable, revoke the SELECT privilege on that table and grant it only to those users for whom this information is necessary.

Defining Column Options

The WITH OPTIONS clause lets you define options that apply to an individual column in the typed table. The format of WITH OPTIONS is:

   column-name WITH OPTIONS column-options

where column-name represents the name of the column in the CREATE TABLE or ALTER TABLE statement, and column-options represents the options defined for the column.

For example, to prevent users from inserting nulls into a SerialNum column, specify the NOT NULL column option as follows:

   (SerialNum WITH OPTIONS NOT NULL)

Defining the Scope of a Reference Column

Another use of WITH OPTIONS is to specify the SCOPE of a column. For example, in the Employee table and its subtables, the clause:

   Dept WITH OPTIONS SCOPE BusinessUnit

declares that the Dept column of this table and its subtables have a scope of BusinessUnit. This means that the reference values in this column of the Employee table are intended to refer to objects in the BusinessUnit table.

For example, the following query on the Employee table uses the dereference operator to tell DB2 to follow the path from the Dept column to the BusinessUnit table. The dereference operator returns the value of the Name column:

   SELECT Name, Salary, Dept->Name
      FROM Employee;