The CREATE DISTINCT TYPE statement creates a distinct type. The distinct type is always sourced on one of the builtin data types. Successful execution of the statement also generates functions to cast between the distinct type and its source type and generates support for the comparison operators (=, <>, <, <=, >, and >=) for use with the distinct type.
This statement can be embedded or issued interactively. It is an executable statement that can be dynamically prepared.
The privileges held by the authorization ID of the statement must include at least one of the following:
The privileges held by the authorization ID of the statement must include at least one of the following:
The authorization ID of the statement has the INSERT privilege on a table when:
If SQL names are specified and a user profile exists that has the same name as the library into which the distinct type is created, and that name is different from the authorization ID of the statement, then the privileges held by the authorization ID of the statement must include at least one of the following:
.DISTINCT. 
If SQL names were specified, the distinct type will be created in the collection or library specified by the implicit or explicit qualifier. The qualifier is the owner of the distinct type if a user profile with that name exists. Otherwise, the owner of the distinct type is the user profile or group user profile of the job invoking the statement.
If system names were specified, the distinct type will be created in the collection or library that is specified by the qualifier. If not qualified, the distinct type will be created in the current library (*CURLIB). The owner of the distinct type is the user profile or group user profile of the job invoking the statement.
If the owner of the distinct type is a member of a group profile (GRPPRF keyword) and group authority is specified (GRPAUT keyword), that group profile will also have authority to the distinct type.
If the distinct type name is not a valid system name, DB2 UDB for AS/400 will generate a system name. For information on the rules for generating a name, see "Rules for Table Name Generation".
distincttypename must not be the name of a builtin data type, or any of the following systemreserved keywords even if you specify them as delimited identifiers.
=  <  >  >= 
<=  <>  ¬=  ¬< 
¬<  !=  !<  !> 
ALL  FALSE  ONLY  TABLE 
AND  FOR  OR  THEN 
ANY  FROM  OVERLAPS  TRIM 
BETWEEN  IN  PARTITION  TRUE 
BOOLEAN  IS  POSITION  TYPE 
CASE  LIKE  RRN  UNIQUE 
CAST  MATCH  SELECT  UNKNOWN 
CHECK  NODENAME  SIMILAR  WHEN 
DISTINCT  NODENUMBER  SOME  
EXCEPT  NOT  STRIP  
EXISTS  NULL  SUBSTRING  
If a qualified distincttypename is specified, the collection name cannot be QSYS, QSYS2, or QTEMP.
A distinct type is created as a *SQLUDT object. If SQL names are used, distinct types are created with the system authority of *EXCLUDE to *PUBLIC. If system names are used, distinct types are created with the authority to *PUBLIC as determined by the create authority (CRTAUT) parameter of the collection or library.
The successful execution of the CREATE DISTINCT TYPE statement causes DB2 to generate the following cast functions:
The cast functions are created as if the following statements were executed:
CREATE FUNCTION distincttypename (sourcetypename)
RETURNS distincttypename
CREATE FUNCTION sourcetypename (distincttypename)
RETURNS sourcetypename
Even if you specified a length, precision, or scale for the source data type in the CREATE DISTINCT TYPE statement, the name of the cast function that converts from the distinct type to the source type is simply the name of the source data type. The data type of the value that the cast function returns includes any length, precision, or scale values that you specified for the source data type. (See Table 23.)
The name of the cast function that converts from the source type to the distinct type is the name of the distinct type. The input parameter of the cast function has the same data type as the source data type, including the length, precision, and scale.
For example, assume that a distinct type named T_SHOESIZE is created with the following statement:
CREATE DISTINCT TYPE CLAIRE.T_SHOESIZE AS VARCHAR(2) WITH COMPARISONS
When the statement is executed, DB2 also generates the following cast functions. VARCHAR converts from the distinct type to the source type, and T_SHOESIZE converts from the source type to the distinct type.
FUNCTION CLAIRE.VARCHAR (CLAIRE.T_SHOESIZE) RETURNS VARCHAR(2)
FUNCTION CLAIRE.T_SHOESIZE (VARCHAR(2) RETURNS CLAIRE.T_SHOESIZE
Notice that function VARCHAR returns a value with a data type of VARCHAR(2) and that function T_SHOESIZE has an input parameter with a data type of VARCHAR(2).
The schema of the generated cast functions is the same as the collection of the distinct type. A function with the same name and function signature must not already exist in the collection.
You cannot explicitly drop a generated cast function. The cast functions that are generated for a distinct type are implicitly dropped when the distinct type is dropped with the DROP statement.
For each builtin data type that can be the source data type for a distinct type, the following table gives the names of the generated cast functions, the data types of the input parameters, and the data types of the values that the functions returns.
Table 23. CAST Functions on Distinct Types
Source Type Name  Function Name  Parameter Type  Return Type  

BLOB  distinct  BLOB(n)  distinct  

BLOB  distinct  BLOB(n)  
CHAR  distinct  CHAR(n)  distinct  

CHAR  distinct  CHAR(n)  

distinct  VARCHAR(n)  distinct  
VARCHAR  distinct  VARCHAR(n)  distinct  

VARCHAR  distinct  VARCHAR(n)  
CLOB  distinct  CLOB(n)  distinct  

CLOB  distinct  CLOB(n)  
GRAPHIC  distinct  GRAPHIC(n)  distinct  

GRAPHIC  distinct  GRAPHIC(n)  

distinct  VARGRAPHIC(n)  distinct  
VARGRAPHIC  distinct  VARGRAPHIC(n)  distinct  

VARGRAPHIC  distinct  VARGRAPHIC(n)  
DBCLOB  distinct  DBCLOB(n)  distinct  

DBCLOB  distinct  DBCLOB(n)  
SMALLINT  distinct  SMALLINT  distinct  

SMALLINT  distinct  SMALLINT  

distinct  INTEGER  distinct  
INTEGER  distinct  INTEGER  distinct  

INTEGER  distinct  INTEGER  
BIGINT  distinct  BIGINT  distinct  

BIGINT  distinct  BIGINT  
DECIMAL  distinct  DECIMAL(p,s)  distinct  

DECIMAL  distinct  DECIMAL(p,s)  
NUMERIC  distinct  NUMERIC(p,s)  distinct  

NUMERIC  distinct  NUMERIC(p,s)  
REAL  distinct  REAL  distinct  

REAL  distinct  REAL  

distinct  DOUBLE  distinct  
FLOAT(n) where n <= 24  distinct  REAL  distinct  

REAL  distinct  REAL  

distinct  DOUBLE  distinct  
FLOAT(n) where n > 24  distinct  DOUBLE  distinct  

DOUBLE  distinct  DOUBLE  
DOUBLE or DOUBLE PRECISION  distinct  DOUBLE  distinct  

DOUBLE  distinct  DOUBLE  
DATE  distinct  DATE  distinct  

DATE  distinct  DATE  
TIME  distinct  TIME  distinct  

TIME  distinct  TIME  
TIMESTAMP  distinct  TIMESTAMP  distinct  

TIMESTAMP  distinct  TIMESTAMP  
DATALINK  distinct  DATALINK  distinct  

DATALINK  distinct  DATALINK  

NUMERIC and FLOAT are not recommended when creating a distinct type for a portable application. DECIMAL and DOUBLE should be used instead.
Create a distinct type named SHOESIZE that is sourced on an INTEGER data type.
CREATE DISTINCT TYPE SHOESIZE AS INTEGER WITH COMPARISONS
The successful execution of this statement also generates two cast functions. Function INTEGER(SHOESIZE) returns a value with data type INTEGER, and function SHOESIZE(INTEGER) returns a value with distinct type SHOESIZE.
Create a distinct type named MILES that is sourced on a DOUBLE data type.
CREATE DISTINCT TYPE MILES AS DOUBLE WITH COMPARISONSThe successful execution of this statement also generates two cast functions. Function DOUBLE(MILES) returns a value with data type DOUBLE, and function MILES(DOUBLE) returns a value with distinct type MILES.