DB2 LATERAL, UNNEST, XMLTABLE, and data-change table references

One idea: produce rows in the FROM clause

A table reference does not have to name a stored table. It can be a nested fullselect, a table function, an expanded collection, an XML-to-relational mapping, or the intermediate result of a data change. Once produced, its columns can usually be selected, filtered, joined, grouped, and ordered like columns from any other table reference. The important differences are how rows are generated and what restrictions Db2 imposes.

LATERAL correlated table expressions

A normal nested table expression in FROM is an independent fullselect. Without lateral capability, it cannot reach sideways to a neighboring table reference at the same query level. LATERAL changes that rule: the nested fullselect may reference columns from table references that appear before it in the same FROM clause. Conceptually, Db2 evaluates the right-side expression for each qualifying left-side row, although the optimizer can choose any equivalent physical strategy.

A classic use case is top-N-per-parent. The following query preserves every department and asks the lateral expression for the highest-paid employee in that department. The correlation name D is visible because DEPT precedes the lateral expression. ON 1 = 1 means no additional join predicate is required; correlation already occurs inside the nested query.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT D.DEPTNO,
       D.DEPTNAME,
       E.EMPNO,
       E.LASTNAME,
       E.SALARY
  FROM DSN8C10.DEPT AS D
  LEFT JOIN LATERAL (
       SELECT EMPNO, LASTNAME, SALARY
         FROM DSN8C10.EMP
        WHERE WORKDEPT = D.DEPTNO
        ORDER BY SALARY DESC
        FETCH FIRST 1 ROW ONLY
  ) AS E
    ON 1 = 1;

LEFT JOIN is significant: a department with no employees still appears, with null E columns. An inner join would discard it. A correlated lateral reference can participate in an inner join or left outer join when every referenced table is to its left. It cannot depend on a table that appears later, and the dependency is incompatible with using that correlated item on the preserved side of a right or full outer join. Put prerequisites first and give every generated table a correlation name.

The TABLE form and table functions

Db2 for z/OS also uses TABLE to introduce a correlated nested table expression. On supported application compatibility levels, LATERAL provides the standard spelling, while TABLE is common in established z/OS SQL. The following TABLE query has the same left-to-right dependency as the prior LATERAL example.

1
2
3
4
5
6
7
8
9
10
SELECT D.DEPTNO, E.EMPNO, E.SALARY
  FROM DSN8C10.DEPT AS D
  LEFT JOIN TABLE (
       SELECT EMPNO, SALARY
         FROM DSN8C10.EMP
        WHERE WORKDEPT = D.DEPTNO
        ORDER BY SALARY DESC
        FETCH FIRST 2 ROWS ONLY
  ) AS E
    ON 1 = 1;

TABLE also appears when invoking a user-defined table function, for example TABLE(HR.EMPLOYEES_FOR_DEPT(D.DEPTNO)). A function argument can contain a column from a preceding table reference, making the function call lateral by nature. The function's declared RETURNS TABLE definition supplies its output types. Cost, external action, determinism, and CARDINALITY attributes matter because a function can execute many times or return far more rows than expected.

• Use LATERAL or TABLE when the generated rows depend on the current left-side row.
• Use an ordinary nested table expression when the subquery is independent and can be evaluated as one logical result.
• Keep selective predicates inside the lateral expression when they define which rows should be generated, especially before FETCH FIRST.
• Check APPLCOMPAT before adopting syntax added at a newer function level.

UNNEST: arrays become relational rows

UNNEST is a collection-derived table. It turns an SQL array value into rows so set-oriented SQL can replace a procedural loop. This is especially useful in native SQL procedures: accept an array of keys, unnest it, and join once to a business table. Arrays in Db2 for z/OS are routine variables, parameters, or expression results; they are not a general replacement for normalized child-table storage.

1
2
3
4
5
6
7
8
9
-- Assume :EMP_IDS is an ordinary array of CHAR(6)
SELECT U.POSITION_NO,
       E.EMPNO,
       E.LASTNAME
  FROM UNNEST(:EMP_IDS) WITH ORDINALITY
       AS U(EMPNO, POSITION_NO)
  INNER JOIN DSN8C10.EMP AS E
    ON E.EMPNO = U.EMPNO
 ORDER BY U.POSITION_NO;

WITH ORDINALITY adds a one-based position column, so the query can preserve meaningful input order. Without ORDER BY, result row order is still not guaranteed. When multiple ordinary arrays are passed to UNNEST, Db2 aligns elements by position and returns as many rows as the largest current cardinality; values missing from a shorter array become null. Give the derived columns names in the correlation clause so later expressions remain readable.

1
2
3
4
5
-- Parallel ordinary arrays: employee number and requested bonus
INSERT INTO HR.BONUS_REQUEST (EMPNO, BONUS)
SELECT U.EMPNO, U.BONUS
  FROM UNNEST(:EMP_IDS, :BONUS_AMOUNTS)
       AS U(EMPNO, BONUS);

Associative arrays

An associative array is keyed by an INTEGER or character index rather than a dense ordinal position. UNNEST of one associative array returns both the index and the element value. Only one associative array can be supplied, and WITH ORDINALITY is not allowed. The index is already the key needed to identify each element.

1
2
3
4
-- :PHONE_BY_TYPE is indexed by values such as 'HOME' and 'WORK'
SELECT U.PHONE_TYPE, U.PHONE_NUMBER
  FROM UNNEST(:PHONE_BY_TYPE)
       AS U(PHONE_TYPE, PHONE_NUMBER);

XMLTABLE: shred XML into typed columns

XMLTABLE is a built-in table function that maps an XML sequence into a relational table. Its row XQuery expression selects the repeating items that become rows. PASSING binds an XML value to an XQuery variable. The COLUMNS clause then defines each SQL column's type and a PATH expression evaluated relative to the current row item. This is different from XMLQUERY, which returns XML, and XMLEXISTS, which answers whether an XQuery result exists.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT O.ORDER_ID,
       X.LINE_NO,
       X.PRODUCT_CODE,
       X.QUANTITY,
       X.UNIT_PRICE
  FROM SALES.ORDER_DOCUMENT AS O,
       XMLTABLE(
         '$doc/order/line'
         PASSING O.ORDER_XML AS "doc"
         COLUMNS
           LINE_NO      INTEGER       PATH '@number',
           PRODUCT_CODE VARCHAR(20)   PATH 'productCode',
           QUANTITY     INTEGER       PATH 'quantity',
           UNIT_PRICE   DECIMAL(9,2)  PATH 'unitPrice'
       ) AS X
 WHERE O.STATUS = 'NEW';

If one order document has four line elements, its XMLTABLE invocation returns four rows. If the row expression finds no line elements, it returns no row for that document. A regular column PATH should produce a value compatible with the declared SQL type; a missing node generally maps to null when the column permits it, while multiple atomic values or a value that cannot be cast can raise an error. You can also define a column with FOR ORDINALITY to number the row items.

Namespaces are part of the name

XPath expressions that look correct can return no rows when the source XML uses a namespace. In XML, an element in a namespace is not the same name as an unqualified element. Declare namespace prefixes with XMLNAMESPACES and use those prefixes in the row and column paths. The URI must match the document; the chosen prefix text does not.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SELECT O.ORDER_ID,
       X.LINE_NO,
       X.SKU,
       X.QUANTITY
  FROM SALES.ORDER_DOCUMENT AS O,
       XMLTABLE(
         XMLNAMESPACES(
           'http://example.com/order' AS "ord",
           'http://example.com/catalog' AS "cat"
         ),
         '$doc/ord:order/ord:lines/ord:line'
         PASSING O.ORDER_XML AS "doc"
         COLUMNS
           LINE_NO  INTEGER      PATH '@number',
           SKU      VARCHAR(20)  PATH 'cat:product/cat:sku',
           QUANTITY INTEGER      PATH 'ord:quantity',
           ITEM_NO  FOR ORDINALITY
       ) AS X;

A default namespace can instead be declared with XMLNAMESPACES(DEFAULT 'http://example.com/order'), but explicit prefixes often make production SQL easier to audit when one document mixes vocabularies. Attribute names are normally unqualified unless the XML explicitly prefixes them, which is why @number has no ord prefix in this example. Keep the row path narrow; shredding an entire large document and filtering only afterward creates unnecessary work.

Data-change-table-reference

A data-change-table-reference nests a supported data-change statement inside the FROM clause of a SELECT. It performs the change and exposes an intermediate result containing the rows directly affected. The main benefit is correctness and fewer round trips: an application can INSERT a row and receive its generated identity, default, ROWID, or row-change timestamp in the same statement.

1
2
3
4
5
6
-- Return a generated identity and timestamp from one INSERT
SELECT ORDER_ID, CREATED_TS, STATUS
  FROM FINAL TABLE (
       INSERT INTO SALES.ORDERS (CUSTOMER_ID, STATUS)
       VALUES (:CUSTOMER_ID, 'NEW')
  );

FINAL TABLE returns the affected rows as they appear at completion of the nested change. It includes assignments made by defaults, generated columns, and applicable BEFORE triggers. Db2 for z/OS rejects situations where an AFTER trigger would further modify the target in a way that prevents FINAL TABLE from guaranteeing those final values. The target and trigger restrictions must therefore be reviewed before using this as a generic return-everything technique.

FINAL TABLE and OLD TABLE with UPDATE

A searched UPDATE can expose after-images through FINAL TABLE or before-images through OLD TABLE. If one result must contain both old and new values, Db2 for z/OS can use an INCLUDE column on the UPDATE. The included value is part of the intermediate result but is not stored as a target column.

1
2
3
4
5
6
7
8
SELECT EMPNO, OLD_SALARY, SALARY AS NEW_SALARY
  FROM FINAL TABLE (
       UPDATE DSN8C10.EMP
          INCLUDE (OLD_SALARY DECIMAL(9,2))
          SET OLD_SALARY = SALARY,
              SALARY = SALARY * 1.05
        WHERE WORKDEPT = 'C01'
  );

1
2
3
4
5
6
7
-- Return rows as they existed before the update
SELECT EMPNO, SALARY AS PREVIOUS_SALARY
  FROM OLD TABLE (
       UPDATE DSN8C10.EMP
          SET SALARY = SALARY * 1.05
        WHERE WORKDEPT = 'C01'
  );

OLD TABLE with DELETE

DELETE has no surviving after-image, so OLD TABLE is the useful form. It can return deleted values to a client or supply application audit processing in the same unit of work. The nested operation must be a searched DELETE, not a positioned DELETE.

1
2
3
4
5
6
SELECT ORDER_ID, CUSTOMER_ID, STATUS
  FROM OLD TABLE (
       DELETE FROM SALES.ORDERS
        WHERE STATUS = 'CANCELLED'
          AND CREATED_TS < CURRENT TIMESTAMP - 90 DAYS
  );

Where NEW TABLE fits—and where it does not

The phrase NEW TABLE requires a platform label. Db2 LUW documentation describes NEW TABLE as a data-change-table-reference that exposes rows after the direct change but before referential-integrity processing and AFTER triggers. Db2 for z/OS data-change-table-reference syntax does not offer that qualifier; its supported forms are FINAL TABLE and OLD TABLE. Copying a Db2 LUW example such as SELECT FROM NEW TABLE (UPDATE ...) into a z/OS package is therefore not portable SQL.

Db2 for z/OS does use NEW TABLE in another context: a statement-level trigger can define a transition table containing the set of new rows affected by the triggering statement. OLD TABLE can likewise name old transition rows where the trigger event permits it. That trigger transition table exists for trigger execution; it is not the FROM-clause data-change-table-reference described above. Always identify whether documentation means a trigger transition table or a SELECT-from-data-change qualifier.

1
2
3
4
5
6
7
8
9
-- Conceptual statement-level trigger transition-table form
CREATE TRIGGER SALES.AUDIT_ORDER_UPDATES
  AFTER UPDATE ON SALES.ORDERS
  REFERENCING OLD TABLE AS O NEW TABLE AS N
  FOR EACH STATEMENT
  INSERT INTO SALES.ORDER_AUDIT (ORDER_ID, OLD_STATUS, NEW_STATUS)
  SELECT O.ORDER_ID, O.STATUS, N.STATUS
    FROM O INNER JOIN N
      ON O.ORDER_ID = N.ORDER_ID;

Constraints and safe design rules

These constructs are composable, but not unrestricted. A correlated LATERAL or TABLE expression depends on preceding FROM items and cannot reverse that dependency. UNNEST needs an array in a context where arrays are supported. XMLTABLE paths and result types are checked at execution, so dirty documents can fail conversion. A data-change table reference performs real DML when its SELECT executes; it is not a preview.

• A data-change-table-reference must be the only table reference in that FROM clause and belongs in the outer fullselect of a SELECT statement or SELECT INTO.
• The cursor produced by SELECT from a data change is read-only, and required privileges include both the data-change privilege and SELECT on the target.
• Nested UPDATE and DELETE forms are searched operations. They cannot correlate to columns outside their own data-change statement.
• The intermediate result contains target columns, including implicitly hidden columns, plus any INCLUDE columns. Select only what the application needs.
• FETCH FIRST on the outer SELECT limits returned rows; it does not reduce how many rows the nested data-change statement changes.
• Review eligible target views, INSTEAD OF triggers, AFTER triggers, referential actions, encoding, and MERGE-specific restrictions in the SQL Reference for your Db2 level.

Choosing the right construct

Choose by the shape of the source. If rows depend on one parent row, use LATERAL or TABLE. If the source is an array, use UNNEST. If the source is XML and repeating nodes must become typed columns, use XMLTABLE. If the source is the rows changed by DML, use FINAL TABLE or OLD TABLE. Do not use XMLTABLE merely to test existence, and do not issue a follow-up SELECT for generated values when FINAL TABLE can return the exact affected row.

Performance starts with cardinality. A lateral top-two query can return at most two rows per parent; an unconstrained lateral expression might multiply millions of rows. UNNEST cardinality comes from array size. XMLTABLE cardinality comes from the row path. Data-change-table cardinality comes from the nested DML search condition. State those expectations during review, keep statistics current on joined tables, and inspect EXPLAIN output rather than assuming a table-valued expression is inexpensive.

Explain it like I'm 5

Imagine a workbench. LATERAL says, “For each box on my left, open it and make a little list.” UNNEST takes a bag of numbered blocks and puts one block on each row. XMLTABLE reads a storybook and makes one spreadsheet row for every matching picture, using labels to understand special names. FINAL TABLE shows the cards after you changed them, while OLD TABLE shows what the cards said before. NEW TABLE is a label used differently in different Db2 houses, so on Db2 for z/OS you check whether someone means a trigger's new-row basket rather than a SELECT-from-change feature.

Exercises

• Rewrite a correlated scalar subquery that finds the two highest salaries per department as a LEFT JOIN LATERAL expression. Explain why DEPT must precede the expression.
• Define an ordinary array variable containing employee numbers, UNNEST it WITH ORDINALITY, and join it to EMP. Test what happens when one employee number is missing.
• Given XML with a default order namespace and a separate catalog prefix, write XMLNAMESPACES, a line-item row path, and typed columns for SKU, quantity, and price.
• Write FINAL TABLE around an INSERT that returns an identity and creation timestamp. Explain why selecting MAX(identity-column) afterward would be unsafe.
• Compare OLD TABLE around UPDATE with FINAL TABLE plus an INCLUDE old-value column. State which design you would use for an audit response containing both values.
• Review a NEW TABLE example found online. Identify its Db2 platform and decide whether it is a data-change-table-reference or a trigger transition table before using it.