DB2 Version 9.7 for Linux, UNIX, and Windows

Comparison of arrays and associative arrays

Simple arrays and associative arrays differ in multiple ways. Understanding the differences can help you to choose the right data type to use.

The following table highlights the differences between arrays and associative arrays:
Table 1. Comparison of arrays and associative arrays
Arrays Associative arrays
The maximum cardinality of a simple array is defined when the simple array is defined. When a value is assigned to index N, the elements with indices between the current cardinality of the array and N are implicitly initialized to NULL. There is no user-specified maximum cardinality and no elements are initialized when an associative array variable is declared. The maximum cardinality is limited by the available free memory.
The index data type for a simple array must be an integer value. The index type for an associative array can be one of a set of supported data types.
The index values in a simple array must be a contiguous set of integer values. In an associative array the index values can be sparse.
The CREATE TYPE statement for a simple array does not require the specification of the array cardinality. For example, in this statement, no cardinality is specified: 
CREATE TYPE simple AS INTEGER ARRAY[];
 In the CREATE TYPE statement for an associative array, instead of requiring a specification of the array cardinality, the index data type is required. For example, in this statement, the cardinality for the index data type is specified as INTEGER: 
CREATE TYPE assoc AS INTEGER ARRAY[INTEGER];
A first assignment to a simple array results in the initialization of array elements with index values between 1 and the index value assigned to the array. The following compound SQL (compiled) statement contains the declaration of a simple array variable and the assignment of a value to the variable: 
BEGIN
 DECLARE mySimpleA simple;

  SET mySimpleA[100] = 123;              

END
After the execution of the assignment statement, the cardinality of mySimpleA is 100; the elements with indices with values 1 to 99 are implicitly initialized to NULL. 		A first assignment to an associative array results in the initialization of a single element with a single index value. The following compound SQL (compiled) statement contains the declaration of an associative array variable and the assignment of a value to the variable: 
BEGIN
  DECLARE myAssocA assoc;

  SET myAssocA[100] = 123;
END
After the execution of the assignment statement, the cardinality of the array is 1.

Example

Parent topic: Array types