IBM® DB2® Universal Database™ (DB2 UDB) for Linux™, UNIX®, and Windows® supports structured data types. A structured data type is a form of user-defined data type that contains a sequence of attributes, each of which has a data type. An attribute is a property that helps describe an instance of the type. For example, if we were to define a structured type called address_t, city might be one of the attributes of this structured type. Structured types make it easy to use data, such as an address, either as a single unit, or as separate data items, without having to store each of those items (or attributes) in a separate column.

Most commonly, structured types are stored in a database:

• As values in one or more columns, which are defined using the structured types as their data types. (See Listing 1 and Listing 2.)
• As rows in a table (a typed table) whose columns are defined by the attributes of the structured type. In this case, the table is created with the structured type, and you don't specify individual columns in the table definition. (See Listing 3.)

Structured data types

A structured data type can be used as the type for a column in a regular table (Listings 1 and 2), the type for an entire table (or view), or as an attribute of another structured type (Listing 3). When used as the type for a table, the table is known as a typed table (Listing 3).

You can create a table with structured type columns in the same way that you would create a table using only DB2 built-in data types. In both cases, you must assign a data type to every column in the table. If the column is a structured type column, you specify the name of the structured type as its data type (Listing 1).

Structured data types exhibit a behavior known as inheritance. A structured type can have subtypes, other structured types that reuse all of its attributes and contain their own specific attributes. The type from which a subtype inherits attributes is known as its supertype. A type hierarchy is a set of subtypes that are based on the same supertype; the pre-eminent supertype in a hierarchy is known as the root type of the hierarchy.

Use the CREATE TYPE statement to create a structured type, and use the DROP statement to delete a structured type.

When you create a structured type, DB2 automatically creates a constructor function for the type and creates both mutator and observer methods for the attributes of the type. You can use the constructor function and the mutator method to create instances of the structured type, and then you can insert these instances into the column of a table.

• The constructor function has the same name as the structured type with which it is associated. The constructor function has no parameters and returns an instance of the type with all of its attributes set to null values.
• A mutator method exists for each attribute of a structured type. When you invoke a mutator method on an instance of a structured type and specify a new value for its associated attribute, the method returns a new instance with the attribute updated to the new value.
• An observer method exists for each attribute of a structured type. When you invoke an observer method on an instance of a structured type, the method returns the value of the attribute for that instance.

To invoke a mutator or observer method on an instance of a structured type, use the double-dot (..) operator (Listings 1, 2, and 3).

Back to top

Typed tables

A typed table is a table that is defined with a user-defined structured type. Typed tables store instances of structured types as rows, in which each attribute of a type is stored in a separate column; in fact, the names and data types of the attributes of the structured type become the names and data types of the columns of the typed table. Similarly to structured types, typed tables can be part of a table hierarchy consisting of a single root table, supertables, and subtables.

Use the CREATE TABLE statement to create a typed table, and use the DROP statement to delete a typed table. The typed table that is being dropped cannot have any subtables. You can also drop an entire table hierarchy by specifying the HIERARCHY keyword in the DROP statement (DROP TABLE HIERARCHY <root-table-name>).

Back to top

A detailed example

For this example, we will use the SAMPLE database that comes with DB2 UDB. Our example requires a database connection to start, nothing else, and for this, the SAMPLE database will do quite nicely.

We will create a table named CLIENTS with four columns. CLIENT_ID is a system-generated identity column; CLIENT_LNAME and CLIENT_FNAME contain a client's last and first name, respectively; and ADDRESS is a structured type column of type Address_t. (A useful convention is to name structured types with the '_t' suffix to make their identity as structured types obvious.) We will need to create this structured type before we attempt to create the CLIENTS table (Listing 1). We will use the simplest form of the CREATE TYPE statement, specifying only four attributes (street, city, province and postal_code) and the required MODE DB2_SQL clause.

To retrieve structured type data from a table, there must be some way to convert that type into a single scalar value whose type, in turn, must be based on one of the built-in DB2 data types. To accomplish this conversion, we first have to create a FROM SQL transform function and then associate that transform function with a transform group.

First, we will create a scalar transform function named ADDRESS_TRANSFORM, using the CREATE FUNCTION (SQL Scalar, Table, or Row) statement. In this example, we specify an input parameter named addr of type Address_t. We also specify that the function will return a VARCHAR(42), which is large enough to hold the concatenated address attributes. The SQL-function-body consists of a RETURN statement, in which the address attributes for an instance of the structured type Address_t are retrieved through their observer methods (specified by the '..' operator) and concatenated (the '||' operator) into a single string to form a mailing address.

Before we can use this transform function, we must use the CREATE TRANSFORM statement to associate the ADDRESS_TRANSFORM transform function with a group name and a type. The CREATE TRANSFORM statement enables an existing function to be used as a transform function. If you do not specify a group name when you run an application that references a structured type, DB2 tries to use a group name called DB2_PROGRAM and assumes that this group name was defined for the structured type. If DB2_PROGRAM has not already been defined for the structured type, you can create that group for your structured type.

We will issue the CREATE TRANSFORM statement, specifying the Address_t structured type and the DB2_PROGRAM group name. The FROM SQL clause defines the specific function that will be used to transform a value to a built-in data type value representing the structured type. The WITH FUNCTION clause specifies the transform function (in this case, ADDRESS_TRANSFORM).

We are now ready to insert some values into the CLIENTS table. The VALUES clause of the INSERT statement includes a call to address_t(), which invokes the constructor function for the address_t structured type to create an instance of the type with all attributes set to null values. The double-dot operator invokes mutator methods to set values for each of the address attributes.

A subsequent query specifying the ADDRESS column in the CLIENTS table retrieves the address information as a concatenated mailing address.

				
connect to sample

...

create type address_t as (street varchar(12), city varchar(12), province varchar(12),
 postal_code char(6)) mode db2sql

create table clients (client_id integer generated always as identity,
 client_lname varchar(12), client_fname varchar(12), address address_t)

create function address_transform (addr address_t) returns varchar(42) language sql
 return addr..street || ', ' || addr..city || ', ' || addr..province ||
  '  ' || addr..postal_code

create transform for address_t db2_program (from sql with function address_transform)

insert into clients (client_lname, client_fname, address) values ('Nicholson', 'James',
 address_t() ..street('20 Indian Rd') ..city('Toronto') ..province('Ontario')
  ..postal_code('M6T2R1'))

select client_id, client_fname, client_lname, address from clients

CLIENT_ID   CLIENT_FNAME CLIENT_LNAME ADDRESS
----------- ------------ ------------ ------------------------------------------
         21 James        Nicholson    20 Indian Rd, Toronto, Ontario  M6T2R1

  1 record(s) selected.

connect reset

Now, suppose we want to retrieve only one address element (say, city) from the CLIENTS table. To do that, we will invoke the observer method (using the double-dot operator) for the city attribute of the structured type value in the ADDRESS column (Listing 2).

Similarly, if we want to update the city attribute value, we can invoke its mutator method in the UPDATE statement (Listing 2).

				
connect to sample

...

select client_id, client_lname, address..city as city from clients

CLIENT_ID   CLIENT_LNAME CITY
----------- ------------ ------------
         21 Nicholson    Toronto

  1 record(s) selected.



update clients set address..city = 'Oakville' where client_id = 21

select client_id, client_lname, address..city as city from clients

CLIENT_ID   CLIENT_LNAME CITY
----------- ------------ ------------
         21 Nicholson    Oakville

  1 record(s) selected.

connect reset

To illustrate a simple typed table hierarchy and the inheritance property, let's create three new structured types (Listing 3): Emp_t, Salesperson_t, and Engineer_t. Emp_t is the root type, and its definition happens to include the Address_t structured type that we created previously. The REF USING INTEGER clause specifies that the INTEGER data type will be used to represent the REFERENCE type of this structured type and all of its subtypes. REFERENCE, in turn, is a system type that is the data type of the object identifier column of any typed table. (Because typed tables contain objects that can be referenced by other objects, every typed table must have an object identifier column as its first column.)

Salesperson_t and Engineer_t are subtypes that are created under Emp_t, which is their supertype. The definitions for these subtypes include additional attributes that are not part of the Emp_t type, but that differentiate these subtypes from their supertype; attributes that are part of the supertype definition, however, are inherited by its subtypes.

Having created the necessary structured types, we can now create typed tables that are based on these types (Listing 3). The supertable in this small table hierarchy is named EMP. In a stroke of imaginative creativity, we name the object identifier column OID and specify that its values will be user generated. After an OID column value is inserted, it cannot be modified. The INHERIT SELECT PRIVILEGES clause specifies that any user or group holding the SELECT privilege on a supertable is to be granted an equivalent privilege on new subtables.

We are now ready to insert data into the ENGINEER and SALESPERSON tables. The VALUES clause of the INSERT statement in both cases includes a call to address_t(), which, as in the earlier example, invokes the constructor function for the address_t structured type to create an instance of the type with all attributes set to null values. The double-dot operator invokes mutator methods to set values for each of the address attributes. The VALUES clause also includes a casting function for the user-specified value of the OID column because the value must be cast into the REFERENCE type of the target table. By default, the name of the casting function is the same as the name of the structured type (in this case, Engineer_t and Salesperson_t, respectively).

Executing queries against the three tables in this hierarchy demonstrates that the ENGINEER and SALESPERSON subtables have inherited the columns of their supertable (EMP), including the object identifier column (OID).

				
connect to sample

...

create type emp_t as (empno integer, lname varchar(12), fname varchar(12),
deptno char(4), salary decimal(7,2), address address_t) ref using integer mode db2sql

create type salesperson_t under emp_t as (commission decimal(7,2)) mode db2sql

create type engineer_t under emp_t as (perf_bonus decimal(7,2),
 recog_award decimal(7,2)) mode db2sql



create table emp of emp_t (ref is oid user generated)

create table salesperson of salesperson_t under emp inherit select privileges

create table engineer of engineer_t under emp inherit select privileges



insert into engineer (oid, empno, lname, fname, deptno, salary,
 perf_bonus, recog_award, address) values (engineer_t(1), 42, 'Kidman', 'Jennifer',
  'Z004', 65000.00, 4000.00, 2000.00, address_t() ..street('7 Dorval Rd')
   ..city('Markham') ..province('Ontario') ..postal_code('L6G2R1'))

insert into salesperson (oid, empno, lname, fname, deptno, salary, commission, address)
 values (salesperson_t(2), 69, 'Theron', 'Maggie', 'C012', 49000.00, 15000.00,
  address_t() ..street('7 River St') ..city('Ottawa') ..province('Ontario')
   ..postal_code('K9G6R2'))



select * from emp

OID         EMPNO       LNAME        FNAME        DEPTNO SALARY    ADDRESS
----------- ----------- ------------ ------------ ------ --------- --------------------
          1          42 Kidman       Jennifer     Z004    65000.00 7 Dorval Rd, Mark...
          2          69 Theron       Maggie       C012    49000.00 7 River St, Ottaw...

  2 record(s) selected.


select oid, empno, lname, deptno, salary, perf_bonus, recog_award, address from engineer

OID         EMPNO       LNAME        DEPTNO SALARY    PERF_BONUS RECOG_AWARD ADDRESS
----------- ----------- ------------ ------ --------- ---------- ----------- ----------
          1          42 Kidman       Z004    65000.00    4000.00     2000.00 7 Dorva...

  1 record(s) selected.


select oid, empno, lname, deptno, salary, commission, address from salesperson

OID         EMPNO       LNAME        DEPTNO SALARY    COMMISSION ADDRESS
----------- ----------- ------------ ------ --------- ---------- ----------------------
          2          69 Theron       C012    49000.00   15000.00 7 River St, Ottawa,...

  1 record(s) selected.

connect reset

Back to top

Summary

We have seen that user-defined structured data types are a useful way to represent structured (non-atomic) data that needs to be handled as separate data elements or as a single unit, depending on the application. This topic is broad and can be complex. This article has introduced you to the basic concepts around structured data types, type hierarchies, and typed tables, and these concepts were clarified through working examples that will get you up and running in no time. To learn more about structured data types, or for more detailed information about any of the topics covered in this article, see the IBM DB2 Universal Database SQL Reference, Volume 2.

Resources

• DB2 Universal Database for Linux, UNIX and Windows Support is the ideal place to locate resources such as the Version 8.2 Information Center and PDF product manuals.
  
  
• For the latest DB2 information online, including more detailed information about structured types, visit the DB2 Information Center.
  
  
• To learn more about DB2, visit the developerWorks DB2 zone. You'll find technical documentation, how-to articles, education, downloads, product information, and more.
  
  
• Visit the developerWorks blogs to get involved in the developerWorks community.
  
  

About the author

Roman B. Melnyk , Ph.D., is a senior member of the DB2 Information Development team, specializing in database administration, DB2 utilities, and SQL. During more than ten years at IBM, Roman has written and edited numerous DB2 books, articles, and other related materials. Roman coauthored DB2 Version 8: The Official Guide (Prentice Hall Professional Technical Reference, 2003), DB2: The Complete Reference (Osborne/McGraw-Hill, 2001), DB2 Fundamentals Certification for Dummies (Hungry Minds, 2001), and DB2 for Dummies (IDG Books, 2000).

Comments (Undergoing maintenance)

Back to top

Trademarks  |  My developerWorks terms and conditions

Table of contents

• Structured data types
• Typed tables
• A detailed example
• Summary
• Resources
• About the author
• Comments

Local resources

• IBM Innovation Center - Austin, TX
• IBM Innovation Center - Chicago, IL
• IBM Innovation Center - Dallas, TX
• IBM Innovation Center - San Mateo, CA
• IBM Innovation Center - Waltham, MA
• Technical Briefings in North America

My developerWorks community

Dig deeper into Information Management on developerWorks

DB2 pureScale

See how true transparent scaling architecture can be achieved on distributed platforms

Special offers