Distinct types and arrays in DB2 SQL

CREATE TYPE

CREATE TYPE registers a user-defined data type at the current server. The name, including schema, must not identify another built-in or user-defined type. Unqualified names on CREATE, DROP, GRANT, and REVOKE follow authorization-ID qualification. In other contexts Db2 finds the type via the SQL path.

Distinct types and strong typing

A distinct type shares its internal representation with a built-in source data type but is a separate and incompatible type for most operations. That is strong typing: two DECIMAL(9,2) values are not interchangeable if one is MONEY and the other is WEIGHT_KG. IBM’s classic example is three BLOB-based types—picture, document, audio—that must never be concatenated by accident.

1
2
3
CREATE TYPE HR.MONEY AS DECIMAL(9,2);
CREATE TYPE MEDIA.AUDIO AS BLOB(1M);
CREATE TYPE MEDIA.EMAIL_BODY AS CLOB(2M);

The source type carries length, precision, and scale. You do not redefine those on the distinct type later; you pick them at CREATE TYPE time.

What Db2 generates

• Comparison operators (=, <, >, and the rest) except when the source is CLOB, DBCLOB, or BLOB
• Cast functions both ways: source to distinct and distinct to source. For AUDIO based on BLOB you get AUDIO(blob) and BLOB(audio) style casts

You do not automatically get +, CONCAT, LENGTH, or other source-type functions. LENGTH of AUDIO might mean seconds of sound, not bytes of BLOB, so Db2 refuses to guess.

Distinct type casting and comparison

Assignment and comparison between a distinct type and its source type usually need an explicit CAST (or the generated cast function). Comparing two values of the same distinct type uses the generated comparison operators (non-LOB).

1
2
3
4
5
6
7
8
9
INSERT INTO HR.PAY (EMPNO, SALARY)
  VALUES ('000010', CAST(50000.00 AS HR.MONEY));
 
SELECT EMPNO
  FROM HR.PAY
 WHERE SALARY > CAST(40000.00 AS HR.MONEY);
 
-- Same distinct type compares without casting:
-- WHERE P1.SALARY > P2.SALARY

Distinct type functions

To add arithmetic or other operators, create a sourced user-defined function whose parameters are the distinct type and whose SOURCE is the built-in function.

1
2
3
4
5
CREATE FUNCTION HR."+" (HR.MONEY, HR.MONEY)
  RETURNS HR.MONEY
  SOURCE SYSIBM."+" (DECIMAL(), DECIMAL());
 
-- CAST(10.00 AS HR.MONEY) + CAST(2.50 AS HR.MONEY)  is then valid

Distinct type privileges

USAGE on the type is required to reference it in tables, functions, and CAST. Grant it to the IDs that create dependent objects:

1
2
GRANT USAGE ON TYPE HR.MONEY TO ROLE HR_DDL;
GRANT USAGE ON TYPE HR.MONEY TO PUBLIC;

CREATEIN on the schema creates the type; DROPIN drops it. Dropping fails while tables or routines still depend on the type.

Array types

An array is an ordered set of elements of one data type. A user-defined array type is created with CREATE TYPE ... ARRAY. You then DECLARE SQL variables or parameters of that type, or CREATE VARIABLE of that type. Array types are not ordinary base-table column types on z/OS. Persistent repeating groups still belong in child tables (or XML).

Ordinary arrays

An ordinary array has a maximum cardinality. Indexes are INTEGER positions starting at 1. If the current cardinality is n, valid ordinal positions are 1 through n. Maximum cardinality must be greater than 0 and at most 2147483647; that large value is the default if you omit the integer.

1
2
3
4
5
CREATE TYPE HR.PHONENUMBERS AS DECIMAL(16,0) ARRAY[50];
 
-- In SQL PL:
-- DECLARE PHONES HR.PHONENUMBERS;
-- SET PHONES[1] = 14085551212;

Varying-length string elements are allocated at their maximum length, so a VARCHAR(4000) ARRAY[1000] is not a cheap structure. Size ordinary arrays for real cardinality.

Associative arrays

An associative array is indexed by INTEGER or VARCHAR (not CLOB). Indexes are unique and need not be contiguous. There is no declared small maximum; the documented ceiling is on the order of two billion elements. Elements are ordered by index value.

1
2
3
4
CREATE TYPE HR.PERSONAL_PHONES AS DECIMAL(16,0) ARRAY[VARCHAR(8)];
 
-- Keys such as 'Home', 'Work', 'Cell'
-- SET PPHONES['Work'] = 14085551212;

Array cardinality and indexing

• CARDINALITY(array) — current number of elements
• MAX_CARDINALITY(array) — declared maximum for an ordinary array
• array[i] — ordinary element at position i (1-based)
• array[key] — associative element for that index

An element of a named array type can be referenced anywhere an expression of the element type is allowed. The result of ARRAY_AGG or an ARRAY constructor without a target type is an unnamed array; you cannot index it directly until you assign or CAST it to a user-defined array type.

ARRAY constructor

An ARRAY constructor builds an ordinary array from a list of expressions. Associative arrays are typically populated one element at a time by index, or with ARRAY_AGG that supplies index and value.

1
2
3
SET INTA = ARRAY[10, 20, 30];
 
SET INTA[4] = 40;   -- grows ordinary array cardinality when assignable

ARRAY_AGG

ARRAY_AGG is an aggregate function that builds an array from a set of rows. For ordinary arrays, use ORDER BY inside the function to control element order. For associative arrays, provide an index expression and a value expression when the target is an associative type.

1
2
3
4
5
6
7
8
9
10
11
12
13
-- Ordinary array of values (order defined):
SET INTARRAY = (SELECT ARRAY_AGG(VAL ORDER BY VAL)
                  FROM HR.CODES);
 
-- Associative array indexed by employee ID:
CREATE PROCEDURE HR.GETPHONES (OUT EMPLOYEES HR.EMPPHONES)
  LANGUAGE SQL
  BEGIN
    SELECT ARRAY_AGG(ID, PHONENUMBER)
      INTO EMPLOYEES
      FROM HR.EMPLOYEE
     WHERE PRIORITY = 1;
  END

You cannot SELECT a column “into an array” as if the column were an array type. Use ARRAY_AGG as the bridge from a table to an array variable.

UNNEST

UNNEST turns arrays into a result table in the FROM clause. Multiple arrays can be unnested together; Db2 aligns elements and produces columns you name in the correlation clause.

1
2
3
4
5
6
INSERT INTO HR.PERSONS (ID, NAME)
  SELECT T.I, T.N
    FROM UNNEST(IDS, NAMES) AS T(I, N);
 
SELECT T.VAL
  FROM UNNEST(DECIMALARRAY) AS T(VAL);

Array assignment and comparison

Assignment copies the whole array or a single element. The source must be assignable to the target type (same array kind, compatible element types, and for ordinary arrays a cardinality that fits the maximum). Element assignment uses the index on the left side.

Array-to-array comparison is not the same as comparing two integers. You typically UNNEST and compare elements, or compare CARDINALITY and then elements in SQL PL. Comparing two distinct-typed scalars still follows distinct-type rules if the element type is distinct.

Array parameters and host variables

SQL procedures and compiled SQL functions can declare IN, OUT, and INOUT parameters of a user-defined array type. Callers that are also SQL PL pass the array directly. Host languages do not treat a COBOL OCCURS table as a Db2 array column; they call a procedure that accepts the array parameter using the host-language array support documented for your Db2 version (assembler, C, COBOL, and Java have specific mappings). If your program cannot use array host variables, UNNEST into a declared global temporary table and process rows.

Explain It Like I'm Five

A distinct type is a sticker you put on a box. Two boxes can both be “decimal-shaped,” but one sticker says MONEY and the other says WEIGHT. You are not allowed to add them until you write a rule that says “money plus money is still money.” An ordinary array is a numbered list of lockers, starting at locker 1, with a maximum number of lockers. An associative array is a row of lockers with name tags (“Home,” “Work”) instead of only numbers. ARRAY_AGG packs table rows into lockers. UNNEST opens the lockers and lays the contents back out as table rows.

Exercises

1. Create distinct type USD AS DECIMAL(11,2) and a sourced “+” so USD + USD is legal.
2. Create an ordinary array type of INTEGER with maximum 10. In a procedure, ARRAY construct three values, print CARDINALITY, then UNNEST them.
3. Create an associative array type indexed by VARCHAR(10). Assign keys 'Home' and 'Work'. Explain why there is no element 2 unless you used an INTEGER index.
4. Write ARRAY_AGG that collects WORKDEPT values from HR.EMPLOYEE into an ordinary array ordered by EMPNO.
5. Attempt CREATE TABLE with an array-type column and record what Db2 returns. Why are arrays aimed at parameters and variables instead?