(OO) concepts and methodology s into your relational database by extending it 

with richer sets of types and functions. With these features, you can store 

instances of object oriented data types in columns of tables, and operate on 

them by means of functions in SQL statements. 

The basic object oriented support is provided by DB2 UDB using user defined 

types (UDTs) and user defined functions (UDFs) and LOBs. They can be used to 

build extensions to DB2 UDB, which include a whole set of customized types and 

functionality. 

User defined data types are customized data types derived from the existing 

built in data types in DB2 UDB. They are useful when existing data types do not 

serve your application requirements, and when you need data integrity, which 

can be achieved by strong typing and encapsulation.

There are three types of user defined types:

Distinct type

A distinct type is a user defined data type that is based on an existing built in 

data type. Internally, it is stored as an existing data type, but it is considered 

as a separate and incompatible type. The main advantages of using distinct 

type are extensibility, strong typing, encapsulation, and customization.

A distinct type can be created by issuing the CREATE DISTINCT TYPE 

statement. The following statement defines a new distinct type for our sample 

application where we want all the identification numbers to have common 

properties and functions. To achieve this goal we create a distinct type ID, 

which contains INTERGER values:

db2>create distinct type ID as integer with comparisons

Structured type

A structured type is a user defined data type that has a well defined structure 

consisting of existing built in or user defined data types. A structured type has 

attributes and methods defined. The attribute defines its data storage 

properties and methods define its behavior. 

A structured type may be used as the type of a table, view, or column. When 

used as the type for a table or view, that table or view is known as a typed 

table or typed view respectively. In this case, attributes of structured type 

becomes columns of a typed table or view.

A structured type can be created using the 

CREATE TYPE

 statement. For 

example, if we want our example database 

ITSODB

 design to use the UDT 

features, we can define a 

product 

and 

sku

 type, which can be used to create 

typed tables as shown in Example 10 6. Figure 10 2 shows its hierarchy.

 Chapter 10. Advanced DB2 UDB features 

325