Distinct types

A distinct type is a user-defined data type that is based on existing built-in DB2® data types.

A distinct type is internally the same as a built-in data type, but DB2 treats them as a separate and incompatible type for semantic purposes.

Defining your own distinct type ensures that only functions that are explicitly defined on a distinct type can be applied to its instances.

Example 1: You might define a US_DOLLAR distinct type that is based on the DB2 DECIMAL data type to identify decimal values that represent United States dollars. The US_DOLLAR distinct type does not automatically acquire the functions and operators of its source type, DECIMAL.

Although you can have different distinct types that are based on the same built-in data types, distinct types have the property of strong typing. With this property, you cannot directly compare instances of a distinct type with anything other than another instance of that same type. Strong typing prevents semantically incorrect operations (such as explicit addition of two different currencies) without first undergoing a conversion process. You define which types of operations can occur for instances of a distinct type.

If your company wants to track sales in many countries, you must convert the currency for each country in which you have sales.

Begin general-use programming interface information.

Example 2: You can define a distinct type for each country. For example, to create US_DOLLAR types and CANADIAN_DOLLAR types, you can use the following CREATE DISTINCT TYPE statements:

CREATE DISTINCT TYPE US_DOLLAR AS DECIMAL (9,2);
CREATE DISTINCT TYPE CANADIAN_DOLLAR AS DECIMAL (9,2);

Example 3: After you define distinct types, you can use them in your CREATE TABLE statements:

CREATE TABLE US_SALES
  (PRODUCT_ITEM_NO INTEGER,
   MONTH           INTEGER,
   YEAR            INTEGER,
   TOTAL_AMOUNT    US_DOLLAR);
CREATE TABLE CANADIAN_SALES
  (PRODUCT_ITEM_NO INTEGER,
   MONTH           INTEGER,
   YEAR            INTEGER,
   TOTAL_AMOUNT    CANADIAN_DOLLAR);

End general-use programming interface information.

User-defined functions support the manipulation of distinct types.