Distinct types and arrays in DB2 for z/OS

User-defined types at a glance

Db2 ships IBM-supplied built-in data types. A user-defined type (UDT) is a named type you create with CREATE TYPE. On Db2 for z/OS the two CREATE TYPE forms you will meet first are:

• Distinct types — a new name whose internal representation is a built-in source type
• Array types — an ordered collection of elements of one built-in (or distinct) element type, either ordinary or associative

Distinct types usually live on columns, parameters, and variables. Array types usually live on SQL variables, SQL parameters, and global variables. Mixing those mental models is the most common beginner mistake: an array is a handy in-memory (or procedure-scoped) collection, not a replacement for a child table of order lines.

Distinct types

A distinct type shares its internal representation with a built-in source type but is considered a separate and incompatible data type for most operations. That is strong typing: two values that happen to be DECIMAL(9,2) in storage are not interchangeable if one is MONEY and the other is WEIGHT_KG.

IBM’s classic motivation is three BLOB-based types that must never be mixed: a picture, a text document, and an audio clip can all be BLOB internally, but concatenating audio bytes onto a JPEG is not a meaningful operation. Distinct types make that mix-up a compile-time or bind-time error instead of a silent data-quality bug.

Creating a distinct type

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

Rules that matter in practice:

• The name, including schema, must not collide with another built-in or user-defined type at the current server
• The source type is a built-in type (with length, precision, and scale as required)
• Privileges on the type are separate from privileges on tables that use it; GRANT USAGE on the type is part of real-world rollout
• Dropping a type fails while dependent objects still use it—plan DROP TYPE like DROP TABLE

What Db2 generates for you

You do not get every source-type operator for free. A LENGTH function on AUDIO might reasonably mean seconds of sound, not bytes of BLOB, so Db2 refuses to guess. What it does generate:

• Comparison operators (=, <, >, and the rest) for distinct types except those based on CLOB, DBCLOB, or BLOB
• Cast functions both ways: from the source type to the distinct type, and from the distinct type back to the source type. For AUDIO based on BLOB, you get AUDIO(blob-value) and BLOB(audio-value) style casts

Assignment and comparison between a distinct type and its source type usually require an explicit CAST (or the generated cast function). That is the point of strong typing. Function resolution also treats the distinct type as different from the source type, which is why CAST and the SQL path show up together when you debug “function not found” errors.

Adding operators with sourced functions

To apply a source-type operator to a distinct type, create a sourced user-defined function whose parameters are the distinct type and whose source is the built-in function or operator. Example: allow MONEY + MONEY by sourcing on DECIMAL addition.

1
2
3
4
5
6
7
8
9
CREATE TYPE MONEY AS DECIMAL(9,2);
 
CREATE FUNCTION "+" (MONEY, MONEY)
  RETURNS MONEY
  SOURCE SYSIBM."+" (DECIMAL(), DECIMAL());
 
-- After the sourced function exists:
--   CAST(10.00 AS MONEY) + CAST(2.50 AS MONEY)
-- is valid; mixing MONEY with a bare DECIMAL still needs a cast.

The same pattern works for CONCAT on string-based distinct types: create a function sourced on CONCAT (or overload the || operator name) instead of hoping VARCHAR concatenation applies automatically. It does not.

Distinct types based on LOBs inherit the usual LOB restrictions: you cannot feed them to functions that reject the source LOB type, and you typically manipulate them with locators, file reference variables, or explicit casts—not with ordinary VARCHAR host variables.

When to use a distinct type

• Prevent unit mix-ups — currency vs quantity, kilometres vs miles, account-id CHAR vs customer-code CHAR
• Document intent — EMAIL_BODY AS CLOB says more in SYSTABLES-adjacent thinking than “another CLOB”
• Control operations — only the functions you source are legal

Do not create a distinct type for every column. Each type is another object to GRANT, DROP, and explain to the next developer. Use them where mixing values would be a real business error.

Array types

A user-defined array type is a data type defined as an array of elements. An array value is an ordered collection. Arrays make it easier to exchange lists with SQL PL routines without staging rows in a declared global temporary table every time.

Two shapes exist:

• Ordinary array — maximum cardinality on CREATE TYPE; INTEGER index is the ordinal position (1 through current cardinality)
• Associative array — indexed by INTEGER or VARCHAR; no predefined upper bound other than a very large maximum (on the order of two billion elements); indexes are unique and need not be contiguous

Elements are based on a built-in type (or, depending on product rules, a distinct type). You cannot nest arrays as element types in the ordinary beginner cases—think “array of DECIMAL,” not “array of array.”

Ordinary arrays

CREATE TYPE with ARRAY[integer-constant] (or ARRAY[] using the default maximum) defines an ordinary array. The constant must be greater than 0 and at most the largest positive INTEGER (2147483647). The default maximum cardinality is that same INTEGER maximum. Each varying-length string element is allocated at its maximum length, which is why huge VARCHAR elements times huge cardinality is a memory conversation with your DBA, not a free lunch.

1
2
3
4
5
6
7
8
9
10
11
CREATE TYPE PHONENUMBERS AS DECIMAL(10,0) ARRAY[50];
 
-- SQL PL sketch: parameter and element assignment (1-based index)
CREATE PROCEDURE APP.COPY_FIRST_PHONE(
    IN  IN_PHONES  PHONENUMBERS,
    OUT OUT_PHONES PHONENUMBERS
  )
  LANGUAGE SQL
BEGIN
  SET OUT_PHONES[1] = IN_PHONES[1];
END;

If n is the current number of elements, valid ordinary-array indexes are integers from 1 through n. Assigning to position n+1 can extend the array (within max cardinality). Gaps are not the ordinary array model—that is what associative arrays are for. Functions such as TRIM_ARRAY drop trailing elements; CARDINALITY reports how many elements exist now; MAX_CARDINALITY reports the declared ceiling.

Ordinary arrays are the right default when the list is “the next 50 phone numbers in order” or “up to 12 monthly amounts.” Subscripts feel like COBOL OCCURS indexes, which is why application programmers usually learn this form first.

Associative arrays

CREATE TYPE with ARRAY[INTEGER] or ARRAY[VARCHAR(n)] defines an associative array. The index type must be INTEGER or VARCHAR. The value you use as an index when assigning an element must be assignable to that index type. Cardinality is not declared as a small constant; the collection can grow to a very large maximum. Indexes are unique. They do not have to be 1, 2, 3, …

1
2
3
4
5
6
7
CREATE TYPE PERSONAL_PHONENUMBERS AS DECIMAL(16,0) ARRAY[VARCHAR(8)];
CREATE TYPE CAPITALSARRAY AS VARCHAR(30) ARRAY[VARCHAR(20)];
CREATE TYPE PRODUCTS AS VARCHAR(40) ARRAY[INTEGER];
 
-- Associative assignment uses the key, not a dense position
--   phones['Home'] = 14085551212
--   phones['Work'] = 14085559999

Associative arrays are the right default when the natural key is a label or a sparse identifier: 'Home' / 'Work' / 'Cell', province name to capital city, product number to description. Do not treat VARCHAR indexes as case-insensitive unless you normalised the keys yourself.

ARRAY_DELETE removes elements (or the whole array, depending on how you call it). After deletes, remaining keys stay; cardinality falls. That is different from TRIM_ARRAY on an ordinary array, which is about the tail of a dense list.

Building, casting, and unnesting arrays

You will see arrays as:

• SQL variables and parameters in SQL PL
• Global variables typed as the array type
• Results of CAST, ARRAY_AGG, ARRAY_DELETE, or TRIM_ARRAY

1
2
3
4
5
6
7
8
9
-- Array constructor / CAST shapes (exact syntax varies by statement context)
CAST(ARRAY[14085551111, 14085552222] AS PHONENUMBERS);
 
-- Turn elements into a result table
SELECT T.PHONE
  FROM UNNEST(CAST(ARRAY[14085551111, 14085552222] AS PHONENUMBERS)) AS T(PHONE);
 
-- How many elements?
VALUES CARDINALITY(CAST(ARRAY[1, 2, 3] AS PHONENUMBERS));

UNNEST is the bridge back to set-based SQL: once the list is a table, you can JOIN it, filter it, or insert it. ARRAY_AGG is the opposite direction in many designs—fold a column of a result set into an array to pass into a procedure. Element references can appear anywhere an expression of the element type is legal, which is why SET outvar[1] = invar[1] looks like ordinary assignment.

Nullability: an array variable can be null as a whole, and elements can be null depending on the element type and assignments. A null array is not the same as an empty array (cardinality 0). Check for null before CARDINALITY if the variable might never have been assigned.

Distinct types versus arrays

They solve different problems and are sometimes combined (an array whose element type is a distinct type, where the product allows it). Distinct types protect meaning on a single value. Arrays collect many values of one element type for procedural exchange. Neither replaces a properly modelled parent/child table when the data must be queried, indexed, and recovered as relational rows.

Privileges, schema qualification, and the SQL path apply to type names the same way they apply to functions: an unqualified type name is resolved using the path. CREATE TYPE in the wrong schema is a common “works in SPUFI under my id, fails in the procedure” incident.

Explain It Like I'm Five

A distinct type is a sticker you put on a box. Two boxes can both be “numbers with two decimal places,” but one sticker says MONEY and the other says WEIGHT. You are not allowed to add them until a grown-up writes a special plus that knows those stickers. An ordinary array is a numbered row of cubbies, starting at cubby 1, with a maximum how many cubbies you bought. An associative array is a row of cubbies with name tags like Home and Work instead of 1 and 2, and some name tags can be missing in the middle.

Exercises

1. Write CREATE TYPE for a US dollar amount based on DECIMAL(11,2) named USD_MONEY.
2. Explain why USD_MONEY + 1.00 is rejected until you CAST or create a sourced “+” function.
3. Create an ordinary array type of up to 12 DECIMAL(9,2) monthly amounts named MONTHLY_AMTS.
4. Create an associative array type of VARCHAR(40) descriptions indexed by INTEGER product numbers.
5. Describe when you would UNNEST an array instead of looping with SQL PL indexes.

Frequently asked questions