Application Development Guide

Defining Tables with UDTs

After you have defined several UDTs, you can start defining tables with columns whose types are UDTs. Following are examples using CREATE TABLE:

Example: Sales

Suppose you want to define tables to keep your company's sales in different countries as follows:

     CREATE TABLE US_SALES 
       (PRODUCT_ITEM  INTEGER, 
        MONTH         INTEGER CHECK (MONTH BETWEEN 1 AND 12), 
        YEAR          INTEGER CHECK (YEAR > 1985), 
        TOTAL         US_DOLLAR) 
      
     CREATE TABLE CANADIAN_SALES 
       (PRODUCT_ITEM  INTEGER, 
        MONTH         INTEGER CHECK (MONTH BETWEEN 1 AND 12), 
        YEAR          INTEGER CHECK (YEAR > 1985), 
        TOTAL         CANADIAN_DOLLAR) 
     
     CREATE TABLE GERMAN_SALES
       (PRODUCT_ITEM  INTEGER, 
        MONTH         INTEGER CHECK (MONTH BETWEEN 1 AND 12), 
        YEAR          INTEGER CHECK (YEAR > 1985), 
        TOTAL         EURO)

The UDTs in the above examples are created using the same CREATE DISTINCT TYPE statements in Example: Money. Note that the above examples use check constraints. For information on check constraints refer to the SQL Reference.

Example: Application Forms

Suppose you need to define a table where you keep the forms filled out by applicants as follows:

     CREATE TABLE APPLICATIONS 
       (ID                SYSIBM.INTEGER, 
        NAME              VARCHAR (30), 
        APPLICATION_DATE  SYSIBM.DATE, 
        FORM              PERSONAL.APPLICATION_FORM)

You have fully qualified the UDT name because its qualifier is not the same as your authorization ID and you have not changed the default function path. Remember that whenever type and function names are not fully qualified, DB2 searches through the schemas listed in the current function path and looks for a type or function name matching the given unqualified name. Because SYSIBM is always considered (if it has been omitted) in the current function path, you can omit the qualification of built-in data types. For example, you can execute SET CURRENT FUNCTION PATH = cheryl and the value of the current function path special register will be "CHERYL", and does not include "SYSIBM". Now, if CHERYL.INTEGER type is not defined, the statement CREATE TABLE FOO(COL1 INTEGER) still succeeds because SYSIBM is always considered as COL1 is of type SYSIBM.INTEGER.

You are, however, allowed to fully qualify the built-in data types if you wish to do so. Details about the use of the current function path are discussed in the SQL Reference.

[ DB2 List of Books | Search the DB2 Books ]