CREATE TYPE statement (array type)

The CREATE TYPE (array) statement defines an array type at the current server. An array type is a user-defined data type that is an ordinary array or an associative array. The elements of an array type are based on one of the built-in data types.

Invocation for CREATE TYPE (array)

This statement can be embedded in an application program or issued through the use of dynamic SQL statements. 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 (array)

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

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 statement is running under a trusted context for which the ROLE AS OBJECT OWNER clause is specified, 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 for CREATE TYPE (array)

Read syntax diagramSkip visual syntax diagramCREATETYPEarray-type-name ASbuilt-in-typeARRAY[ 2147483647integer-constantdata-type2 ]

built-in-type:

Read syntax diagramSkip visual syntax diagramSMALLINTINTEGERINTBIGINTDECIMALDECNUMERIC(5,0)( integer, integer)FLOAT(53)( integer)REALDOUBLEPRECISIONDECFLOAT(34)(16)CHARACTERCHAR(1)( integer)CHARACTERCHARVARYINGVARCHAR( integer)CCSIDASCIIEBCDICUNICODEFORSBCSMIXEDBITDATACHARACTERCHARLARGE OBJECTCLOB(1M)( integerKMG)CCSIDASCIIEBCDICUNICODEFORSBCSMIXEDDATAGRAPHIC(1)( integer)VARGRAPHIC(integer)DBCLOB(1M)( integerKMG)CCSIDASCIIEBCDICUNICODEBINARY(1)( integer)BINARY VARYINGVARBINARY( integer)BINARY LARGE OBJECTBLOB(1M)( integerKMG)DATETIMETIMESTAMP(6)(integer)WITHOUT TIME ZONEWITH TIME ZONE

data-type2:

Read syntax diagramSkip visual syntax diagramINTEGERINTVARCHARCHARACTERCHARVARYING( integer)CCSIDASCIIEBCDICUNICODEFORSBCSMIXEDBITDATA

Description for CREATE TYPE (array)

array-type-name
Names the array type. The name, including the implicit or explicit qualifier, must not identify any other built-in or user-defined type that exists at the current server.

The unqualified form of array-type-name must not be any of the 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

Start of changeThe 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'.End of change

built-in-type
Specifies the built-in data type of the array elements. The data type must not be ROWID or XML. For more information on built-in data types, see built-in-type in CREATE TABLE.
CCSID ASCII, EBCDIC, or UNICODE in a built-in-type specification
Start of changeIf the data type is a character or graphic string and a CCSID clause is not specified for built-in-type, the default CCSID for built-in-type is determined as follows:
  • If data-type2 is a character string data type with an explicit CCSID clause, that same CCSID value is used for built-in-type.
  • If data-type2 is a character string data type without an explicit CCSID clause, the CCSID for built-in-type is determined from the encoding scheme that is indicated by the value of field DEF ENCODING SCHEME on installation panel DSNTIPF.

If a CCSID clause is specified for built-in-type and for data-type2, the CCSID values must be the same.

End of change
FOR SBCS, MIXED, or BIT DATA in a built-in-type specification
Specifies a subtype for a character string data type (VARCHAR). Do not use this clause with any other data type.
SBCS
Single-byte data.
MIXED
Mixed data. Do not specify MIXED if the value of field MIXED DATA on installation panel DSNTIPF is NO unless the CCSID UNICODE clause is also specified.
BIT
Bit data.
If you do not specify the FOR SBCS DATA, FOR MIXED DATA, or FOR BIT DATA clause, the default value is determined as follows:
  • 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 data, the default subtype is MIXED.
ARRAY[integer-constant]
Specifies that the type is an ordinary array with a maximum cardinality of integer-constant. The value must be an integer that is greater than 0 and less than or equal to the largest positive integer value (2147483647). The default is 2147483647. Each varying-length string array element is allocated as its maximum length.

The cardinality of an array value is determined by the highest element position that is assigned to the array value. The maximum cardinality of an array is limited by the total amount of memory that is available to Db2 applications. Therefore, although an array with a large cardinality can be created, not all elements might be available for use. An attempt to assign a value to an array element when there is not enough memory results in an error.

ARRAY[data-type2]
Specifies that the type is an associative array that is indexed by values of data type data-type2. The data type must be the INTEGER or VARCHAR data type. The value that is specified as the index during assignment of a value to an array element must be assignable to a value of data-type2.

The cardinality of an array value is determined by the number of unique index values that are used when during assignment of array elements.

CCSID ASCII, EBCDIC, or UNICODE in a data-type2 specification
If the data type is a character string, and a CCSID clause is not specified for data-type2, the default CCSID is determined as follows:
  • If built-in-type is a character string data type with an explicit CCSID clause, that same CCSID value is used for data-type2.
  • If built-in-type is a character string data type without an explicit CCSID clause, the CCSID for data-type2 is determined from the encoding scheme that is indicated by the value of field DEF ENCODING SCHEME on installation panel DSNTIPF.

If a CCSID clause is specified for built-in-type and for data-type2, the CCSID values must be the same.

FOR SBCS, MIXED, or BIT DATA in a data-type2 specification
Specifies a subtype for a character string data type (VARCHAR). Do not use this clause with any other data type.
SBCS
Single-byte data.
MIXED
Mixed data. Do not specify MIXED if the value of field MIXED DATA on installation panel DSNTIPF is NO unless the CCSID UNICODE clause is also specified.
BIT
Bit data.
If you do not specify the FOR SBCS DATA, FOR MIXED DATA, or FOR BIT DATA clause, the default value is determined as follows:
  • 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 data, the default subtype is MIXED.

Notes for CREATE TYPE (array)

Array type usage: A user-defined array type can only be used as the data type of:
  • An SQL variable
  • Start of changeA global variableEnd of change
  • A parameter or RETURNS data-type of an SQL scalar function
  • A parameter of a native SQL procedure
  • The target data type for a CAST specification

Generated cast functions: The successful execution of the CREATE TYPE (array) statement causes the Db2 database manager to generate cast functions for the user-defined array type. Those cast functions are recorded in the Db2 catalog. The unqualified names of the two cast functions are ARRAY and the name of the array type. A generated cast function cannot be explicitly dropped. The cast functions that are generated for an array type are implicitly dropped when the array type is dropped with the DROP statement.

Examples for CREATE TYPE (array)

Example 1
Create an ordinary array user-defined type named PHONENUMBERS, with a maximum of 50 elements. The elements are of the DECIMAL(10,0) data type.
CREATE TYPE PHONENUMBERS AS DECIMAL(10,0) ARRAY[50];
Example 2
Create an ordinary array user-defined type named NUMBERS, in the schema GENERIC. You do not know the maximum number of elements, so you use the default value. The elements are of the DECFLOAT(34) data type.
CREATE TYPE GENERIC.NUMBERS AS DECFLOAT(34) ARRAY[];
Example 3
Create an associative array user-defined type named PERSONAL_PHONENUMBERS. The elements are of the DECIMAL(16, 0) data type. The array type is indexed by strings such as 'Home', 'Work', or 'Cell', so the index data type must be VARCHAR.
CREATE TYPE PERSONAL_PHONENUMBERS AS DECIMAL(16,0) ARRAY[VARCHAR(8)];
Example 4
Create an associative array user-defined type named CAPITALSARRAY. The elements are capital cities. The index values are province, territory, or country names, so the index data type must be VARCHAR.
CREATE TYPE CAPITALSARRAY AS VARCHAR(30) ARRAY[VARCHAR(20)];
Example 5
Create an associative array user-defined type named PRODUCTS. The elements are product descriptions of up to 40 characters. The index values are product numbers, which have the INTEGER data type.
CREATE TYPE PRODUCTS AS VARCHAR(40) ARRAY[INTEGER];