DB2 10.5 for Linux, UNIX, and Windows

Storing structured type objects in table columns

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

About this task

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 1.

Figure 1. 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 or regions. 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.

Figure 2 shows a hierarchy for the different types of addresses. The root type is Address_t, which has three subtypes, each with an additional attribute that reflects some aspect of how addresses are formed in that region.

Figure 2. Structured type hierarchy for Address_t type

   CREATE TYPE Address_t AS
      (street VARCHAR(30),
      number CHAR(15),
      city VARCHAR(30),
      state VARCHAR(10))
      MODE DB2SQL;

   CREATE TYPE Germany_addr_t UNDER Address_t AS
      (family_name VARCHAR(30))
      MODE DB2SQL;

   CREATE TYPE Brazil_addr_t UNDER Address_t AS
      (neighborhood VARCHAR(30))
      MODE DB2SQL;

   CREATE TYPE US_addr_t UNDER Address_t AS
      (zip CHAR(10))
      MODE DB2SQL;

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 example uses one observer method and two mutator methods, one for the Number attribute and one for the Street attribute, 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.

To allow for updating of more complex, especially nested, instances of structured types, DB2 also allows you to drill down to the attribute to be updated on the left side of the SET clause:

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

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.