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 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
  • Start of changeDatabase administrator authorityEnd of change

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
  • Start of changeDatabase administrator authorityEnd of change

For information about the system authorities corresponding to SQL privileges, see Corresponding System Authorities When Checking Privileges to a Table or View.

Syntax

Read syntax diagramSkip visual syntax diagramCREATETYPEarray-type-nameASbuilt-in-type ARRAY[2147483647integer-constant]
built-in-type
Read syntax diagramSkip visual syntax diagramSMALLINTINTEGERINTBIGINTDECIMALDECNUMERICNUM(5,0)(integer,0, integer)FLOAT(53)(integer)REALDOUBLEPRECISIONDECFLOAT(34)(16)CHARACTERCHAR(1)(integer)CHARACTERCHARVARYINGVARCHAR(integer)FOR BIT DATAFOR SBCS DATAFOR MIXED DATAccsid-clauseCHARACTERCHARLARGE OBJECTCLOB(1M)(integerKM)FOR SBCS DATAFOR MIXED DATAccsid-clauseGRAPHIC(1)(integer)GRAPHIC VARYINGVARGRAPHIC(integer)DBCLOB(512K)(integerK)ccsid-clauseNATIONAL CHARACTERNATIONAL CHARNCHAR(1)(integer)NATIONAL CHARACTERNATIONAL CHARNCHARVARYINGNVARCHAR(integer)NATIONAL CHARACTERNCHARLARGE OBJECTNCLOB(512K)(integerK)normalize-clauseBINARY(1)(integer)BINARY VARYINGVARBINARY(integer)BLOBBINARY LARGE OBJECT(1M)(integerKM)DATETIME(0)TIMESTAMP(6)(integer)XMLccsid-clause
ccsid-clause
Read syntax diagramSkip visual syntax diagramCCSIDintegernormalize-clause
normalize-clause
Read syntax diagramSkip visual syntax diagramNOT NORMALIZEDNORMALIZED

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 FALSE POSITION XMLAGG
AND FOR RID XMLATTRIBUTES
ANY FROM RRN XMLCOMMENT
ARRAY_AGG HASHED_VALUE SELECT XMLCONCAT
BETWEEN IN SIMILAR XMLDOCUMENT
BOOLEAN INTERVAL SOME XMLELEMENT
CASE IS STRIP XMLFOREST
CAST LIKE SUBSTRING XMLGROUP
CHECK MATCH TABLE XMLNAMESPACES
DATAPARTITIONNAME NODENAME THEN XMLPARSE
DATAPARTITIONNUM NODENUMBER TRIM XMLPI
DBPARTITIONNAME NOT TRUE XMLROW
DBPARTITIONNUM NULL TYPE XMLSERIALIZE
DISTINCT ONLY UNIQUE XMLTEXT
EXCEPT OR UNKNOWN XMLVALIDATE
EXISTS OVERLAPS WHEN XSLTRANSFORM
EXTRACT PARTITION    

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 Start of changethreadEnd of change executing the statement.

If system names were specified, the owner of the array type is the user profile or group user profile of the Start of changethreadEnd of change 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[]