The CREATE DISTINCT TYPE statement creates a distinct type. The distinct type is always sourced on one of the built-in 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.

Invocation

This statement can be embedded or issued interactively. It is an executable statement that can be dynamically prepared.

Authorization

The privileges held by the authorization ID of the statement must include at least one of the following:

  • The system authorities *EXECUTE, *READ and *ADD to the library into which the distinct type is created, and
  • Administrative authority

The privileges held by the authorization ID of the statement must include at least one of the following:

  • For the SYSTYPES catalog table:
    • The INSERT privilege on the table, and
    • The system authority *EXECUTE on library QSYS2
  • Administrative authority

The authorization ID of the statement has the INSERT privilege on a table when:

  • It is the owner of the table,
  • It has been granted the INSERT privilege on the table, or
  • It has been granted the system authorities of *OBJOPR and *ADD on the table.

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:

  • The system authority *ADD to the user profile with that name
  • Administrative authority

Syntax



            .-DISTINCT-.

        >>-CREATE---+----------+--TYPE--distinct-type-name-------------->

        

        .-WITH COMPARISONS--.

        >-----AS--source-data-type--+-------------------+--------------><

        

        source-data-type

        

        |---+---BIGINT--------------------------------------------------------------------------------+->

        +-+-INTEGER-+-----------------------------------------------------------------------------+

        | '-INT-----'                                                                             |

        +---SMALLINT------------------------------------------------------------------------------+

        +--+-DECIMAL-+---+-------------------------------+----------------------------------------+

        |  +-DEC-----+   '-(--integer--+-----------+--)--'                                        |

        |  '-NUMERIC-'                 '-, integer-'                                              |

        +-+-FLOAT--+----------------+-+-----------------------------------------------------------+

        | |        '-(--integer--)--' |                                                           |

        | +-REAL----------------------+                                                           |

        | |        .-PRECISION-.      |                                                           |

        | '-DOUBLE-+-----------+------'                                                           |

        +----+-BLOB----------------+-----+------------------------+-------------------------------+

        |    '-BINARY LARGE OBJECT-'     '-(--integer--+---+---)--'                               |

        |                                              +-K-+                                      |

        |                                              '-M-'                                      |

        +--+--+-CHARACTER-+---+----------------+--------------------------+---+-----------------+-+

        |  |  '-CHAR------'   '-(--integer--)--'                          |   +-FOR BIT DATA----+ |

        |  +-----+-VARCHAR-------------------+---(--integer--)------------+   +-FOR SBCS DATA---+ |

        |  |     '--+-CHARACTER-+---VARYING--'                            |   +-FOR MIXED DATA--+ |

        |  |        '-CHAR------'                                         |   '-CCSID--integer--' |

        |  '----+-CLOB-------------------+-----+------------------------+-'                       |

        |       +-CHAR LARGE OBJECT------+     '-(--integer--+---+---)--'                         |

        |       '-CHARACTER LARGE OBJECT-'                   +-K-+                                |

        |                                                    '-M-'                                |

        +--+----GRAPHIC-----+----------------+----------+---+-----------------+-------------------+

        |  |                '-(--integer--)--'          |   '-CCSID--integer--'                   |

        |  +-----+-VARGRAPHIC------+---(--integer--)----+                                         |

        |  |     '-GRAPHIC VARYING-'                    |                                         |

        |  '----DBCLOB------+------------------------+--'                                         |

        |                   '-(--integer--+---+---)--'                                            |

        |                                 +-K-+                                                   |

        |                                 '-M-'                                                   |

        +---DATE----------------------------------------------------------------------------------+

        +---TIME----------------------------------------------------------------------------------+

        +---TIMESTAMP-----------------------------------------------------------------------------+

        '---DATALINK--+----------------+---+-----------------+------------------------------------'

        '-(--integer--)--'   '-CCSID--integer--'

        

        >---------------------------------------------------------------|

Description

distinct-type-name
Names the distinct type. The name, including the implicit or explicit qualifier, must not be the same as a distinct type that already exists at the current server.

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".

distinct-type-name must not be the name of a built-in data type, or any of the following system-reserved 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 distinct-type-name is specified, the collection name cannot be QSYS, QSYS2, or QTEMP.

source-data-type
Specifies the data type that is used as the basis for the internal representation of the distinct type. The data type must be a built-in data type. You can use any of the built-in data types that are allowed for the CREATE TABLE statement except for LONG VARCHAR or LONG VARGRAPHIC. For more information about data types see CREATE TABLE.
WITH COMPARISONS
Specifies that system-generated comparison operators are to be created for comparing two instances of the distinct type. WITH COMPARISONS is the default. Comparisons will be generated for all source types with the exception of a DATALINK whether or not WITH COMPARISONS is specified. For compatibility with other DB2 products, WITH COMPARISONS should be specified.

Notes

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:

  • One function to convert from the distinct type to the source type
  • One function to convert from the source type to the distinct type
  • One function to convert from INTEGER to the distinct type if the source type is SMALLINT
  • One function to convert from DOUBLE to the distinct type if the source type is REAL
  • one function to convert from VARCHAR to the distinct type if the source type is CHAR
  • one function to convert from VARGRAPHIC to the distinct type if the source type is GRAPHIC.

The cast functions are created as if the following statements were executed: 

CREATE FUNCTION distinct-type-name (source-type-name)

RETURNS distinct-type-name



CREATE FUNCTION source-type-name (distinct-type-name)

RETURNS source-type-name

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 built-in 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
Notes:

* Conversion is only supported for UCS-2 graphic.

Only a DATALINK can be cast to a DATALINK type.

NUMERIC and FLOAT are not recommended when creating a distinct type for a portable application. DECIMAL and DOUBLE should be used instead.

Examples

Example 1

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.

Example 2

Create a distinct type named MILES that is sourced on a DOUBLE data type. 

CREATE DISTINCT TYPE MILES AS DOUBLE WITH COMPARISONS
The 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.