This statement can be embedded in an application program or issued interactively.
The privilege set that is defined below must include at least one of the following:
The authorization ID that matches the schema name implicitly has the CREATEIN privilege on the schema.
If the statement is embedded in an application program, the privilege set is the privileges that are held by the authorization ID
of the owner of the plan or package.
If the statement is dynamically prepared, the privilege set is the privileges that are held by the SQL authorization ID of the process. The specified distinct type name can include a schema name (a qualifier). However, if the schema name is not the same as the SQL authorization ID, one of the following conditions must be met:
CREATE DISTINCT TYPE distinct-type-name AS source-data-type
[WITH COMPARISONS]
source-data-type:
SMALLINT |
{INTEGER | INT} |
{DECIMAL | DEC | NUMERIC} [integer[,...]] |
{FLOAT [integer] | REAL [PRECISION] | DOUBLE} |
{ {CHARACTER | CHAR} [(integer)] |
{CHARACTER | CHAR} VARYING (integer) |
VARCHAR (integer) }
[FOR {SBCS | MIXED | BIT} DATA] [CCSID {EBCDIC | ASCII}] |
{{CHARACTER | CHAR} LARGE OBJECT | CLOB} [(integer [K|M|G])] }
[FOR {SBCS | MIXED} DATA] [CCSID {EBCDIC | ASCII}] } |
{BINARY LARGE OBJECT | BLOB} (integer [K|M|G]) |
{GRAPHIC [(integer)] | VARGRAPHIC (integer) | DBCLOB (integer [K|M|G])} [CCSID {EBCDIC | ASCII}]|
{DATE | TIME | TIMESTAMP} |
ROWID
Names the distinct type. The name is implicitly or explicitly qualified by a schema name. The name, together with the implicit or explicit schema name, must not identify a distinct type that exists at the current server.
The unqualified form of distinct-type-name is a long SQL identifier.
distinct-type-name must not be the name of a built-in data type, BOOLEAN, or any of following system-reserved keywords even if you specify them as delimited identifiers:
| ALL | LIKE | UNIQUE |
| AND | MATCH | UNKNOWN |
| ANY | NOT | = |
| BETWEEN | NULL | ¬= |
| DISTINCT | ONLY | < |
| EXCEPT | OR | <= |
| EXISTS | OVERLAPS | ¬< |
| FALSE | SIMILAR | > |
| FOR | SOME | >= |
| FROM | TABLE | ¬> |
| IN | TRUE | <> |
| IS | TYPE |
The unqualified name is implicitly qualified with a schema name according to the following rules:
The qualified form of distinct-type-name is a short SQL identifier (the schema name) followed by a period and a long SQL identifier.
The schema name can be 'SYSTOOLS' if the user who executes the CREATE statement has SYSADM or SYSCTRL privilege. Otherwise, the schema name must not begin with 'SYS' unless the schema name is 'SYSADM'.
The owner of the distinct type is determined by how the CREATE DISTINCT TYPE statement is invoked:
Although the information is not recorded in the catalog, the owner is given the USAGE privilege on the distinct type. The owner is also given the EXECUTE privilege with the GRANT option on each of the generated cast functions.
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 LONG VARCHAR or LONG VARGRAPHIC. Use VARCHAR or VARGRAPHIC with an explicit length instead.
If you do not specify a specific value for the data types that have length, precision, or scale attributes (CHAR, GRAPHIC, DECIMAL, NUMERIC, FLOAT), the defaults are as follows:
| CHAR | CHAR(1) |
| GRAPHIC | GRAPHIC(1) |
| DECIMAL | DECIMAL(5,0) |
| FLOAT | DOUBLE (length of 8) |
If the distinct type is sourced on a character string data type, the FOR clause indicates the subtype. If you do not specify the FOR clause, the distinct type is defined with the default subtype. The default is SBCS when the value of field MIXED DATA on installation panel DSNTIPF is NO. The default is MIXED when the value is YES.
If the distinct type is sourced on a string data type, the CCSID clause indicates whether the encoding scheme of the data is ASCII or EBCDIC. If you do not specify CCSID ASCII or CCSID EBCDIC, the encoding scheme is the value of field DEF ENCODING SCHEME on installation panel DSNTIPF.
Specifies that system-generated comparison operators are to be created for comparing two instances of the distinct type. Do not specify WITH COMPARISONS if the source data type is BLOB, CLOB, or DBCLOB; otherwise, a warning occurs and the comparison operators are not generated. You must specify WITH COMPARISONS for all other source data types.
DB2 implicitly creates comparison functions for the following comparison operators for use with the distinct type:
| BETWEEN | IS NULL | < | ¬< |
| NOT BETWEEN | IS NOT NULL | > | ¬> |
| IN | = | <= | >= |
| NOT IN | ¬= | <> |
The name of the function is the same as the comparison operator. You cannot invoke the comparison functions using function notation syntax, for example, '<'(C1,C2). Instead, use the syntax C1 < C2.
You must not specify the comparison operations for distinct types that are sourced on a CLOB, BLOB, or DBCLOB, or that have a length greater than 255 bytes. (Distinct types that are sourced on a VARCHAR or VARGRAPHIC can have a length greater than 255 bytes).
If you specify a GRAPHIC source type and the ASCII encoding scheme, or a string source type with a mixed data subtype (FOR MIXED DATA), the value of field FOR MIXED DATA on installation panel DSNTIPF must be YES; otherwise, an error occurs. In addition, to create a distinct type on a GRAPHIC data type, the corresponding CCSID must be defined for the implicitly or explicitly specified encoding scheme.
The successful execution of the CREATE DISTINCT TYPE statement causes DB2 to generate the following cast functions:
For some source data types, DB2 supports an additional function to convert from:
The cast functions are created as if the following statements were executed:
CREATE FUNCTION source-type-name (distinct-type-name)
RETURNS source-type-name ...
CREATE FUNCTION distinct-type-name (source-type-name)
RETURNS distinct-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.
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 SYSIBM.VARCHAR (2) FUNCTION CLAIRE.T_SHOESIZE (SYSIBM.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 schema of the distinct type. No other function with the same name and function signature must already exist in the database.
In the preceding example, if T_SHOESIZE had been sourced on a SMALLINT, CHAR, or GRAPHIC data type instead of a VARCHAR data type, another cast function would have been generated in addition to the two functions to cast between the distinct type and the source data type. For example, assume that T_SHOESIZE is created with this statement:
CREATE DISTINCT TYPE CLAIRE.T_SHOESIZE AS CHAR(2) WITH COMPARISONS
When the statement is executed, DB2 generates these cast functions:
FUNCTION CLAIRE.CHAR (CLAIRE.T_SHOESIZE) RETURNS SYSIBM.CHAR (2) FUNCTION CLAIRE.T_SHOESIZE (SYSIBM.CHAR (2)) RETURNS CLAIRE.T_SHOESIZE FUNCTION CLAIRE.T_SHOESIZE (SYSIBM.VARCHAR (2)) RETURNS CLAIRE.T_SHOESIZE
Notice that the third function enables the casting of a VARCHAR(2) to T_SHOESIZE. This additional function is created to enable casting a constant, such as 'AB', directly to the distinct type. Without the additional function, you would have to first cast 'AB', which has a data type of VARCHAR, to a data type of CHAR and then cast it to the distinct type.
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.
When a distinct type is defined, the built-in functions (such as AVG, MAX, and LENGTH) are not automatically supported for the distinct type. You can use a built-in function on a distinct type only after a sourced user-defined function, which is based on the built-in function, has been created for the distinct type.
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 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.