Write recursive SQL in DB2 for z/OS

Prerequisites and sample hierarchy

Before writing the query, identify a node key, a parent key, and the direction you want to travel. The following EMPLOYEE_HIERARCHY table uses an adjacency-list design: EMPLOYEE_ID identifies a person and MANAGER_ID points to another row in the same table. A top-level employee has a null manager. The examples assume that EMPLOYEE_ID is unique and that an index beginning with MANAGER_ID supports downward traversal.

1
2
3
4
5
6
7
8
9
10
11
12
CREATE TABLE EMPLOYEE_HIERARCHY (
  EMPLOYEE_ID    INTEGER      NOT NULL PRIMARY KEY,
  EMPLOYEE_NAME  VARCHAR(80)  NOT NULL,
  MANAGER_ID     INTEGER,
  JOB_TITLE      VARCHAR(80),
  ACTIVE_FLAG    CHAR(1)      NOT NULL,
  FOREIGN KEY (MANAGER_ID)
    REFERENCES EMPLOYEE_HIERARCHY (EMPLOYEE_ID)
);
 
CREATE INDEX IX_EMPLOYEE_MANAGER
  ON EMPLOYEE_HIERARCHY (MANAGER_ID, EMPLOYEE_ID);

A self-referencing foreign key prevents a manager value from pointing to a nonexistent employee, but it does not by itself prove that every route is acyclic. Data can still be wrong in ways that form a longer loop. The SQL therefore needs defensive logic even when referential integrity is enabled.

Recursive CTE structure

IBM describes a common table expression as a temporary named result that exists for one SQL statement. A CTE becomes recursive when a fullselect in its definition references the CTE name in its FROM clause. Db2 first evaluates the initialization fullselect, often called the anchor member. It then repeatedly evaluates the iterative fullselect, often called the recursive member, using newly produced rows to find the next set.

• CTE name and column list: the explicit list defines the shape of the recursive result. Anchor and recursive expressions in each position must have compatible data types, lengths, precision, and scale.
• Anchor member: selects the seed row or rows and does not reference the recursive CTE. The anchor might select one manager, every root, or the direct components of one assembly.
• UNION ALL: connects the initialization and iterative fullselects. Db2 requires the ALL form in the recursion cycle; it does not perform duplicate elimination.
• Recursive member: references the CTE and joins its current rows to base data to find the next level. It also carries calculated state such as depth and path.
• Outer query: consumes the completed recursive result. Put final filtering, aggregation, and presentation ordering here when those operations are restricted inside the cycle.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
WITH ORG_TREE (
  EMPLOYEE_ID,
  EMPLOYEE_NAME,
  MANAGER_ID,
  TREE_LEVEL
) AS (
      SELECT E.EMPLOYEE_ID,
             E.EMPLOYEE_NAME,
             E.MANAGER_ID,
             0
        FROM EMPLOYEE_HIERARCHY E
       WHERE E.EMPLOYEE_ID = :START_EMPLOYEE
 
    UNION ALL
 
      SELECT C.EMPLOYEE_ID,
             C.EMPLOYEE_NAME,
             C.MANAGER_ID,
             P.TREE_LEVEL + 1
        FROM ORG_TREE P
        INNER JOIN EMPLOYEE_HIERARCHY C
          ON C.MANAGER_ID = P.EMPLOYEE_ID
       WHERE P.TREE_LEVEL < :MAX_LEVEL
)
SELECT EMPLOYEE_ID,
       EMPLOYEE_NAME,
       MANAGER_ID,
       TREE_LEVEL
  FROM ORG_TREE
 ORDER BY TREE_LEVEL, EMPLOYEE_ID;

If the host variable START_EMPLOYEE is 100, the anchor emits employee 100 at level zero. The first recursive iteration finds direct reports and gives them level one. The next iteration finds their reports at level two. A branch ends naturally when its current row has no children. The whole recursion ends when the latest iteration produces no rows or when MAX_LEVEL prevents additional rows from expanding.

Write an organization chart step by step

1. Choose a precise anchor predicate. Use a host variable when the same static SQL must start at different managers. Use MANAGER_ID IS NULL only when the requirement is to traverse every root.
2. Write and run the anchor SELECT by itself. Verify that it returns exactly the intended seeds before introducing recursion.
3. Add a column list after the CTE name. Include stable identifiers, the relationship key, a level counter, and any state that must survive each iteration.
4. Add UNION ALL, then join the recursive CTE to the base table in the desired direction. For descendants, match child.MANAGER_ID to parent.EMPLOYEE_ID.
5. Increment an integer level and add a limiting predicate. IBM looks for the recognizable pattern of a counter incremented by a constant and compared with a constant or host variable; absent that pattern, Db2 can issue warning SQLSTATE 01605.
6. Add cycle detection, then place the final ORDER BY in the outer fullselect. Test the statement with leaf rows, deep branches, multiple children, and deliberately bad data.

The direction of the recursive join controls the result. The previous query walks from a manager down to descendants. To walk upward, join the current row's MANAGER_ID to another row's EMPLOYEE_ID. The CTE pattern does not change; only the edge followed by the iterative fullselect changes.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
WITH MANAGER_CHAIN (
  EMPLOYEE_ID, EMPLOYEE_NAME, MANAGER_ID, DISTANCE
) AS (
      SELECT EMPLOYEE_ID, EMPLOYEE_NAME, MANAGER_ID, 0
        FROM EMPLOYEE_HIERARCHY
       WHERE EMPLOYEE_ID = :EMPLOYEE_ID
 
    UNION ALL
 
      SELECT M.EMPLOYEE_ID,
             M.EMPLOYEE_NAME,
             M.MANAGER_ID,
             C.DISTANCE + 1
        FROM MANAGER_CHAIN C
        INNER JOIN EMPLOYEE_HIERARCHY M
          ON M.EMPLOYEE_ID = C.MANAGER_ID
       WHERE C.DISTANCE < 20
)
SELECT EMPLOYEE_ID, EMPLOYEE_NAME, MANAGER_ID, DISTANCE
  FROM MANAGER_CHAIN
 ORDER BY DISTANCE;

Build readable paths and stable levels

A level column answers “how far is this row from the anchor?” A path answers “which route reached this row?” Both are useful for displaying and debugging a hierarchy. Cast the anchor path to the intended final type because its inferred length establishes the result type. If the anchor is merely EMPLOYEE_NAME, a later concatenation can exceed that short type. An explicit VARCHAR gives recursive rows room to grow.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
WITH ORG_PATH (
  EMPLOYEE_ID,
  EMPLOYEE_NAME,
  MANAGER_ID,
  TREE_LEVEL,
  DISPLAY_PATH
) AS (
      SELECT E.EMPLOYEE_ID,
             E.EMPLOYEE_NAME,
             E.MANAGER_ID,
             0,
             CAST(E.EMPLOYEE_NAME AS VARCHAR(2000))
        FROM EMPLOYEE_HIERARCHY E
       WHERE E.EMPLOYEE_ID = :START_EMPLOYEE
 
    UNION ALL
 
      SELECT C.EMPLOYEE_ID,
             C.EMPLOYEE_NAME,
             C.MANAGER_ID,
             P.TREE_LEVEL + 1,
             P.DISPLAY_PATH CONCAT ' > ' CONCAT C.EMPLOYEE_NAME
        FROM ORG_PATH P
        INNER JOIN EMPLOYEE_HIERARCHY C
          ON C.MANAGER_ID = P.EMPLOYEE_ID
       WHERE P.TREE_LEVEL < 20
)
SELECT EMPLOYEE_ID, EMPLOYEE_NAME, TREE_LEVEL, DISPLAY_PATH
  FROM ORG_PATH
 ORDER BY TREE_LEVEL, DISPLAY_PATH;

Names make a friendly display path but are not necessarily unique or suitable for deterministic tree ordering. A production report can carry two paths: a readable name path and a sortable key path made from consistently formatted identifiers. Always order in the outer query. The order in which Db2 generates recursive rows is an implementation detail, not a breadth-first or depth-first presentation guarantee.

Detect cycles and avoid repeated routes

A cycle occurs when traversal returns to a node already visited on the same route. The simplest case is an employee whose manager is the same employee. A direct inequality can reject that error, but it cannot detect A to B to C to A. A maximum level eventually stops the loop, yet it still creates repeated rows and unnecessary work before stopping. A visited-key path detects the repeated node as soon as it appears.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
WITH ORG_SAFE (
  EMPLOYEE_ID,
  EMPLOYEE_NAME,
  MANAGER_ID,
  TREE_LEVEL,
  VISITED_KEYS
) AS (
      SELECT E.EMPLOYEE_ID,
             E.EMPLOYEE_NAME,
             E.MANAGER_ID,
             0,
             CAST(
               '/' CONCAT VARCHAR(E.EMPLOYEE_ID) CONCAT '/'
               AS VARCHAR(3000)
             )
        FROM EMPLOYEE_HIERARCHY E
       WHERE E.EMPLOYEE_ID = :START_EMPLOYEE
 
    UNION ALL
 
      SELECT C.EMPLOYEE_ID,
             C.EMPLOYEE_NAME,
             C.MANAGER_ID,
             P.TREE_LEVEL + 1,
             P.VISITED_KEYS CONCAT VARCHAR(C.EMPLOYEE_ID) CONCAT '/'
        FROM ORG_SAFE P
        INNER JOIN EMPLOYEE_HIERARCHY C
          ON C.MANAGER_ID = P.EMPLOYEE_ID
       WHERE P.TREE_LEVEL < :MAX_LEVEL
         AND C.EMPLOYEE_ID <> C.MANAGER_ID
         AND LOCATE(
               '/' CONCAT VARCHAR(C.EMPLOYEE_ID) CONCAT '/',
               P.VISITED_KEYS
             ) = 0
)
SELECT EMPLOYEE_ID, EMPLOYEE_NAME, MANAGER_ID, TREE_LEVEL
  FROM ORG_SAFE
 ORDER BY TREE_LEVEL, EMPLOYEE_ID;

Delimiters are essential. Searching for 12 without boundaries could incorrectly match 112. The visited path is route-specific, so two legitimate branches can still reach the same node when the data represents a graph rather than a strict tree. Decide whether the required result is a set of routes or a set of unique nodes. If unique nodes are needed, deduplicate after expansion only when doing so preserves the business meaning.

Recursion limits and reliable termination

Do not treat “the data should terminate” as a stopping policy. Db2 for z/OS does not use the SQL Server-style MAXRECURSION query hint. Instead, carry an integer counter, increment it by a constant, and compare it with a constant or host variable in the iterative fullselect. Choose a ceiling above the deepest valid business hierarchy but low enough to contain malformed data. An organization expected to have at most 12 reporting levels might use 20; a bill of materials may require a different measured limit.

A counter is a safety ceiling, not complete cycle detection. Combine it with visited-key logic and data-quality controls. Also remember that depth is not the only growth factor. If every row has ten children, three levels can produce 1,110 descendant rows and six levels can produce more than a million. Restrict the anchor and prune branches even when the maximum depth is small.

Unsupported constructs and IBM restrictions

IBM places deliberate restrictions on fullselects that participate in a recursion cycle. Keeping the recursive member focused on “find the next rows” makes its behavior easier to reason about. For the exact rules of your release, check the Db2 for z/OS SQL Reference, especially after changing application compatibility levels.

• Specify column names after the recursive CTE name. Corresponding initialization and iterative expressions must be assignment-compatible.
• The initialization fullselect must not reference the recursive CTE. A fullselect in the cycle can include at most one reference to a CTE that is part of that cycle.
• Members in the cycle start with SELECT or SELECT ALL. SELECT DISTINCT is not allowed, and set operators in the cycle use the ALL keyword.
• Aggregate functions, GROUP BY, and HAVING are not allowed in fullselects that participate in the cycle. Aggregate the completed CTE in the outer query.
• ORDER BY, OFFSET, and FETCH FIRST are not allowed within the recursive cycle. Put final row limiting and presentation order outside the CTE.
• Outer joins and scalar or quantified subqueries must not be part of the recursion cycle. Rewrite the traversal so its iterative relationship uses supported joins, then enrich the completed result afterward.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
WITH ORG_TOTALS (
  EMPLOYEE_ID, MANAGER_ID, TREE_LEVEL
) AS (
      SELECT EMPLOYEE_ID, MANAGER_ID, 0
        FROM EMPLOYEE_HIERARCHY
       WHERE EMPLOYEE_ID = :START_EMPLOYEE
 
    UNION ALL
 
      SELECT C.EMPLOYEE_ID,
             C.MANAGER_ID,
             P.TREE_LEVEL + 1
        FROM ORG_TOTALS P
        INNER JOIN EMPLOYEE_HIERARCHY C
          ON C.MANAGER_ID = P.EMPLOYEE_ID
       WHERE P.TREE_LEVEL < 20
)
SELECT TREE_LEVEL, COUNT(*) AS EMPLOYEE_COUNT
  FROM ORG_TOTALS
 GROUP BY TREE_LEVEL
 ORDER BY TREE_LEVEL;

COUNT and GROUP BY are valid here because the outer query runs after ORG_TOTALS has expanded the hierarchy. This separation is a reusable design rule: recurse to discover rows, then summarize, rank, enrich, and format the finished result.

Ordering recursive results

Without an outer ORDER BY, Db2 can return rows in any order. Ordering by TREE_LEVEL and a node key produces a level-grouped, breadth-like report. Ordering by a sortable path keeps descendants near their parent and produces a depth-like display. A path based on names can shift when names change and can collide when names repeat, so stable fixed-width keys are safer for deterministic reports.

Keep traversal and presentation separate. Do not infer that the engine visits rows in the same order shown by a previous execution. Access paths can change after RUNSTATS, a bind, maintenance, or a Db2 upgrade. The final ORDER BY is the only SQL guarantee of result sequence.

Performance and debugging

• Make the anchor selective. Accidentally seeding every row can traverse the same subtree many times. Test the anchor independently and inspect its cardinality.
• Index the recursive join. Descendant traversal usually benefits from an index beginning with the parent key, while ancestor traversal normally uses the node's primary or unique key.
• Carry only required columns. Descriptions and long paths enlarge the intermediate result and work-file demand. Add nonrecursive descriptive data after traversal when possible.
• Filter branches early. If inactive employees must never be followed, put ACTIVE_FLAG = 'Y' in the iterative member so those branches do not generate deeper rows.
• Use intentional types. Cast growing strings and calculated decimals in the anchor. Test the longest valid path and the largest expected numeric result.
• Use EXPLAIN. Verify access paths and estimated cardinalities rather than assuming the optimizer executes the statement like a procedural loop.
• Test realistic fan-out. A five-row demonstration does not reveal the cost of a hierarchy with hundreds of children per node.

A practical debugging method is to lower MAX_LEVEL to one and inspect the direct children. Increase it one level at a time while returning TREE_LEVEL, parent key, child key, and VISITED_KEYS. This exposes a reversed join, an overly broad anchor, an incorrect delimiter, or a data cycle quickly. Once the traversal is correct, restore the business limit and remove diagnostic columns that consumers do not need.

Verify the result

1. Run the anchor SELECT alone and confirm its row count and keys.
2. Run with MAX_LEVEL zero, one, and two. Verify that each level contains the expected relationships and that the counter begins at the intended value.
3. Start from a leaf employee. The result should contain only the anchor for a descendant query.
4. Insert or isolate test data containing a self-link and a longer A-to-B-to-C-to-A cycle. Confirm that VISITED_KEYS stops each route before a key repeats.
5. Compare counts by level with a trusted sample. Check for duplicate nodes reached by multiple valid routes and decide whether routes or unique nodes are required.
6. Use EXPLAIN and representative statistics to confirm that the recursive relationship uses a suitable access path and that estimated growth resembles production data.

Common errors

SQLSTATE 01605 warning

Db2 did not recognize the expected counter increment and limiting predicate used to guard against infinite recursion. Review the iterative member and add an integer counter plus a direct comparison to a constant or host variable. Do not silence the warning without proving termination.

SQLSTATE 42836 restriction error

The recursion cycle contains an unsupported construct or invalid reference pattern. Common causes include an aggregate, GROUP BY, HAVING, outer join, subquery, multiple recursive references, or ordering inside the cycle. Move post-processing to the outer query and simplify the iterative member.

SQLSTATE 42925 set-operation error

A member uses SELECT DISTINCT or the recursion cycle does not use an ALL set operator. Use SELECT or SELECT ALL and connect members with UNION ALL. Add intentional deduplication outside recursion only when the requirement calls for it.

Path truncation or incompatible result types

The anchor expression established a type too short or otherwise incompatible with the recursive expression. Cast the anchor path to an adequately sized VARCHAR and give numeric calculations sufficient precision and scale.

Explain it like I'm five

Imagine an organization chart made from cards. First, you put one manager's card on the table. That is the anchor. Then you follow a rule: “For every new card, find all cards that say this person is their manager.” That rule is the recursive member. UNION ALL puts the first card and every card you discover into one result.

You also carry two notes. One says how many steps you have taken, so you stop after a safe number. The other lists every card already seen on your current route, so a bad chart cannot send you in a circle. At the end, you arrange the cards with ORDER BY because the order in which you found them is not guaranteed.

Exercises

1. Write a recursive CTE that starts at EMPLOYEE_ID 100 and returns all descendants with level zero for the anchor.
2. Change the recursive join to return the selected employee's chain of managers. Explain why the join direction changed.
3. Add a readable name path and a separate sortable identifier path. Compare ordering by level with ordering by the identifier path.
4. Add visited-key cycle detection using unambiguous delimiters. Test employee IDs 12 and 112 to prove that one does not falsely match the other.
5. Create a three-node cycle and compare the output from a depth limit alone with the output from both a depth limit and visited-key detection.
6. Return a count of employees at each level. Keep COUNT and GROUP BY outside the recursion cycle and explain why.
7. Run EXPLAIN before and after adding an index beginning with MANAGER_ID. Compare the access path and estimated cost for a representative root.
8. Deliberately place ORDER BY in the iterative fullselect, observe the Db2 diagnostic, then move the ordering to the final SELECT.

Frequently asked questions

Can the anchor return more than one row?

Yes. Multiple seeds produce a forest or several independent traversals. Carry a ROOT_ID through the recursive result when every generated row must remain associated with its originating root.

Does UNION ALL prevent duplicate rows?

No. UNION ALL preserves generated rows and avoids duplicate-elimination work. Prevent cycles by checking visited keys. If valid graph routes reach the same node more than once, decide whether those routes are meaningful before deduplicating the outer result.

When should I avoid recursive SQL?

Fixed self-joins can be simpler when depth is guaranteed to be very small and constant. Frequently queried, mostly static hierarchies can benefit from a maintained path or closure table, although those designs make updates more complex. Choose with measured workload evidence.

What IBM documentation should I check?

Use the Db2 for z/OS SQL Reference topic for common-table-expression syntax and restrictions, plus IBM's task topic “Creating recursive SQL by using common table expressions.” Confirm that the documentation version matches the subsystem release and application compatibility level.