
The CREATE TYPE (Array) statement defines an array type at the current server.
This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared.
The privileges held by the authorization ID of the statement must include at least one of the following:
The privileges held by the authorization ID of the statement must include at least one of the following:
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:
For information about the system authorities corresponding to SQL privileges, see Corresponding System Authorities When Checking Privileges to a Table or View.
>>-CREATE--TYPE--array-type-name--------------------------------> .-2147483647-------. >--AS--built-in-type--ARRAY--[--+------------------+--]-------->< '-integer-constant-'
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------' | +-FOR MIXED DATA-+ | | | | '-VARCHAR----------------' '-ccsid-clause---' | | | | .-(--1M--)-------------. | | | '-----+-+-CHARACTER-+--LARGE OBJECT-+------+----------------------+--+----------------+-' | | | '-CHAR------' | '-(--integer--+---+--)-' +-FOR SBCS DATA--+ | | '-CLOB------------------------' +-K-+ +-FOR MIXED DATA-+ | | '-M-' '-ccsid-clause---' | | .-(--1--)-------. | +-+---GRAPHIC----+---------------+-------+--+--------------+-------------------------------------------+ | | '-(--integer--)-' | '-ccsid-clause-' | | +-+-GRAPHIC VARYING-+--(--integer--)---+ | | | '-VARGRAPHIC------' | | | | .-(--512K--)-----------. | | | '---DBCLOB----+----------------------+-' | | '-(--integer--+---+--)-' | | '-K-' | | .-(--1--)-------. | +-+-+-+-NATIONAL CHARACTER-+--+---------------+----------+---------------------+--+------------------+-+ | | | +-NATIONAL CHAR------+ '-(--integer--)-' | | '-normalize-clause-' | | | | '-NCHAR--------------' | | | | | '-+-+-NATIONAL CHARACTER-+--VARYING-+--(--integer--)-' | | | | | +-NATIONAL CHAR------+ | | | | | | '-NCHAR--------------' | | | | | '-NVARCHAR------------------------' | | | | .-(--512K--)-----------. | | | '-----+-+-NATIONAL CHARACTER-+--LARGE OBJECT-+------+----------------------+-' | | | '-NCHAR--------------' | '-(--integer--+---+--)-' | | '-NCLOB--------------------------------' '-K-' | | .-(--1--)-------. | +-+-+-BINARY--+---------------+---------+-----------------+--------------------------------------------+ | | | '-(--integer--)-' | | | | | '-+-BINARY VARYING-+--(--integer--)-' | | | | '-VARBINARY------' | | | | .-(--1M--)-------------. | | | '---+-BLOB----------------+----+----------------------+-' | | '-BINARY LARGE OBJECT-' '-(--integer--+---+--)-' | | +-K-+ | | '-M-' | +-+-DATE-------------------+---------------------------------------------------------------------------+ | | .-(--0--)-. | | | +-TIME--+---------+------+ | | | .-(--6--)-. | | | '-TIMESTAMP--+---------+-' | '---XML--+--------------+------------------------------------------------------------------------------' '-ccsid-clause-' ccsid-clause |--CCSID--integer--+------------------+-------------------------| '-normalize-clause-' normalize-clause .-NOT NORMALIZED-. |--+-NORMALIZED-----+-------------------------------------------|
If SQL names were specified, the array type will be created in the schema specified by the implicit or explicit qualifier.
If system names were specified, the array type will be created in the schema that is specified by the qualifier. If not qualified:
If the array 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.
| = | < | > | >= |
| <= | <> | ¬= | ¬< |
| ¬< | != | !< | !> |
| ALL | DISTINCT | NODENAME | SIMILAR |
| AND | EXCEPT | NODENUMBER | SOME |
| ANY | EXISTS | NOT | STRIP |
| ARRAY | EXTRACT | NULL | SUBSTRING |
| BETWEEN | FALSE | ONLY | TABLE |
| BOOLEAN | FOR | OR | THEN |
| CASE | FROM | OVERLAPS | TRIM |
| CAST | HASHED_VALUE | PARTITION | TRUE |
| CHECK | IN | POSITION | TYPE |
| DATAPARTITIONNAME | INTERVAL | RID | UNIQUE |
| DATAPARTITIONNUM | IS | RRN | UNKNOWN |
| DBPARTITIONNAME | LIKE | SELECT | WHEN |
| DBPARTITIONNUM | MATCH |
If a qualified array-type-name is specified, the schema name cannot be QSYS, QSYS2, QTEMP, or SYSIBM.
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 array type is for a string data type, a CCSID is associated with the array type at the time the array type is created. For more information about data types, see CREATE TABLE.
Additional generated functions: Functions are
created to convert to and from the array type, but service programs
are not created, so you cannot grant or revoke privileges to these
functions.
Names of the generated cast functions: The
unqualified name of the cast function that converts from the array
type is ARRAY. The name of the cast function that converts to the
array type is the name of the array type. The input parameter of the
cast function has the same data type as the ARRAY.
For example, assume that an array type named
T_SHOESIZES is created with the following statement: 
CREATE TYPE CLAIRE.T_SHOESIZES AS INT ARRAY[]

When the statement is executed, the database
manager also generates the following cast functions. ARRAY converts
from the array type to an array, and T_SHOESIZES converts from an
array to the array type.
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.
Array type attributes: An array type is created as a *SQLUDT object.
Array type ownership: If SQL names were specified:
If system names were specified, the owner of the array type is the user profile or group user profile of the job executing the statement.
Array type authority: If SQL names are used, array types are created with the system authority of *EXCLUDE on *PUBLIC. If system names are used, array types are created with the authority to *PUBLIC as determined by the create authority (CRTAUT) parameter of the schema.
If the owner of the array 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 array type.
Example 1: Create an array type named PHONENUMBERS with a maximum of 5 elements that are of the DECIMAL(10,0) data type.
CREATE TYPE PHONENUMBERS AS DECIMAL(10,0) ARRAY[5]
Example 2: Create an array type named NUMBERS in the schema GENERIC for which the maximum number of elements is not known.
CREATE TYPE GENERIC.NUMBERS AS BIGINT ARRAY[]
