CREATE TYPE statement (distinct type)

Invocation for CREATE TYPE (distinct)

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 in effect. For more information, see Authorization IDs and dynamic SQL.

Authorization for CREATE TYPE (distinct)

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
• Installation SYSOPR authority (when the current SQLID of the process is set to SYSINSTL)

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.

Description for CREATE TYPE (distinct)

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 privilege set includes the SYSADM or SYSCTRL privilege. Otherwise, the schema name must not begin with 'SYS' unless the schema name is 'SYSADM'.

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 in the range 0–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 in the range 0–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 for CREATE TYPE (distinct)

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 statement (type or JAR file privileges). The owner is also authorized to invoke the generated cast function (EXECUTE privilege; see GRANT statement (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.

Base data types with DBCS or mixed data:

When the implicit or explicit encoding scheme is ASCII or EBCDIC and the base 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 base data type
• A function to convert from the base 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 base data type S of distinct type DT
  For some base 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 …

In cases in which a length, precision, or scale is specified for the base type in the CREATE TYPE statement, the unqualified name of the cast function that converts from the distinct type to the base type is the name of the base 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 base data type on the CREATE TYPE statement. (See Table 1 for details.)

For example, assume that a distinct type named T_SHOESIZE is created with the following statement:

   CREATE TYPE 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 was based 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 base data type. For example, assume that T_SHOESIZE is created with this statement:

CREATE TYPE 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 base 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 CHARACTER	distinct-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 VARYING	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(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 statement (sourced function).

Arithmetic operators with distinct type operands:

A distinct type cannot be used with arithmetic operators even if its base 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 base 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

.