Thursday, June 08, 2017

The DB2 12 for z/OS Blog Series - Part 11: Enhanced Support for Arrays

The ARRAY data type was added to DB2 in the last release (Version 11) with the ability to define both ordinary arrays or associative arrays. An ordinary array has a user-defined number of elements that are referenced by their ordinal position in the array. An associative array has no user-defined number of elements that are referenced by the array index value. An associative array’s index values do not have to be contiguous but they are unique. SQL PL variables and parameters for SQL PL routines could be defined as arrays. 

Support for global variables was also added to DB2 11 for z/OS, but they could not be defined as an ARRAY. With DB2 12 for z/OS you can create global variables with an array data type. So the following is now legal as long as you are on V12 or higher:

  CREATE TYPE IntgrArray AS INTEGER ARRAY[5]
  ...
  CREATE VARIABLE IntgrArrayGV IntgrArray

A data type is defined as an integer array and a global variable is created using that data type.

Additional enhancements for array handling added to DB2 12 include the ability to use the ARRAY_AGG aggregate function to create an associative array... and  you can specify the ORDER BY clause on the ARRAY_AGG aggregate function (as an option). The ARRAY_AGG function enables your programs to utilize arrays without having to code SQL PL in stored procedures or triggers.