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 database tables, you must create a corresponding table hierarchy.
The following example illustrates creation of a table hierarchy based on the type hierarchy shown in Figure 9.
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;
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.
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. After you insert the object identifier, you cannot update the value of the object identifier. For information on configuring DB2 to automatically generate object identifiers, see Defining System-generated Object Identifiers.
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.
| Note: | A subtable must reside in the same schema as its supertable. |
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 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, described in Returning Objects of a Particular Type Using ONLY.
The 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. For more information on the INHERIT SELECT PRIVILEGES clause, refer to the SQL Reference.
Privileges may 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.
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)
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;
For more information about references and scoping references, see Using Reference Types.
After creating the structured types in the previous examples, and after creating the corresponding tables and subtables, the structure of your database looks like Figure 11:
Figure 11. Typed table hierarchy
|
When the hierarchy is established, you can use the INSERT statement, as usual, to populate the tables. The only difference is that you must remember to populate the object identifier columns and, optionally, any additional attributes of the objects in each table or subtable. Because the object identifier column is a REF type, which is strongly typed, you must cast the user-provided object identifier values, using the cast function that the system generated for you when you created the structured type.
INSERT INTO BusinessUnit (Oid, Name, Headcount)
VALUES(BusinessUnit_t(1), 'Toy', 15);
INSERT INTO BusinessUnit (Oid, Name, Headcount)
VALUES(BusinessUnit_t(2), 'Shoe', 10);
INSERT INTO Person (Oid, Name, Age)
VALUES(Person_t('a'), 'Andrew', 20);
INSERT INTO Person (Oid, Name, Age)
VALUES(Person_t('b'), 'Bob', 30);
INSERT INTO Person (Oid, Name, Age)
VALUES(Person_t('c'), 'Cathy', 25);
INSERT INTO Employee (Oid, Name, Age, SerialNum, Salary, Dept)
VALUES(Employee_t('d'), 'Dennis', 26, 105, 30000, BusinessUnit_t(1));
INSERT INTO Employee (Oid, Name, Age, SerialNum, Salary, Dept)
VALUES(Employee_t('e'), 'Eva', 31, 83, 45000, BusinessUnit_t(2));
INSERT INTO Employee (Oid, Name, Age, SerialNum, Salary, Dept)
VALUES(Employee_t('f'), 'Franky', 28, 214, 39000, BusinessUnit_t(2));
INSERT INTO Student (Oid, Name, Age, SerialNum, GPA)
VALUES(Student_t('g'), 'Gordon', 19, '10245', 4.7);
INSERT INTO Student (Oid, Name, Age, SerialNum, GPA)
VALUES(Student_t('h'), 'Helen', 20, '10357', 3.5);
INSERT INTO Manager (Oid, Name, Age, SerialNum, Salary, Dept, Bonus)
VALUES(Manager_t('i'), 'Iris', 35, 251, 55000, BusinessUnit_t(1), 12000);
INSERT INTO Manager (Oid, Name, Age, SerialNum, Salary, Dept,
Bonus)
VALUES(Manager_t('j'), 'Christina', 10, 317, 85000, BusinessUnit_t(1),
25000);
INSERT INTO Manager (Oid, Name, Age, SerialNum, Salary, Dept, Bonus)
VALUES(Manager_t('k'), 'Ken', 55, 482, 105000, BusinessUnit_t(2), 48000);
INSERT INTO Architect (Oid, Name, Age, SerialNum, Salary, Dept, StockOption)
VALUES(Architect_t('l'), 'Leo', 35, 661, 92000, BusinessUnit_t(2), 20000);
The previous example does not insert any addresses. For information about how to insert structured type values into columns, see Inserting Rows that Contain Structured Type Values.
When you insert rows into a typed table, the first value in each inserted row must be the object identifier for the data being inserted into the tables. Also, just as with non-typed tables, you must provide data for all columns that are defined as NOT NULL. Finally, notice that any reference-valued expression of the appropriate type can be used to initialize a reference attribute. In the previous examples, the Dept reference of the employees is input as an appropriately type-cast constant. However, you can also obtain the reference using a subquery, as shown in the following example:
INSERT INTO Architect (Oid, Name, Age, SerialNum, Salary, Dept, StockOption)
VALUES(Architect_t('m'), 'Brian', 7, 882, 112000,
(SELECT Oid FROM BusinessUnit WHERE name = 'Toy'), 30000);
For each structured type, DB2 supports a corresponding reference type. For example, when you create the Person_t type, DB2 automatically creates a type of REF(Person_t). The representation type of the REF(Person_t) type (and the REF types of all subtypes of Person_t) is, by default, VARCHAR (16) FOR BIT DATA, but you can choose a different representation type using the REF USING clause for the CREATE TYPE statement. That reference type is the basis of the object identifier column of the typed table that you create to store instances of the structured type. For example, if you create a root type People_t using the default representation type for the reference type, the object identifier column of the associated People table is based on VARCHAR(16) FOR BIT DATA.
Reference types are strongly typed. To compare a reference to a constant, you can cast the constant to the appropriate reference type, or you can cast the reference type to the base type, and then perform the comparison. All references in a given type hierarchy have the same reference representation type. This enables REF(S) and REF(T) to be compared, provided that S and T have a common supertype. Because uniqueness of the object identifier column is enforced only within a table hierarchy, it is possible that a value of REF(T) in one table hierarchy may be equal to a value of REF(T) in another table hierarchy, even though they reference different rows.
Using the WITH OPTIONS clause of CREATE TABLE, you can define that a relationship exists between a column in one table and the objects in the same or another table. For example, in the BusinessUnit and Person table hierarchies, the department for each employee is actually a reference to an object in the BusinessUnit table, as shown in Figure 12. To define the destination objects of a given reference column, use the SCOPE keyword on the WITH OPTIONS clause.
Figure 12. Dept attribute refers to a BusinessUnit object
 |
You can define scoped references to objects in the same typed table as well. The statements in the following example create one typed table for parts and one typed table for suppliers. To show the reference type definitions, the sample also includes the statements used to create the types:
CREATE TYPE Company_t AS
(name VARCHAR(30),
location VARCHAR(30))
MODE DB2SQL ;
CREATE TYPE Part_t AS
(Descript VARCHAR(20),
Supplied_by REF(Company_t),
Used_in REF(part_t))
MODE DB2SQL;
CREATE TABLE Suppliers OF Company_t
(REF IS suppno USER GENERATED);
CREATE TABLE Parts OF Part_t
(REF IS Partno USER GENERATED,
Supplied_by WITH OPTIONS SCOPE Suppliers,
Used_in WITH OPTIONS SCOPE Parts);
Figure 13. Example of a self-referencing scope
 |
You can use scoped references to write queries that, without scoped references, would have to be written as outer joins or correlated subqueries. For more information, see Queries that Dereference References.
Although scoped references do define relationships among objects in tables, they are different than referential integrity relationships. Scopes simply provide information about a target table. That information is used when dereferencing objects from that target table. Scoped references do not require or enforce that a value exists at the other table. For example, the Dept column in the Employee table can have a reference to a BusinessUnit object identifier column that does not exist in the BusinessUnit table. To ensure that the objects in these relationships exist, you must add a referential constraint between the tables. For more information, see Creating Constraints on Object Identifier Columns.
You can create a typed view using the CREATE VIEW statement. For example, to create a view of the typed BusinessUnit table, you can define a structured type that has the desired attributes and then create a typed view using that type:
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 valid 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 typed view on a root type, you must specify an object identifier column for the view. If you create a typed view on a subtype, your view can inherit the object identifier column.
The USER GENERATED clause specifies that the initial 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 initial 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 created earlier under Creating a Typed Table:
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 above 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. For more information on the INHERIT SELECT PRIVILEGES clause, see Indicating that SELECT Privileges are Inherited. 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.
There are several important rules associated with restrictions on the queries for typed views found in the SQL Reference that you should read carefully before attempting to create and use a typed view.
You can drop a user-defined type (UDT) or type mapping using the DROP statement. For more information on type mappings, see Working with Data Type Mappings. You cannot drop a UDT if it is used:
You cannot drop a default type mapping; you can only override it by creating another type mapping.
The database manager attempts to drop every user-defined function (UDF) that is dependent on this UDT. A UDF cannot be dropped if a view, trigger, table check constraint, or another UDF is dependent on it. If DB2 cannot drop a dependent UDF, DB2 does not drop the UDT. Dropping a UDT invalidates any packages or cached dynamic SQL statements that used it.
If you have created a transform for a UDT, and you plan to drop that UDT, consider dropping the associated transform. To drop a transform, issue a DROP TRANSFORM statement. For the complete syntax of the DROP TRANSFORM statement, refer to the SQL Reference. Note that you can only drop user-defined transforms. You cannot drop built-in transforms or their associated group definitions.
The ALTER VIEW statement modifies an existing view by altering a reference type column to add a scope. Any other changes you make to a view require that you drop and then re-create the view.
When altering the view, the scope must be added to an existing reference type column that does not already have a scope defined. Further, the column must not be inherited from a superview.
The data type of the column-name in the ALTER VIEW statement must be REF (type of the typed table name or typed view name).
Refer to the SQL Reference for additional information on the ALTER VIEW statement.
The following example shows how to drop the EMP_VIEW:
DROP VIEW EMP_VIEW;
Any views that are dependent on the dropped view become inoperative. For more information on inoperative views, refer to the "Recovering Inoperative Views" section of the Administration Guide.
Other database objects such as tables and indexes will not be affected although packages and cached dynamic statements are marked invalid. For more information, refer to the "Statement Dependencies" section of the Administration Guide.
As in the case of a table hierarchy, it is possible to drop an entire view hierarchy in one statement by naming the root view of the hierarchy, as in the following example:
DROP VIEW HIERARCHY VPerson;
For more information on dropping and creating views, refer to the SQL Reference.
If you have the required SELECT authority, you can query a typed table in the same way that you query non-typed tables. The query returns the requested columns from the qualifying rows from the target of the SELECT and all of its subtables. For example, the following query on the data in the Person table hierarchy returns the names and ages of all people; that is, all rows in the Person table and its subtables. For information on writing a similar query if one of the columns is a structured type column, see Retrieving and Modifying Structured Type Values.
SELECT Name, Age
FROM Person;
The result of the query is as follows:
NAME AGE -------------------- ----------- Andrew 29 Bob 30 Cathy 25 Dennis 26 Eva 31 Franky 28 Gordon 19 Helen 20 Iris 35 Christina 10 Ken 55 Leo 35 Brian 7 Susan 39
Whenever you have a scoped reference, you can use a dereference operation to issue queries that would otherwise require outer joins or correlated subqueries. Consider the Dept attribute of the Employee table, and subtables of Employee, which is scoped to the BusinessUnit table. The following example returns the names, salaries, and department names, or NULL values where applicable, of all the employees in the database; that means the query returns these values for every row in the Employee table and the Employee subtables. You could write a similar query using a correlated subquery or an outer join. However, it is easier to use the dereference operator (->) to traverse the path from the reference column in the Employee table and subtables to the BusinessUnit table, and to return the result from the Name column of the BusinessUnit table.
The simple format of the dereference operation is as follows:
scoped-reference-expression -> column-in-target-typed-table
The following query uses the dereference operator to obtain the Name column from the BusinessUnit table:
SELECT Name, Salary, Dept->Name
FROM Employee
The result of the query is as follows:
NAME SALARY NAME -------------------- ----------- -------------------- Dennis 30000 Toy Eva 45000 Shoe Franky 39000 Shoe Iris 55000 Toy Christina 85000 Toy Ken 105000 Shoe Leo 92000 Shoe Brian 112000 Toy Susan 37000.48 ---
You can dereference self-referencing references as well. Consider the Parts table defined in Figure 13. The following query lists the parts directly used in a wing with the locations of the suppliers of the parts:
SELECT P.Descript, P.Supplied_by ->Location
FROM Parts P
WHERE P.Used_in -> Descript='Wing';
You can also dereference references to obtain entire structured objects as a single value by using the DEREF built-in function. The simple form of DEREF is as follows:
DEREF (scoped-reference-expression)
DEREF is usually used in the context of other built-in functions, such as TYPE_NAME, or to obtain a whole structured object for the purposes of binding out to an application.
The DEREF function is often invoked as part of the TYPE_NAME, TYPE_ID, or TYPE_SCHEMA built-in functions. The purpose of these functions, respectively, is to return the name, internal ID, and schema name of the dynamic type of an expression. For example, the following example creates a Project typed table with an attribute called Responsible:
CREATE TYPE Project_t
AS (Projid INT, Responsible REF(Employee_t))
MODE DB2SQL;
CREATE TABLE Project
OF Project_t (REF IS Oid USER GENERATED,
Responsible WITH OPTIONS SCOPE Employee);
The Responsible attribute is defined as a reference to the Employee table, so that it can refer to instances of managers and architects as well as employees. If your application needs to know the name of the dynamic type of every row, you can use a query like the following:
SELECT Projid, Responsible->Name,
TYPE_NAME(DEREF(Responsible))
FROM PROJECT;
The preceding example uses the dereference operator to return the value of Name from the Employee table, and invokes the DEREF function to return the dynamic type for the instance of Employee_t.
For more information about the built-in functions described in this section, refer to the SQL Reference.
Authorization requirement: To use the DEREF function, you must have SELECT authority on every table and subtable in the referenced portion of the table hierarchy. In the above query, for example, you need SELECT privileges on the Employee, Manager, and Architect typed tables.
To have a query return only objects of a particular type, and not of its subtypes, use the ONLY keyword. For example, the following query returns only the names of employees that are not architects or managers:
SELECT Name FROM ONLY(Employee);
The previous query returns the following result:
NAME -------------------- Dennis Eva Franky Susan
To protect the security of the data, the use of ONLY requires the SELECT privilege on every subtable of Employee.
You can also use the ONLY clause to restrict the operation of an UPDATE or DELETE statement to the named table. That is, the ONLY clause ensures that the operation does not occur on any subtables of that named table.
If you want a more general way to restrict what rows are returned or affected by an SQL statement, you can use the type predicate. The type predicate enables you to compare the dynamic type of an expression to one or more named types. A simple version of the type predicate is:
<expression> IS OF (<type_name>[, ...])
where expression represents an SQL expression that returns an instance of a structured type, and type_name represents one or more structured types with which the instance is compared.
For example, the following query returns people who are greater than 35 years old, and who are either managers or architects:
SELECT Name
FROM Employee E
WHERE E.Age > 35 AND
DEREF(E.Oid) IS OF (Manager_t, Architect_t);
The previous query returns the following result:
NAME -------------------- Ken
When DB2 returns a structured type row value, the application does not necessarily know which attributes that particular instance contains or can contain. For example, when you return a person, that person might just have the attributes of a person, or it might have attributes of an employee, manager, or other subtype of person. If your application needs to obtain the values of all possible attributes within one SQL query, you can use the keyword OUTER in the table reference.
OUTER (table-name) and OUTER(view-name) return a virtual table that consists of the columns of the table or view followed by the additional columns introduced by each of its subtables, if any. The additional columns are added on the right hand side of the table, traversing the subtable hierarchy in the order of depth. Subtables that have a common parent are traversed in the order in which their respective types were created. The rows include all the rows of table-name and all of the additional rows of the subtables of table-name. Null values are returned for columns that are not in the subtable for the row.
You might use OUTER, for example, when you want to see information about people who tend to achieve above the norm. The following query returns information from the Person table hierarchy that have either a high salary Salary or a high grade point average GPA:
SELECT *
FROM OUTER(Person) P
WHERE P.Salary > 200000
OR P.GPA > 3.95 ;
Using OUTER(Person) enables you to refer to subtype attributes, which is not otherwise possible in Person queries.
The use of OUTER requires the SELECT privilege on every subtable or view of the referenced table because all of their information is exposed through its usage.
Suppose that your application needs to see not just the attributes of these high achievers, but what the most specific type is for each one. You can do this in a single query by passing the object identifier of an object to the TYPE_NAME built-in function and combining it with an OUTER query, as follows:
SELECT TYPE_NAME(DEREF(P.Oid)), P.*
FROM OUTER(Person) P
WHERE P.Salary > 200000 OR
P.GPA > 3.95 ;
Because the Address column of the Person typed table contains structured types, you would have to define additional functions and issue additional SQL to return the data from that column. For more information on returning data from a structured type column, see Retrieving and Modifying Structured Type Values. Assuming you perform these additional steps, the preceding query returns the following output, where Additional Attributes includes GPA and Salary:
1 OID NAME Additional Attributes ------------------ ------------- -------------------- ... PERSON_T a Andrew ... PERSON_T b Bob ... PERSON_T c Cathy ... EMPLOYEE_T d Dennis ... EMPLOYEE_T e Eva ... EMPLOYEE_T f Franky ... MANAGER_T i Iris ... ARCHITECT_T l Leo ... EMPLOYEE_T s Susan ...
To have DB2 automatically generate unique object identifiers, you can use the GENERATE_UNIQUE function. Because GENERATE_UNIQUE returns a CHAR (13) FOR BIT DATA value, ensure that your REF USING clause on the CREATE TYPE statement can accommodate a value of that type. The default of VARCHAR (16) FOR BIT DATA is suitable for this purpose. For example, assume that the BusinessUnit_t type is created with the default representation type; that is, no REF USING clause is specified, as follows:
CREATE TYPE BusinessUnit_t AS
(Name VARCHAR(20),
Headcount INT)
MODE DB2SQL;
The typed table definition is as follows:
CREATE TABLE BusinessUnit OF BusinessUnit_t (REF IS Oid USER GENERATED);
Note that you must always provide the clause USER GENERATED.
An INSERT statement to insert a row into the typed table, then, might look like this:
INSERT INTO BusinessUnit (Oid, Name, Headcount)
VALUES(BusinessUnit_t(GENERATE_UNIQUE( )), 'Toy' 15);
To insert an employee that belongs to the Toy department, you can use a statement like the following, which issues a subselect to retrieve the value of the object identifier column from the BusinessUnit table, casts the value to the BusinessUnit_t type, and inserts that value into the Dept column:
INSERT INTO Employee (Oid, Name, Age, SerialNum, Salary, Dept)
VALUES(Employee_t('d'), 'Dennis', 26, 105, 30000,
BusinessUnit_t(SELECT Oid FROM BusinessUnit WHERE Name='Toy'));
If you want to use the object identifier column as a key column of the parent table in a foreign key, you must first alter the typed table to add an explicit unique or primary key constraint on the object identifier column. For example, assume that you want to create a self-referencing relationship on employees in which the manager of each employee must always exist as an employee in the employee table, as shown in Figure 14.
Figure 14. Self-referencing type example
 |
To create a self-referencing relationship, perform the following steps:
Step 1. | Create the type CREATE TYPE Empl_t AS
(Name VARCHAR(10), Mgr REF(Empl_t))
MODE DB2SQL;
|
Step 2. | Create the typed table CREATE TABLE Empl OF Empl_t
(REF IS Oid USER GENERATED);
|
Step 3. | Add the primary or unique constraint on the Oid column: ALTER TABLE Empl ADD CONSTRAINT pk1 UNIQUE(Oid); |
Step 4. | Add the foreign key constraint. ALTER TABLE Empl ADD CONSTRAINT fk1 FOREIGN KEY(Mgr)
REFERENCES Empl (Oid);
|