Distinct type

ID

Structure  type

Typed table

use

Order

Product_type

Product

Structure  type

Typed table

SKU_type

SKU

Figure 10 2   User defined data types

Example 10 6   Structured types and typed tables

db2>create type product_type as (

name varchar(20), 

description varchar(200),

brand varchar(30))

instantiable ref using integer mode db2sql

db2>create type sku_type 

under product_type as (quantity integer)

instantiable mode db2sql

db2>create table product 

of product_type (ref is id user generated)

db2>create table sku of sku_type 

under product inherit select privileges

db2>create table order(id ID,sku sku_type)

We can also use this type as a type for a column as shown in the last 

statement of Example 10 6.

Reference type

A reference type is a companion type to a structured type; it is quite similar to 

a distinct type. The reference type representation is defined when the root 

type of a type hierarchy is created. When using a reference type, a structured 

326 

MySQL to DB2 UDB Conversion Guide