Application Development Guide

Examples of Using CREATE DISTINCT TYPE

The following are examples of using CREATE DISTINCT TYPE:

Example: Money

Suppose you are writing applications that need to handle different currencies and wish to ensure that DB2 does not allow these currencies to be compared or manipulated directly with one another in queries. Remember that conversions are necessary whenever you want to compare values of different currencies. So you define as many distinct types as you need; one for each currency that you may need to represent:

     CREATE DISTINCT TYPE US_DOLLAR AS DECIMAL (9,2) WITH COMPARISONS 
     CREATE DISTINCT TYPE CANADIAN_DOLLAR AS DECIMAL (9,2) WITH COMPARISONS 
     CREATE DISTINCT TYPE EURO AS DECIMAL (9,2) WITH COMPARISONS

Note that you have to specify WITH COMPARISONS since comparison operators are supported on DECIMAL (9,2).

Example: Job Application

Suppose you would like to keep the form filled by applicants to your company in a DB2 table and you are going to use functions to extract the information from these forms. Because these functions cannot be applied to regular character strings (because they are certainly not able to find the information they are supposed to return), you define a distinct type to represent the filled forms:

     CREATE DISTINCT TYPE PERSONAL.APPLICATION_FORM AS CLOB(32K)

Because DB2 does not support comparisons on CLOBs, you do not specify the clause WITH COMPARISONS. You have specified a schema name different from your authorization ID since you have DBADM authority, and you would like to keep all distinct types and UDFs dealing with applicant forms in the same schema.