Application Development Guide

Defining Tables with Distinct Types

After you have defined several distinct types, you can start defining tables with columns whose types are distinct types. 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 distinct types 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 distinct type 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.