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 addresst, _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.
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.
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.
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_ >).
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 (
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 SQLtransform 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 (
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,
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.
Listing 1. Using a structured data type as the type for a column in a regular table
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).
Listing 2. Updating an instance of a structured data type in the column of a regular table
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).
Listing 3. A typed table hierarchy, demonstrating inheritance
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
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.