Blog
DB2utor

Categories

March 11, 2014

Using Arrays

Last week I discussed the new user-defined ARRAY data type in DB2 11. This is the first DB2 for z/OS release to support arrays, although DB2 for Linux, UNIX and Windows has supported arrays since DB2 V9.5 (October 2007).

The DB2 11 for z/OS SQL Reference Guide lists and defines the SQL built-in functions that can be used to assign values as well as retrieve first, last and next values. It also includes numerous examples that show how to assign values to an array. I'll cite two here:

 

            Example 1: Suppose that the array variable RECENT_CALLS has the array type

            PHONENUMBERS. Assign an array of fixed numbers to RECENT_CALLS.

 

            SET RECENT_CALLS = ARRAY[9055553907, 4165554213, 4085553678];

 

            Example 2: Suppose that the array variable DEPT_PHONES has the array type             PHONENUMBERS. Assign array phone numbers that are retrieved from the  DEPARTMENT_INFO table to DEPT_PHONES.

            SET DEPT_PHONES =

              ARRAY[SELECT DECIMAL(AREA_CODE CONCAT ’555’ CONCAT EXTENSION,16)

                            FROM DEPARTMENT_INFO

                            WHERE DEPTID = 624];

 

Java developers should review the DB2 11 for z/OS Application Programming and Reference for Java manual. As you'd expect from the title, this manual provides examples usage in Java. The DB2 11 z/OS Application Programming and SQL Guide covers other languages like C, COBOL and REXX.

Another resource on arrays is the IBM Redbook, "DB2 11 for z/OS Technical Overview." Included in this publication is the following example that illustrates how to use the ARRAY_AGG function to return an array of employee phone numbers:

 

                CREATE TYPE PHONELIST AS CHAR(4) ARRAY[];

                CREATE FUNCTION PHONELIST_UDF (LOWSAL DECIMAL(9,2))

                    RETURNS PHONELIST

                    LANGUAGE SQL

                    CONTAINS SQL

                    NO EXTERNAL ACTION

                    RETURN

                    (SELECT ARRAY_AGG(PHONENO ORDER BY SALARY)
                     FROM DSN81110.EMP WHERE SALARY > LOWSAL)

 

Do you have a creative use for arrays? Please share your ideas in comments.