CREATE TYPE (Distinct)
The CREATE TYPE (Distinct) statement defines a distinct type at the current server. A distinct type is always sourced on one of the built-in data types.
Successful execution of the statement also generates:
- A function to cast from the distinct type to its source type
- A function to cast from the source type to 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.
Authorization
The privileges held by the authorization ID of the statement must include at least one of the following:
- The privilege to create in the schema. For more information, see Privileges necessary to create in a schema.
- 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
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
For information about the system authorities corresponding to SQL privileges, see Corresponding System Authorities When Checking Privileges to a Table or View.
built-in-type |--+-+---SMALLINT---+----------------------------------------------------------------------------------------------------------+--| | +-+-INTEGER-+--+ | | | '-INT-----' | | | '---BIGINT-----' | | .-(5,0)------------------------. | +-+-+-DECIMAL-+-+--+------------------------------+-------------------------------------------------------------------------+ | | '-DEC-----' | | .-,0--------. | | | '-+-NUMERIC-+-' '-(--integer--+-----------+--)-' | | '-NUM-----' '-, integer-' | | .-(--53--)------. | +-+-FLOAT--+---------------+-+----------------------------------------------------------------------------------------------+ | | '-(--integer--)-' | | | +-REAL---------------------+ | | | .-PRECISION-. | | | '-DOUBLE--+-----------+----' | | .-(--34--)-. | +---DECFLOAT--+----------+--------------------------------------------------------------------------------------------------+ | '-(--16--)-' | | .-(--1--)-------. | +-+-+-+-CHARACTER-+--+---------------+-------------------------------+--+----------------+---------------------+------------+ | | | '-CHAR------' '-(--integer--)-' | +-FOR BIT DATA---+ | | | | '-+-+-CHARACTER-+--VARYING-+--(--integer--)--+-----------------+-' +-FOR SBCS DATA--+ | | | | | '-CHAR------' | '-allocate-clause-' +-FOR MIXED DATA-+ | | | | '-VARCHAR----------------' '-ccsid-clause---' | | | | .-(--1M--)-------------. | | | '-----+-+-CHARACTER-+--LARGE OBJECT-+------+----------------------+--+-----------------+--+----------------+-' | | | '-CHAR------' | '-(--integer--+---+--)-' '-allocate-clause-' +-FOR SBCS DATA--+ | | '-CLOB------------------------' +-K-+ +-FOR MIXED DATA-+ | | +-M-+ '-ccsid-clause---' | | '-G-' | | .-(--1--)-------. | +-+---GRAPHIC----+---------------+----------------------------+--+--------------+-------------------------------------------+ | | '-(--integer--)-' | '-ccsid-clause-' | | +-+-GRAPHIC VARYING-+--(--integer--)--+-----------------+---+ | | | '-VARGRAPHIC------' '-allocate-clause-' | | | | .-(--1M--)-------------. | | | '---DBCLOB----+----------------------+--+-----------------+-' | | '-(--integer--+---+--)-' '-allocate-clause-' | | +-K-+ | | +-M-+ | | '-G-' | | .-(--1--)-------. | +-+-+-+-NATIONAL CHARACTER-+--+---------------+-------------------------------+---------------------+--+------------------+-+ | | | +-NATIONAL CHAR------+ '-(--integer--)-' | | '-normalize-clause-' | | | | '-NCHAR--------------' | | | | | '-+-+-NATIONAL CHARACTER-+--VARYING-+--(--integer--)--+-----------------+-' | | | | | +-NATIONAL CHAR------+ | '-allocate-clause-' | | | | | '-NCHAR--------------' | | | | | '-NVARCHAR------------------------' | | | | .-(--1M--)-------------. | | | '-----+-+-NATIONAL CHARACTER-+--LARGE OBJECT-+------+----------------------+--+-----------------+-' | | | '-NCHAR--------------' | '-(--integer--+---+--)-' '-allocate-clause-' | | '-NCLOB--------------------------------' +-K-+ | | +-M-+ | | '-G-' | | .-(--1--)-------. | +-+-+-BINARY--+---------------+------------------------------+-----------------+--------------------------------------------+ | | | '-(--integer--)-' | | | | | '-+-BINARY VARYING-+--(--integer--)--+-----------------+-' | | | | '-VARBINARY------' '-allocate-clause-' | | | | .-(--1M--)-------------. | | | '---+-BLOB----------------+----+----------------------+--+-----------------+-' | | '-BINARY LARGE OBJECT-' '-(--integer--+---+--)-' '-allocate-clause-' | | +-K-+ | | +-M-+ | | '-G-' | +-+-DATE-------------------+------------------------------------------------------------------------------------------------+ | | .-(--0--)-. | | | +-TIME--+---------+------+ | | | .-(--6--)-. | | | '-TIMESTAMP--+---------+-' | | .-(--200--)-----. | +---DATALINK--+---------------+--+-----------------+--+--------------+------------------------------------------------------+ | '-(--integer--)-' '-allocate-clause-' '-ccsid-clause-' | +---ROWID-------------------------------------------------------------------------------------------------------------------+ '---XML--+-----------------+--+--------------+------------------------------------------------------------------------------' '-allocate-clause-' '-ccsid-clause-' ccsid-clause |--CCSID--integer--+------------------+-------------------------| '-normalize-clause-' normalize-clause .-NOT NORMALIZED-. |--+-NORMALIZED-----+-------------------------------------------|
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 or array type that already exists at the current
server. If SQL names were specified, the distinct type will be created in the schema specified by the implicit or explicit qualifier.
If system names were specified, the distinct type will be created in the schema that is specified by the qualifier. If not qualified:
- If the value of the CURRENT SCHEMA special register is *LIBL, the distinct type will be created in the current library (*CURLIB).
- Otherwise, the distinct type will be created in the current schema.
If the distinct type name is not a valid system name, DB2® for i will generate a system name. For information about 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 EXTRACT PARTITION XMLAGG AND FALSE POSITION XMLATTRIBUTES ANY FOR RID XMLCOMMENT ARRAY_AGG FROM RRN XMLCONCAT BETWEEN HASHED_VALUE SELECT XMLDOCUMENT BOOLEAN IN SIMILAR XMLELEMENT CASE INTERVAL SOME XMLFOREST CAST IS STRIP XMLGROUP CHECK LIKE SUBSTRING XMLNAMESPACES DATAPARTITIONNAME MATCH TABLE XMLPARSE DATAPARTITIONNUM NODENAME THEN XMLPI DBPARTITIONNAME NODENUMBER TRIM XMLROW DBPARTITIONNUM NOT TRUE XMLSERIALIZE DISTINCT NULL TYPE XMLTEXT EXCEPT ONLY UNIQUE XMLVALIDATE EXISTS OR UNKNOWN XSLTRANSFORM OVERLAPS WHEN If a qualified distinct-type-name is specified, the schema name cannot be QSYS, QSYS2, QTEMP, or SYSIBM.
- built-in-type
- Specifies the built-in
data type used as the basis for the internal representation of the
distinct type. See CREATE TABLE for a more
complete description of each built-in data type.
For portability of applications across platforms, use the following recommended data type names:
- DOUBLE or REAL instead of FLOAT.
- DECIMAL instead of NUMERIC.
If a specific value is not specified for the data types that have length, precision, or scale attributes, the default attributes of the data type as shown in the syntax diagram are implied.
If the distinct type is sourced on a string data type, a CCSID is associated with the distinct data type at the time the distinct type is created. For more information about data types, see CREATE TABLE.
Notes
Additional generated functions: Besides the system-generated comparison operators described above, the following functions become available to convert to and from the source type:
- The distinct type to the source type
- The source type to the distinct type
- INTEGER to the distinct type if the source type is SMALLINT
- DOUBLE to the distinct type if the source type is REAL
- VARCHAR to the distinct type if the source type is CHAR
- VARGRAPHIC to the distinct type if the source type is GRAPHIC
These functions are created as if the following statements were executed (except that the service programs are not created, so you cannot grant or revoke privileges to these functions):
CREATE FUNCTION source-type-name (distinct-type-name)
RETURNS source-type-name
CREATE FUNCTION distinct-type-name (source-type-name)
RETURNS distinct-type-nameNames of the generated cast functions: Table 1 contains details about the generated cast functions. The unqualified name of the cast function that converts from the distinct type to the source type is the name of the source data type.
In cases in which a length, precision, or scale is specified for the source data type in the CREATE TYPE statement, the unqualified 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 were specified for the source data type on the CREATE TYPE statement.
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.
The cast functions that are generated are created in the same schema as that of the distinct type. A function with the same name and same function signature must not already exist in the current server.
For example, assume that a distinct type named T_SHOESIZE is created with the following statement:
CREATE TYPE CLAIRE.T_SHOESIZE AS VARCHAR(2) WITH COMPARISONSWhen the statement is executed, the database manager 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_SHOESIZENotice 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).
A generated cast function cannot be explicitly dropped. 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.
| 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 | NUMERIC(p,s) | distinct-type-name |
| NUMERIC | distinct-type-name | NUMERIC(p,s) | |
| REAL or FLOAT(n) where n <= 24 | distinct-type-name | REAL | distinct-type-name |
| distinct-type-name | DOUBLE | distinct-type-name | |
| REAL | distinct-type-name | REAL | |
| DOUBLE or DOUBLE PRECISION or FLOAT(n) where n > 24 | distinct-type-name | DOUBLE | distinct-type-name |
| DOUBLE | distinct-type-name | DOUBLE | |
| DECFLOAT | distinct-type-name | DECFLOAT(n) | distinct-type-name |
| DECFLOAT | distinct-type-name | DECFLOAT(n) | |
| CHAR | distinct-type-name | CHAR(n) | distinct-type-name |
| CHAR | distinct-type-name | CHAR(n) | |
| distinct-type-name | VARCHAR(n) | distinct-type-name | |
| VARCHAR | distinct-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 | |
| DATALINK | distinct-type-name | DATALINK | distinct-type-name |
| DATALINK | distinct-type-name | DATALINK | |
| ROWID | distinct-type-name | ROWID | distinct-type-name |
| ROWID | distinct-type-name | ROWID |
NUMERIC and FLOAT are not recommended when creating a distinct type for a portable application. DECIMAL and DOUBLE should be used instead.
Built-in functions: The functions described in the above table are the only functions that are generated automatically when distinct types are defined. Consequently, none of the built-in functions (AVG, MAX, LENGTH, and so on) are automatically supported for the distinct type. A built-in function can be used 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. See Extending or overriding a built-in function:.
The schema name of the distinct type must be included in the distinct type for successful use of these operators and cast functions in SQL statements.
Distinct type attributes: A distinct type is created as a *SQLUDT object.
Distinct type ownership: If SQL names were specified:
- If a user profile with the same name as the schema into which the distinct type is created exists, the owner of the distinct type is that user profile.
- Otherwise, the owner of the distinct type is the user profile or group user profile of the job executing the statement.
If system names were specified, the owner of the distinct type is the user profile or group user profile of the job executing the statement.
Distinct type authority: If SQL names are used, distinct types are created with the system authority of *EXCLUDE on *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 schema.
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.
Syntax alternatives: 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.
- CREATE DISTINCT TYPE can be specified in place of CREATE TYPE.
Examples
Example 1: Create a distinct type named SHOESIZE that is sourced on the built-in INTEGER data type.
CREATE 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 the built-in DOUBLE data type.
CREATE 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.
Example 3: Create a distinct type T_DEPARTMENT that is sourced on the built-in CHAR data type.
CREATE TYPE CLAIRE.T_DEPARTMENT AS CHAR(3)
WITH COMPARISONSThe successful execution of this statement also generates three cast functions:
- Function CLAIRE.CHAR takes a T_DEPARTMENT as input and returns a value with data type CHAR(3).
- Function CLAIRE.T_DEPARTMENT takes a CHAR(3) as input and returns a value with distinct type T_DEPARTMENT.
- Function CLAIRE.T_DEPARTMENT takes a VARCHAR(3) as input and returns a value with distinct type T_DEPARTMENT.