Application Development Guide

Structured Types Overview

Structured types are useful for modelling objects that have a well-defined structure consisting of attributes. Attributes are properties that describe an instance of a type. A geometric shape, for example, might have as attributes its list of Cartesian coordinates. A person might have attributes of name, address, and so on. A department might have a name or some other kind of ID.

To create a type, you must specify the name of the type, its attribute names and their data types, and, optionally, how you want the reference type for this type to be represented in the system. Here is the SQL to create the BusinessUnit_t type:

   CREATE TYPE BusinessUnit_t AS
     (Name VARCHAR(20),
     Headcount INT)
     REF USING INT
     MODE DB2SQL;

The AS clause provides the attribute definitions associated with the type. BusinessUnit_t is a type with two attributes: Name and Headcount. To create a structured type, you must include the MODE DB2SQL clause in the CREATE TYPE statement. For more information on the REF USING clause, see Reference Types and Their Representation Types.

Structured types offer two major extensions beyond traditional relational data types: the property of inheritance, and the capability of storing instances of a structured type either as rows in a table, or as values in a column. The following section briefly describes these features:

Inheritance

It is certainly possible to model objects such as people using traditional relational tables and columns. However, structured types offer an additional property of inheritance. That is, a structured type can have subtypes that reuse all of its attributes and contain additional attributes specific to the subtype. For example, the structured type Person_t might contain attributes for Name, Age, and Address. A subtype of Person_t might be Employee_t, that contains all of the attributes Name, Age, and Address and in addition contains attributes for SerialNum, Salary, and BusinessUnit.

Figure 7. Structured type Employee_t inherits from Person_t

Storing instances of structured type

A structured type instance can be stored in the database in two ways:

As a row in a table, in which each column of the table is an attribute of the instance of the type. To store objects as rows in a table, the table is defined with the structured type, rather than by specifying individual columns in the table definition:
```
   CREATE TABLE Person OF Person_t
      ...
```
Each column in the table derives its name and data type from one of the attributes of the indicated structured type. Such tables are known as typed tables.
As a value in a column. To store objects in table columns, the column is defined using the structured type as its type. The following statement creates a Properties table that has a structured type Address that is of the Address_t structured type:
```
   CREATE TABLE Properties
      (ParcelNum INT,
      Photo BLOB(2K),
      Address Address_t)
      ...
```

Creating a Structured Type Hierarchy

A structured type may be created under another structured type, in which case the newly created type is a subtype of the original structured type. The original type is the supertype. The subtype inherits all the attributes of the supertype, and can optionally have additional attributes of its own.

For example, a data model may need to represent a special type of employee called a manager. Managers have more attributes than employees who are not managers. The Manager_t type inherits the attributes defined for an employee, but also is defined with some additional attributes of its own, such as a special bonus attribute that is only available to managers. The type hierarchies used for examples in this book are shown in Figure 8. The type hierarchy for Address_t is defined in Inserting Structured Type Instances into a Column.

Figure 8. Type hierarchies (BusinessUnit_t and Person_t)

In Figure 8, the person type Person_t is the root type of the hierarchy. Person_t is also the supertype of the types below it--in this case, the type named Employee_t and the type named Student_t. The relationships among subtypes and supertypes are transitive; in other words, the relationship between subtype and supertype exists throughout the entire type hierarchy. So, Person_t is also a supertype of types Manager_t and Architect_t.

Type BusinessUnit_t, defined in Structured Types Overview, has no subtypes. Type Address_t, defined in Inserting Structured Type Instances into a Column, has the following subtypes: Germany_addr_t, Brazil_addr_t, and US_addr_t.

The CREATE TYPE statement for type Person_t declares that Person_t is INSTANTIABLE. For more information on declaring structured types using the INSTANTIABLE or NOT INSTANTIABLE clauses, see Additional Properties of Structured Types.

The following SQL statements create the Person_t type hierarchy:

   CREATE TYPE Person_t AS 
      (Name VARCHAR(20), 
      Age INT,
      Address Address_t)
      INSTANTIABLE
      REF USING VARCHAR(13) FOR BIT DATA
      MODE DB2SQL;
 
   CREATE TYPE Employee_t UNDER Person_t AS
      (SerialNum INT, 
      Salary DECIMAL (9,2), 
      Dept REF(BusinessUnit_t))
      MODE DB2SQL;
 
   CREATE TYPE Student_t UNDER Person_t AS 
      (SerialNum CHAR(6), 
      GPA DOUBLE)
      MODE DB2SQL;
 
   CREATE TYPE Manager_t UNDER Employee_t AS 
      (Bonus DECIMAL (7,2))
      MODE DB2SQL;
 
   CREATE TYPE Architect_t UNDER Employee_t AS 
      (StockOption INTEGER)
      MODE DB2SQL;

Person_t has three attributes: Name, Age and Address. Its two subtypes, Employee_t and Student_t, each inherit the attributes of Person_t and also have several additional attributes that are specific to their particular types. For example, although both employees and students have serial numbers, the format used for student serial numbers is different from the format used for employee serial numbers.
Note: A typed table created from the Person_t type includes the column Address of structured type Address_t. As with any structured type column, you must define transform functions for the structured type of that column. For information on defining transform functions, see Creating the Mapping to the Host Language Program: Transform Functions.

Finally, Manager_t and Architect_t are both subtypes of Employee_t; they inherit all the attributes of Employee_t and extend them further as appropriate for their types. Thus, an instance of type Manager_t will have a total of seven attributes: Name, Age, Address, SerialNum, Salary, Dept, and Bonus.

Reference Types and Their Representation Types

For every structured type you create, DB2 automatically creates a companion type. The companion type is called a reference type and the structured type to which it refers is called a referenced type. Typed tables can make special use of the reference type, as described in Using Structured Types in Typed Tables. You can also use reference types in SQL statements like other user-defined types. To use a reference type in an SQL statement, use REF(type-name), where type-name represents the referenced type.

DB2 uses the reference type as the type of the object identifier column in typed tables. The object identifier uniquely identifies a row object in the typed table hierarchy. DB2 also uses reference types to store references to rows in typed tables. You can use reference types to refer to each row object in the table. For more information about using references, see Using Reference Types. For more information on typed tables, see Storing Objects in Typed Tables.

References are strongly typed. Therefore, you must have a way to use the type in expressions. When you create the root type of a type hierarchy, you can specify the base type for a reference with the REF USING clause of the CREATE TYPE statement. The base type for a reference is called the representation type. If you do not specify the representation type with the REF USING clause, DB2 uses the default data type of VARCHAR(16) FOR BIT DATA. The representation type of the root type is inherited by all its subtypes. The REF USING clause is only valid when you define the root type of a hierarchy. In the examples used throughout this section, the representation type for the BusinessUnit_t type is INTEGER, while the representation type for Person_t is VARCHAR(13).

Casting and Comparing Reference Types

DB2 automatically creates functions that cast values between the reference type and its representation type, in both directions. The CREATE TYPE statement has an optional CAST WITH clause, described in the SQL Reference, that allows you to choose the names of these two cast functions. By default, the names of the cast functions are the same as the names of the structured type and its reference representation type. For example, the CREATE TYPE Person_t statement from Creating a Structured Type Hierarchy automatically creates the following functions:

   CREATE FUNCTION VARCHAR(REF(Person_t))
      RETURNS VARCHAR

DB2 also creates the function that does the inverse operation:

   CREATE FUNCTION Person_t(VARCHAR(13))
      RETURNS REF(Person_t)

You will use these cast functions whenever you need to insert a new value into the typed table or when you want to compare a reference value to another value.

DB2 also creates functions that let you compare reference types using the following comparison operators: =, <>, <, <=, >, and >=. For more information on comparison operators for reference types, refer to the SQL Reference.

Other System-Generated Routines

Every structured type that you create causes DB2 to implicitly create a set of functions and methods that you can use to construct, observe, or modify a structured type value. This means, for instance, that for type Person_t, DB2 automatically creates the following functions and methods when you create the type:

Constructor function

A function of the same name as the type is created. This function has no parameters and returns an instance of the type with all of its attributes set to null. The function that is created for Person_t, for example, is as if the following statement were executed:

   CREATE FUNCTION Person_t ( ) RETURNS Person_t

For the subtype Manager_t, a constructor function is created as if the following statement had been executed:

   CREATE FUNCTION Manager_t ( ) RETURNS Manager_t

To construct an instance of a type to insert into a column, use the constructor function with the mutator methods. If the type is stored in a table, rather than a column, you do not have to use the constructor function with the mutator methods to insert an instance of a type. For more information on inserting data into typed tables, see Inserting Rows that Contain Structured Type Values.

Mutator methods

A mutator method exists for each attribute of an object. The instance of a type on which a method is invoked is called the subject instance of the method. When the mutator method invoked on a subject instance receives a new value for an attribute, the method returns a new instance with the attribute updated to the new value. So, for type Person_t, DB2 creates mutator methods for each of the following attributes: name, age, and address.

The mutator method DB2 creates for attribute age, for example, is as if the following statement had been executed:

   ALTER TYPE Person_t 
      ADD METHOD AGE(int)
      RETURNS Person_t;

For more information on mutating objects, see Retrieving and Modifying Structured Type Values.

Observer methods

An observer method exists for each attribute of an object. If the method for an attribute receives an object of the expected type or subtype, the method returns the value of the attribute for that object.

The observer method DB2 creates for the attribute age of the type Person_t, for example, is as if DB2 issued the following statement:

   ALTER TYPE Person_t
      ADD METHOD AGE()
      RETURNS INTEGER;

For more information about using observer methods, see Retrieving and Modifying Structured Type Values.

To invoke a method on a structured type, use the method invocation operator: '..'. For more information about method invocation, refer to the SQL Reference.

Defining Behavior for Types

To define behaviors for structured types, you can create user-defined methods. You cannot create methods for distinct types. Creating a method is similar to creating a function, with the exception that methods are created specifically for a type, so that the type and its behavior are tightly integrated.

The method specification must be associated with the type before you issue the CREATE METHOD statement. The following statement adds the method specification for a method called calc_bonus to the Employee_t type:

   ALTER TYPE Employee_t
      ADD METHOD calc_bonus (rate DOUBLE)
      RETURNS DECIMAL(7,2)
      LANGUAGE SQL
      CONTAINS SQL 
      NO EXTERNAL ACTION
      DETERMINISTIC;

Once you have associated the method specification with the type, you can define the behavior for the type by creating the method as either an external method or an SQL-bodied method, according to the method specification. For example, the following statement registers an SQL method called calc_bonus that resides in the same schema as the type Employee_t:

   CREATE METHOD calc_bonus (rate DOUBLE)
      FOR Employee_t
      RETURN SELF..salary * rate;

You can create as many methods named calc_bonus as you like, as long as they have different numbers or types of parameters, or are defined for types in different type hierarchies. In other words, you cannot create another method named calc_bonus for Architect_t that has the same parameter types and same number of parameters.
Note: DB2 does not currently support dynamic dispatch. This means that you cannot declare a method for a type, and then redefine the method for a subtype using the same number of parameters. As a workaround, you can use the TYPE predicate to determine the dynamic type and then use the TREAT AS clause to call a different method for each dynamic type. For an example of transform functions that handle subtypes, see Retrieving Subtype Data from DB2 (Bind Out).

For more information about registering, writing, and invoking methods, see User-Defined Functions (UDFs) and Methods and Writing User-Defined Functions (UDFs) and Methods.

Storing Objects in Typed Tables

You can store instances of structured types either as rows in typed tables, in which each attribute of the type is stored in a separate column, or as objects in columns, in which all of the attributes of the type are stored in a single column. Typed tables have the attribute of identity; that is, another table can use references to access attributes of instances. If you need to refer to your instance from other tables, you must use typed tables. If your objects do not need to be identified by other tables, consider storing the objects in columns.

When objects are stored as rows in a table, each column of the table contains one attribute of the object. You could store an instance of a person, for example, in a table that contains a column for name and a column for age. Here is an example of a CREATE TABLE statement for storing instances of Person.

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

To insert an instance of Person into the table, you could use the following syntax:

   INSERT INTO Person (Oid, Name, Age)
      VALUES(Person_t('a'), 'Andrew', 29);

Table 10. Person typed table

Oid Name Age Address
a Andrew 29

Oid	Name	Age	Address
a	Andrew	29

Your program accesses attributes of the object by accessing the columns of the typed table:

UPDATE Person SET Age=30 WHERE Name='Andrew';

After the previous UPDATE statement, the table looks like:

Table 11. Person typed table after update

Oid Name Age Address
a Andrew 30

Oid	Name	Age	Address
a	Andrew	30

Because there is a subtype of Person_t called Employee_t, instances of Employee_t cannot be stored in the Person table, and need to be stored in another table. This table is called a subtable. The following CREATE TABLE statement creates the Employee subtable under the Person table:

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

And, again, an insert into the Employee table looks like this:

   INSERT INTO Employee (Oid, Name, Age, SerialNum, Salary)
      VALUES (Employee_t('s'), 'Susan', 39, 24001, 37000.48)

Table 12. Employer typed subtable

Oid Name Age Address SerialNum Salary Dept
s Susan 39 24001 37000.48

Oid	Name	Age	Address	SerialNum	Salary	Dept
s	Susan	39		24001	37000.48

If you execute the following query, the information for Susan is returned:

   SELECT *
      FROM Employee
      WHERE Name='Susan';

The interesting thing about these two tables is that you can access instances of both employees and people just by executing your SQL statement on the Person table. This feature is called substitutability, and is discussed in Additional Properties of Structured Types. By executing a query on the table that contains instances that are higher in the type hierarchy, you automatically get instances of types that are lower in the hierarchy. In other words, the Person table logically looks like this to SELECT, UPDATE, and DELETE statements :

Table 13. Person table contains Person and Employee instances

Oid Name Age Address
a Andrew 30 (null)
s Susan 39 (null)

Oid	Name	Age	Address
a	Andrew	30	(null)
s	Susan	39	(null)

If you execute the following query, you get an object identifier and Person_t information about both Andrew (a person) and Susan (an employee):

   SELECT *
      FROM Person;

For more information on substitutability, see Additional Properties of Structured Types.

Defining Relationships Between Objects in Typed Tables

You can define relationships between objects in one typed table and objects in another table. You can also define relationships between objects in the same typed table. For example, assume that you have defined a typed table that contains instances of departments. Instead of maintaining department numbers in the Employee table, the Dept column of the Employee table can contain a logical pointer to one of the departments in the BusinessUnit table. These pointers are called references, and are illustrated in Figure 9.

Figure 9. Structured type references from Employee_t to BusinessUnit_t

Important: References do not perform the same function as referential constraints. It is possible to have a reference to a department that does not exist. If it is important to maintain integrity between department and employees, you can define a referential constraint between those two tables. The real power of references is that it gives you the ability to write queries that navigate the relationship between the tables. What the query does is dereference the relationship and instantiate the object that is being pointed to. The operator that you use to perform this action is called the dereference operator, which looks like this: ->.

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 on writing queries on typed tables, see Querying a Typed Table.

Storing Objects in Columns

Storing objects in columns is useful when you need to model facts about your business objects that cannot be adequately modelled with the DB2 built-in data types. In other words, you may store your business objects (such as employees, departments, and so on) in typed tables, but those objects might also have attributes that are best modelled using a structured type.

For example, assume that your application has the need to access certain parts of an address. Rather than store the address as an unstructured character string, you can store it as a structured object as shown in Figure 10.

Figure 10. Address attribute as a structured type

Furthermore, you can define a type hierarchy of addresses to model different formats of addresses that are used in different countries. For example, you might want to include both a US address type, which contains a zip code, and a Brazilian address type, for which the neighborhood attribute is required. The Address_t type hierarchy is defined in Inserting Structured Type Instances into a Column.

When objects are stored as column values, the attributes are not externally represented as they are with objects stored in rows of tables. Instead, you must use methods to manipulate their attributes. DB2 generates both observer methods to return attributes, and mutator methods to change attributes. The following examples uses one observer method and two mutator methods, one for the Number attribute and one for the Street attribut, to change an address:

   UPDATE Employee
      SET Address=Address..Number('4869')..Street('Appletree')
      WHERE Name='Franky'
      AND Address..State='CA';

In the preceding example, the SET clause of the UPDATE statement invokes the Number and Street mutator methods to update attributes of the instances of type Address_t. The WHERE clause restricts the operation of the update statement with two predicates: an equality comparison for the Name column, and an equality comparison that invokes the State observer method of the Address column.

Additional Properties of Structured Types

Substitutability

When a SELECT, UPDATE, or DELETE statement is applied to a typed table, the operation applies to the named table and all of its subtables. For example, if you create a typed table from Person_t and select all rows from that table, your application can receive not just instances of the Person type, but Person information about instances of the Employee subtype and other subtypes. The property of substitutability also applies to subtables created from subtypes. For example, SELECT, UPDATE, and DELETE statements for the Employee subtable apply to both the Employee_t type and its own subtypes.

Similarly, a column defined with Address_t type can contain instances of a US address or a Brazilian address.

INSERT operations, in contrast, only apply to the table that is specified in the INSERT statement. Inserting into the Employee table creates an Employee_t object in the Person table hierarchy.

You can also substitute subtype instances when you pass structured types as parameters to functions, or as the result from a function. If a scalar function has a parameter of type Address_t, you can pass an instance of one of its subtypes, such as US_addr_t, instead of an instance of Address_t. Table functions cannot return structured type columns.

Because a column or table is defined with one type but might contain instances of other types, it is sometimes important to distinguish between the type that was used for the definition and the type of the instance that is actually returned at runtime. The definition of the structured type in a column, row, or function parameter is called the static type. The actual type of a structured type instance is called the dynamic type.To retrieve information about the dynamic type, your application can use the TYPE_NAME, TYPE_SCHEMA, and TYPE_ID built-in functions that are described in Other Type-related Built-in Functions.

Instantiability

Types can also be defined to be INSTANTIABLE or NOT INSTANTIABLE. By default, types are instantiable, which means that an instance of that object can be created. Noninstantiable types, on the other hand, serve as models intended for further refinement in the type hierarchy. For example, if you define Person_t using the NOT INSTANTIABLE clause, then you cannot store any instances of a person in the database, and you cannot create a table or view using Person_t. Instead, you can only store instances of Employee_t or other subtypes of Person_t that you define.

[ Top of Page | Previous Page | Next Page ]