CREATE TYPE (array)
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
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 the package.
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 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
built-in-type:
data-type2:
Description
- 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
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'.
- 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
If 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.

- 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
- An SQL variable
- 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
CREATE TYPE PHONENUMBERS AS DECIMAL(10,0) ARRAY[50];CREATE TYPE GENERIC.NUMBERS AS DECFLOAT(34) ARRAY[];CREATE TYPE PERSONAL_PHONENUMBERS AS DECIMAL(16,0) ARRAY[VARCHAR(8)];CREATE TYPE CAPITALSARRAY AS VARCHAR(30) ARRAY[VARCHAR(20)];CREATE TYPE PRODUCTS AS VARCHAR(40) ARRAY[INTEGER];
