Start of change

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:

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.

Syntax

Read syntax diagramSkip visual syntax diagram
>>-CREATE--TYPE--array-type-name-------------------------------->

                                .-2147483647-------.      
>--AS--built-in-type--ARRAY--[--+------------------+--]--------><
                                '-integer-constant-'      

Read syntax diagramSkip visual syntax diagram
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-----+-------------------------------------------|

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 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.

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.

Notes

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

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

Start of changeFor example, assume that an array type named T_SHOESIZES is created with the following statement: End of change

Start of change
CREATE TYPE CLAIRE.T_SHOESIZES AS INT ARRAY[]
End of change

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

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

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.

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[]
End of change