CREATE TYPE (distinct)
The CREATE TYPE (distinct) statement defines a distinct type, which is a data type that a user defines. A distinct type must be based on one of the built-in data types.
Successful execution of the statement also generates:
- A function to cast between the distinct type and its source type
- A function to cast between the source type and its distinct type
- As appropriate, support for the use of comparison operators with the distinct type
Invocation
This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared only if DYNAMICRULES run behavior is implicitly or explicitly specified.
Authorization
The privilege set that is defined below must include at least one of the following:
- The CREATEIN privilege on the schema
- SYSADM or SYSCTRL authority
- System DBADM
The authorization ID that matches the schema name implicitly has the CREATEIN privilege on the schema.
Privilege set: If the statement is embedded in an application program, the privilege set is the privileges that are held by the owner of the plan or package. If the owner is a role, the implicit schema match does not apply and this role needs to include one of the previously listed conditions.
If the statement is dynamically prepared and is not running in a trusted context for which the ROLE AS OBJECT OWNER clause is specified, the privilege set is the set of privileges that are held by the SQL authorization ID of the process. The specified distinct type name can include a schema name (a qualifier). If the schema name is not the same as the SQL authorization ID of the process, one of the following conditions must be met:
- The privilege set includes SYSADM or SYSCTRL authority.
- The SQL authorization ID of the process has the CREATEIN privilege on the schema.
Syntax
>>-CREATE--TYPE--distinct-type-name--AS--| source-data-type |---> >--+----------------------------+------------------------------>< | (1) | '-INLINE LENGTH--integer-----'
- INLINE LENGTH can only be specified when source-data-type is a LOB data type.
source-data-type
>>-+-+-SMALLINT----+-------------------------------------------------------------------------------------------------+->< | +-+-INTEGER-+-+ | | | '-INT-----' | | | '-BIGINT------' | | .-(5,0)--------------------. | +-+-DECIMAL-+--+--------------------------+-----------------------------------------------------------------------+ | +-DEC-----+ '-(integer-+-----------+-)-' | | '-NUMERIC-' '-, integer-' | | .-(53)------. | +-+-FLOAT--+-----------+--+---------------------------------------------------------------------------------------+ | | '-(integer)-' | | | +-REAL------------------+ | | | .-PRECISION-. | | | '-DOUBLE--+-----------+-' | | .-(34)-. | +-DECFLOAT--+------+----------------------------------------------------------------------------------------------+ | '-(16)-' | | .-(1)-------. | +-+-+-+-CHARACTER-+--+-----------+----------+----+--------------------+--+----------------------+---------------+-+ | | | '-CHAR------' '-(integer)-' | '-CCSID--+-ASCII---+-' '-FOR--+-SBCS--+--DATA-' | | | | '-+-+-CHARACTER-+--VARYING-+--(integer)-' +-EBCDIC--+ +-MIXED-+ | | | | | '-CHAR------' | '-UNICODE-' '-BIT---' | | | | '-VARCHAR----------------' | | | | .-(1M)-------------. | | | '-+-+-CHARACTER-+--LARGE OBJECT-+--+------------------+----+--------------------+--+----------------------+---' | | | '-CHAR------' | '-(integer-+---+-)-' '-CCSID--+-ASCII---+-' '-FOR--+-SBCS--+--DATA-' | | '-CLOB------------------------' +-K-+ +-EBCDIC--+ '-MIXED-' | | +-M-+ '-UNICODE-' | | '-G-' | | .-(1)-------. | +-+-GRAPHIC--+-----------+-------+--+--------------------+--------------------------------------------------------+ | | '-(integer)-' | '-CCSID--+-ASCII---+-' | | +-VARGRAPHIC--(--integer--)----+ +-EBCDIC--+ | | | .-(1M)-------------. | '-UNICODE-' | | '-DBCLOB--+------------------+-' | | '-(integer-+---+-)-' | | +-K-+ | | +-M-+ | | '-G-' | | .-(1)-------. | +-+-BINARY--+-----------+-------------------------+---------------------------------------------------------------+ | | '-(integer)-' | | | +-+-BINARY VARYING-+-(integer)------------------+ | | | '-VARBINARY------' | | | | .-(1M)-------------. | | | '-+-BINARY LARGE OBJECT-+--+------------------+-' | | '-BLOB----------------' '-(integer-+---+-)-' | | +-K-+ | | +-M-+ | | '-G-' | +-+-DATE------------------------------------------------+---------------------------------------------------------+ | +-TIME------------------------------------------------+ | | | .-(--6--)-------. .-WITHOUT TIME ZONE-. | | | '-TIMESTAMP--+---------------+--+-------------------+-' | | '-(--integer--)-' '-WITH TIME ZONE----' | '-ROWID-----------------------------------------------------------------------------------------------------------'
Description
- distinct-type-name
- Names the distinct type. The name, including the
implicit or explicit qualifier, must not identify a distinct type
that exists at the current server.
- The unqualified form of 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 qualified form of distinct-type-name is an SQL identifier (the schema name) followed by a period and an 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 unqualified form of 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:
- 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. For more information on built-in data types, see built-in-type.
If the distinct type is based on a character or graphic 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. For ASCII or EBCDIC data, 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. For UNICODE character data, the default subtype is mixed.
If the distinct type is based on a string data type, the CCSID clause indicates whether the encoding scheme of the data is ASCII, EBCDIC or UNICODE. If you do not specify CCSID ASCII, CCSID EBCDIC, or UNICODE, the encoding scheme is the value of field DEF ENCODING SCHEME on installation panel DSNTIPF.
- INLINE LENGTH integer
- Specifies the default inline length for columns that reference
the distinct type. INLINE LENGTH can only be specified when source-data-type is
a LOB data type. Only columns in a table that is in a universal table
space can inherit the specified inline length for the distinct type.
If the table is not in a universal table space, the specified inline
length is ignored.
Where source-data-type is BLOB and CLOB, integer specifies the maximum number of bytes that are stored in the base table space for columns that reference this distinct type. integer must be between 0 and 32680 (inclusive) for a BLOB or CLOB source-data-type.
Where source-data-type is DBCLOB, integer specifies the maximum number of double-byte characters that are stored in the table space for columns that reference the distinct type. integer must be between 0 and 16340 (inclusive) for a DBCLOB source-data-type.
If INLINE LENGTH is specified with a value of 0 for integer, any column that references the distinct type will not have an inline length unless the CREATE TABLE or ALTER TABLE ADD statement specifies an inline length for the column.
If INLINE LENGTH is not specified, any column that reference the distinct type takes its default vale from the value of the LOB INLINE LENGTH parameter on installation panel DSNTIPD.
integer cannot be greater than the maximum length of the distinct type.
Notes
- Owner privileges:
- The owner of the distinct type is authorized to define columns, parameters, or variables with the distinct type (USAGE privilege) with the ability to grant these privileges to others. See GRANT (type or JAR file privileges). The owner is also authorized to invoke the generated cast function (EXECUTE privilege; see GRANT (function or procedure privileges)). The owner is given the USAGE and EXECUTE privileges with the GRANT option. For more information about ownership of the object, see Authorization, privileges, permissions, masks, and object ownership.
- Source data types with DBCS or mixed data:
- When the implicit or explicit encoding scheme is ASCII or EBCDIC and the source data type is graphic or a character type is MIXED DATA, then the value of field FOR MIXED DATA on installation panel DSNTIPF must be YES; otherwise, an error occurs.
- Generated cast functions:
- The successful execution of the CREATE TYPE (distinct) statement
causes DB2® to generate the following
cast functions:
- A function to convert from the distinct type to its source data type
- A function to convert from the source data type to the distinct type
- A function to cast from a data type A to distinct type DT,
where A is promotable to the source data type S of distinct
type DT For some source data types, DB2 supports an additional function to convert from:
- INTEGER to the distinct type if the source type is SMALLINT
- VARCHAR to the distinct type if the source type is CHAR
- VARGRAPHIC to the distinct type if the source type is GRAPHIC
- VARBINARY to the distinct type if the source type is BINARY
- DOUBLE to the distinct type if the source type is REAL
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 TYPE (distinct) 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 1 for details.)
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 TYPE (distinct) CLAIRE.T_SHOESIZE AS VARCHAR(2)
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 TYPE (distinct) CLAIRE.T_SHOESIZE AS CHAR(2)
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.
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 1. CAST functions on distinct types Source type name Function name Parameter-type Return-type SMALLINT distinct-type-name SMALLINT distinct-type-name distinct-type-name INTEGER distinct-type-name SMALLINT distinct-type-name SMALLINT INTEGER distinct-type-name INTEGER distinct-type-name INTEGER distinct-type-name INTEGER BIGINT distinct-type-name BIGINT distinct-type-name BIGINT distinct-type-name BIGINT DECIMAL distinct-type-name DECIMAL (p,s) distinct-type-name DECIMAL distinct-type-name DECIMAL (p,s) NUMERIC distinct-type-name DECIMAL (p,s) distinct-type-name DECIMAL distinct-type-name DECIMAL (p,s) REAL distinct-type-name REAL distinct-type-name distinct-type-name DOUBLE distinct-type-name REAL distinct-type-name REAL DECFLOAT distinct-type-name DECFLOAT(n) DECFLOAT(n) DECFLOAT distinct-type-name DECFLOAT(n) FLOAT(n) where n<=21 distinct-type-name REAL distinct-type-name distinct-type-name DOUBLE distinct-type-name REAL distinct-type-name REAL FLOAT(n) where n>21 distinct-type-name DOUBLE distinct-type-name DOUBLE distinct-type-name DOUBLE FLOAT distinct-type-name DOUBLE distinct-type-name DOUBLE distinct-type-name DOUBLE DOUBLE distinct-type-name DOUBLE distinct-type-name DOUBLE distinct-type-name DOUBLE DOUBLE PRECISION distinct-type-name DOUBLE distinct-type-name distinct-type-name CHAR (n) distinct-type-name CHAR distinct-type-name CHAR (n) distinct-type-name VARCHAR (n) distinct-type-name DOUBLE distinct-type-name DOUBLE CHAR
CHARACTERdistinct-type-name CHAR (n) distinct-type-name CHAR distinct-type-name CHAR (n) distinct-type-name VARCHAR (n) distinct-type-name VARCHAR
CHARACTER VARYING
CHAR VARYINGdistinct-type-name VARCHAR (n) distinct-type-name VARCHAR distinct-type-name VARCHAR (n) CLOB distinct-type-name CLOB (n) distinct-type-name CLOB distinct-type-name CLOB (n) GRAPHIC distinct-type-name GRAPHIC (n) distinct-type-name GRAPHIC distinct-type-name GRAPHIC (n) distinct-type-name VARGRAPHIC (n) distinct-type-name VARGRAPHIC distinct-type-name VARGRAPHIC (n) distinct-type-name VARGRAPHIC distinct-type-name VARGRAPHIC (n) DBCLOB distinct-type-name DBCLOB (n) distinct-type-name DBCLOB distinct-type-name DBCLOB (n) BINARY distinct-type-name BINARY(n) distinct-type-name BINARY distinct-type-name BINARY(n) distinct-type-name VARBINARY(n) distinct-type-name VARBINARY distinct-type-name VARBINARY(n) distinct-type-name VARBINARY distinct-type-name VARBINARY(n) BLOB distinct-type-name BLOB (n) distinct-type-name BLOB distinct-type-name BLOB (n) DATE distinct-type-name DATE distinct-type-name DATE distinct-type-name DATE TIME distinct-type-name TIME distinct-type-name TIME distinct-type-name TIME TIMESTAMP distinct-type-name TIMESTAMP distinct-type-name TIMESTAMP distinct-type-name TIMESTAMP(p) WITHOUT TIME ZONE TIMESTAMP(p) WITH TIME ZONE distinct-type-name TIMESTAMP WITH TIME ZONE distinct-type-name TIMESTAMP_TZ distinct-type-name TIMESTAMP(p) WITH TIME ZONE ROWID distinct-type-name ROWID distinct-type-name ROWID distinct-type-name ROWID Notes: NUMERIC and FLOAT are not recommended when creating a distinct type for a portable application. Use DECIMAL and DOUBLE (or REAL) instead. - Built-in functions:
- 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. For information on defining sourced user-defined functions, see CREATE FUNCTION (sourced).
- Arithmetic operators with distinct type operands:
- A distinct type cannot be used with arithmetic operators even
if its source data type is numeric.
For additional information see Arithmetic with distinct type operands.
- Alternative syntax and synonyms:
- The WITH COMPARISONS clause, which specifies that system-generated
comparison operators are to be created for comparing two instances
of the distinct type, can be specified as the last clause of the statement.
Use WITH COMPARISONS only if it is required for compatibility with
other products in the DB2 family.
If the source data type is either BLOB, CLOB, or DBCLOB and WITH COMPARISONS
is specified, a warning occurs as in previous releases.To provide compatibility with previous releases of DB2 or other products in the DB2 family, DB2 supports the following clauses:
- DISTINCT TYPE as a synonym for TYPE
- TIMEZONE can be specified as an alternative to TIME ZONE
Examples
CREATE TYPE SHOESIZE AS INTEGER;
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 TYPE MILES AS DOUBLE;
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.