CREATE TYPE (Array)
The CREATE TYPE (Array) statement defines an array type at the current server.
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.
Syntax
>>-CREATE--TYPE--array-type-name--------------------------------> .-2147483647-------. >--AS--built-in-type--ARRAY--[--+------------------+--]-------->< '-integer-constant-'
Description
- array-type-name
- Names
the array. 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 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 value of the CURRENT SCHEMA special register is *LIBL, the array type will be created in the current library (*CURLIB).
- Otherwise, the array type will be created in the current schema.
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.
array-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 FROM RRN XMLCONCAT ARRAY_AGG HASHED_VALUE SELECT XMLDOCUMENT BETWEEN IN SIMILAR XMLELEMENT BOOLEAN INTERVAL SOME XMLFOREST CASE IS STRIP XMLGROUP CAST LIKE SUBSTRING XMLNAMESPACES CHECK MATCH TABLE XMLPARSE DATAPARTITIONNAME NODENAME THEN XMLPI DATAPARTITIONNUM NODENUMBER TRIM XMLROW DBPARTITIONNAME NOT TRUE XMLSERIALIZE DBPARTITIONNUM NULL TYPE XMLTEXT DISTINCT ONLY UNIQUE XMLVALIDATE EXCEPT OR UNKNOWN XSLTRANSFORM EXISTS OVERLAPS WHEN If a qualified array-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 data type for all
the elements of the array. See CREATE TABLE for
a more complete description of each built-in data type.
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.
- ARRAY [integer-constant]
- Specifies that the array has a maximum cardinality
of integer-constant. The value must be a positive number greater
than 0. If no value is specified, the maximum integer value of 2147483647
is used. The maximum number of array elements that can be assigned
in the array is the number of elements that fit in 4 gigabytes. Each
varying length, LOB, and XML array element is allocated as its maximum
length.
Notes
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 one of the cast functions 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 an array type are
implicitly dropped when the array 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 a user profile with the same name as the schema into which the array type is created exists, the owner of the array type is that user profile.
- Otherwise, the owner of the array type is the user profile or group user profile of the job executing the statement.
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.
Examples
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[]