CREATE TYPE (array) statement

The CREATE TYPE (array) statement defines an array type. The elements of an array type are based on one of the built-in data types or a user-defined distinct type.

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 (SQLSTATE 42509).

Authorization

The privileges held by the authorization ID of the statement must include at least one of the following authorities:
  • IMPLICIT_SCHEMA authority on the database, if the schema name of the array type does not refer to an existing schema
  • CREATEIN privilege on the schema, if the schema name of the array type refers to an existing schema
  • DBADM authority

Syntax

Read syntax diagramSkip visual syntax diagramCREATE OR REPLACE TYPEtype-nameASdata-type ARRAY[2147483647integer-constantdata-type2]
data-type
Read syntax diagramSkip visual syntax diagrambuilt-in-typeanchored-data-typerow-type-namearray-type-name
data-type2
Read syntax diagramSkip visual syntax diagram INTEGERINTVARCHARCHARACTERCHARVARYING(integerOCTETSCODEUNITS32)anchored-non-row-data-type
anchored-data-type
Read syntax diagramSkip visual syntax diagramANCHORDATA TYPE TO variable-nametable-name.column-nameROWOFtable-nameview-namecursor-variable-name
built-in-type
Read syntax diagramSkip visual syntax diagramSMALLINTINTEGERINTBIGINTDECIMALDECNUMERICNUM(5,0)( integer,0, integer)FLOAT(53)( integer)REALDOUBLEPRECISIONCHARACTERCHAR(1)( integerOCTETSCODEUNITS32)VARCHARCHARACTERCHARVARYING( integerOCTETSCODEUNITS32)FOR BIT DATA1CLOBCHARACTERCHARLARGE OBJECT(1M)( integerKMGOCTETSCODEUNITS32)GRAPHIC(1)( integerCODEUNITS16CODEUNITS32)VARGRAPHIC( integerCODEUNITS16CODEUNITS32)DBCLOB(1M)( integerKMGCODEUNITS16CODEUNITS32)NCHARNATIONALCHARCHARACTER(1)( integer)NVARCHARNCHAR VARYINGNATIONALCHARCHARACTERVARYING( integer)NCLOBNCHAR LARGE OBJECTNATIONAL CHARACTER LARGE OBJECT(1M)( integerKMG)BINARY(1)( integer)VARBINARYBINARY VARYING(integer)BLOBBINARY LARGE OBJECT(1M)( integerKMG)DATETIMETIMESTAMP(6)( integer)BOOLEAN
anchored-non-row-data-type
Read syntax diagramSkip visual syntax diagramANCHORDATA TYPETO variable-nametable-name.column-name
Notes:
  • 1 The FOR BIT DATA clause can be specified in any order with the other column constraints that follow. The FOR BIT DATA clause cannot be specified with string units CODEUNITS32 (SQLSTATE 42613).

Description

OR REPLACE
Specifies to replace the definition for the data type if one exists at the current server. The existing definition is effectively dropped before the new definition is replaced in the catalog, with the exception that functions and methods are invalidated instead of dropped when they have parameters or a return value defined with the data type being replaced. The existing definition must not be a structured type (SQLSTATE 42809). This option is ignored if a definition for the data type does not exist at the current server.
type-name
Names the type. The name, including the implicit or explicit qualifier, must not identify any other type (built-in or user-defined) that already exists at the current server. The unqualified name must not be the same as the name of a built-in data type or VARBINARY (SQLSTATE 42918).

A number of names used as keywords in predicates are reserved for system use, and cannot be used as a type-name (SQLSTATE 42939). The names are SOME, ANY, ALL, NOT, AND, OR, BETWEEN, NULL, LIKE, EXISTS, IN, UNIQUE, OVERLAPS, SIMILAR, MATCH, and the comparison operators.

If a two-part type-name is specified, the schema name must not begin with the characters 'SYS' (SQLSTATE 42939).

data-type
Specifies the data type of the array elements.
built-in-type
Specifies a built-in data type. See "CREATE TABLE" for the description of built-in data types. Built-in types include the data types described in CREATE TABLE, other than reference, SYSPROC.DB2SECURITYLABEL, XML, or user-defined types (SQLSTATE 429C2).
row-type-name
Specifies the name of a user-defined row type. If a row-type-name is specified without a schema name, the row-type-name is resolved by searching the schemas in the SQL path. Row types can be nested as elements in other array types with a maximum nesting level of sixteen.
array-type-name
Specifies an array type. If an array-type-name is specified without a schema name, the array-type-name is resolved by searching the schemas in the SQL path. Array types can be nested as elements in other array types with a maximum nesting level of sixteen.
anchored-data-type
Identifies another object used to determine the data type. The data type of the anchor object is bound by the same limitations that apply when specifying the data type directly, or in the case of a row, to creating a row type.
ANCHOR DATA TYPE TO
Indicates that an anchored data type is used to specify the data type.
variable-name
Identifies a global variable. The data type of the global variable is used as the data type for the array elements.
table-name.column-name
Identifies a column name of an existing table or view. The data type of the column is used as the data type for the array elements.
ROW OF table-name or view-name
Specifies a row of fields with names and data types that are based on the column names and column data types of the table identified by table-name or the view identified by view-name.The data type of the array elements is an unnamed row type.
ROW OF cursor-variable-name
Specifies a row of fields with names and data types that are based on the field names and field data types of the cursor variable identified by cursor-variable-name. The specified cursor variable must be one of the following elements (SQLSTATE 428HS):
  • A global variable with a strongly typed cursor data type
  • A global variable with a weakly typed cursor data type that was created or declared with a CONSTANT clause specifying a select-statement where all the result columns are named.
If the cursor type of the cursor variable is not strongly-typed using a named row type, the data type of the array elements is an unnamed row type.
anchored-non-row-data-type
Identifies another object used to determine the data type. The data type of the anchor object is bound by the same limitations that apply when specifying the data type directly.
ANCHOR DATA TYPE TO
Indicates that an anchored data type is used to specify the data type.
variable-name
Identifies a global variable with a data type that is an INTEGER or VARCHAR data type. The data type of the global variable is used as the data type for the array index.
table-name.column-name
Identifies a column name of an existing table or view with a data type that is an INTEGER or VARCHAR data type. The data type of the column is used as the data type for the array index.
ARRAY [integer-constant]
Specifies that the type is an array with a maximum cardinality of integer-constant. The value must be a positive integer (not zero) and less than the largest positive integer value (SQLSTATE 42820). The default is the largest positive integer value (2 147 483 647). The cardinality of an array value is determined by the highest element position assigned to the array value.

The maximum cardinality of an array on a given system is limited by the total amount of memory available to database applications. As such, although arrays of large cardinalities can be created, not all elements might be available for use.

ARRAY[data-type2]
Specifies that the type is an associative array that is indexed with values of data type data-type2. The data type must be either the INTEGER or VARCHAR data type (SQLSTATE 429C2). The values specified as the index when assigning 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 used when assigning array elements.

Rules

  • Use of anchored data types: An anchored data type cannot refer to the following objects (SQLSTATE 428HS): a nickname, typed table, typed view, statistical view that is associated with an expression-based index, declared temporary table, row definition that is associated with a weakly typed cursor, object with a code page or collation that is different from the database code page or database collation.

Notes

  • Array type usage: An array type can only be used as the data type of:
    • A local variable in a compound SQL (compiled) statement
    • A parameter of an SQL routine
    • A parameter of a Java™ procedure (non-nested ordinary arrays only)
    • The returns type of an SQL function
    • A global variable
  • A variable or parameter defined with an array type can only be used in compound SQL (compiled) statements

Examples

Example 1: Create an array type named PHONENUMBERS with a maximum of 50 elements that are of the DECIMAL(10, 0) data type.
   CREATE TYPE PHONENUMBERS AS DECIMAL(10,0)
     ARRAY[50]
Example 2: Create an array type named NUMBERS with the default number of elements in the schema GENERIC.
   CREATE TYPE GENERIC.NUMBERS AS DECFLOAT(34)
     ARRAY[]
Example 3: Create an associative array named PERSONAL_PHONENUMBERS with elements that are DECIMAL(16, 0) that is indexed by strings like 'Home', 'Work', or 'Mom'.
   CREATE TYPE PERSONALPHONENUMBERS AS DECIMAL(16, 0) ARRAY[VARCHAR(8)]
Example 4: Create an associative array type where the indexes are province, territory, or country names and the elements are capital cities:
   CREATE TYPE CAPITALSARRAY AS VARCHAR(30) ARRAY[VARCHAR(20)]
Example 5: Create an associative array type for product descriptions of up to 40 characters long, where the indexes are the product numbers, which are a maximum of 12 characters long:
   CREATE TYPE PRODUCTS AS VARCHAR(40) ARRAY[VARCHAR(12)]