March 04, 2014

Array Support in DB2 11

DB2 11 brings many new SQL processing enhancements that are designed to simplify application coding. One such enhancement is support for arrays. An array is a user-defined data type that consists of an ordered set of elements of a single built-in data type. Elements can be accessed and modified by their index position. An array type is created with an SQL CREATE TYPE (array) statement. An array type can be used as a parameter of a procedure and as a variable in an SQL procedure.

Here's the syntax for creating a user-defined array:

        >>-CREATE--TYPE--array-type-name--AS--| built-in-type |--------->





              '-| data-type2 |---'  

There are two array types: ordinary or associative. An ordinary array type is defined using the ARRAY(integer-constant) syntax. The maximum cardinality is based on the value integer-constant. The value must be an integer between 0 and positive 2147483647. The maximum value is the default size (2147483647).

Here's an example of an ordinary array user-defined type. The company-name element can be up to 100 characters in length, while the company-name array can contain up to 75 elements.

            CREATE TYPE company-name AS VARCHAR(100) ARRAY(75)

ARRAY(data-type2) is an associative array that's indexed by data-type2. The data type must be either INTEGER or VARCHAR, and the value assigned to the index must be of the same data type. Each index is unique, and as a new index is assigned, the cardinality is incremented by 1. The maximum cardinality of an array is limited by the total amount of memory available to DB2 applications.

For more information on ARRAY TYPE, see the DB2 11 for z/OS SQL Reference Guide.

Next week I'll explain how user-defined data types (ARRAY TYPE) can be used.