These are User-defined Data Types in DB2

In DB2, a tricky subject is user-defined data types. In addition to the built-in data types, you can create user-defined data types similar to built-in.

Here you will know the creation and usage of distinct user-defined data types: CANDOL and USADOL.

User-defined distinct

Define a new data type based on a built-in type. This new type has the same features as the built-in type, but you can use it to ensure that only values of the same type are compared.

For example, you can define a Canadian dollar type (CANDOL) and a US dollar type(USADOL), both based on DECIMAL(10,2). Both types are based on the same built-in type, but you won’t be able to compare them unless a conversion function is applied. The following CREATE TYPE statements create the CANDOL and USADOL UDTs,

Here’s how to create a distinct user-defined data type

The create type statement creates data type you want.

CREATE DISTINCT TYPE CANDOL AS DECIMAL(10,2) WITH COMPARISONS;
CREATE DISTINCT TYPE USADOL AS DECIMAL(10,2) WITH COMPARISONS;

DB2 automatically generates functions to perform casting between the base type and the distincttype, and comparison operators for comparing instances of the distinct type.

Here’s how to use user-defined data type

CREATE TABLE ITEMS (ITEMID CHAR(5), PRICE CANDOL);
INSERT INTO ITEMS VALUES('ABC11',CANDOL(30.50) );

These are other data types you can create in Linux, UNIX, and WINDOWS versions.

DB2 user-defined data types list

User-defined array

An array type is defined as an array with elements of another data type. This type supports in Zos too.

User-defined cursor

A cursor type represents a reference to an underlying cursor.

User-defined row

A row type is a structure that can be used by SQL PL as a data type for variables and parameters to represent a row for simpler manipulation. It can also be used as the data type of an array.

User-defined structured

A structured data type has a structure that contains a sequence of attributes each of which has a data type. A structured type may be used as the type of a table, view, or column.

For example, you can create a structured type named ADDRESS that contains data for street number, street name, city, and so forth. Then you can use this type when defining other tables, such as employees or suppliers because the same data is required for both.

Also, structured types can have sub types in a hierarchical structure. This lets you store objects that belong to a hierarchy.

User-defined reference

When using structured types, you can define references to rows in another table using reference types.

These references appear similar to referential constraints; however, they do not enforce relationships between the tables. References in tables let you specify queries in a different way.

Reference