Hierarchical and recursive patterns in Db2 for z/OS

What makes data hierarchical?

A hierarchy stores relationships between parents and children. In an employee table, EMP_ID is the node and MANAGER_ID points back to another EMP_ID. The top employee has no manager. In a parts table, each row can instead represent an edge from an assembly to one component. Both designs form a tree when every child has one route to a root. If nodes can have several parents or routes, the structure is a graph, but the same recursive CTE pattern can still traverse it with more careful cycle and duplicate handling.

1
2
3
4
5
6
7
CREATE TABLE EMPLOYEE_TREE (
  EMP_ID      INTEGER      NOT NULL PRIMARY KEY,
  EMP_NAME    VARCHAR(60)  NOT NULL,
  MANAGER_ID  INTEGER,
  JOB_TITLE   VARCHAR(60),
  FOREIGN KEY (MANAGER_ID) REFERENCES EMPLOYEE_TREE (EMP_ID)
);

The self-referencing foreign key protects referential integrity, but it does not by itself prove that the data is acyclic. Depending on the changes and constraints used, rows can still form a longer loop. Query design therefore needs defensive stopping rules even when the table has a foreign key.

The recursive common table expression

IBM describes a common table expression as a temporary named result that exists for the duration of one SQL statement. It is not a permanent view or table. A CTE becomes recursive when a fullselect inside its definition refers to the CTE name in its FROM clause. Conceptually, Db2 evaluates seed rows, uses those rows to find another set, and repeats the iterative step until the latest step returns no rows.

Four pieces to recognize

• Explicit column list: the names after the CTE name describe the recursive result. Corresponding anchor and recursive expressions must have compatible data types and lengths.
• Anchor member: also called the initialization fullselect, this selects the starting row or rows and does not reference the CTE.
• UNION ALL: this joins the initialization and iterative fullselects in the recursive cycle. It preserves rows rather than performing duplicate elimination.
• Recursive member: also called the iterative fullselect, this references the CTE once and joins it to base data to produce the next generation.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
WITH HIERARCHY (NODE_ID, PARENT_ID, DEPTH) AS (
      -- Anchor member: choose the root
      SELECT NODE_ID, PARENT_ID, 0
        FROM NODE_TABLE
       WHERE NODE_ID = :ROOT_ID
 
    UNION ALL
 
      -- Recursive member: find children of the prior level
      SELECT C.NODE_ID, C.PARENT_ID, H.DEPTH + 1
        FROM HIERARCHY H
        INNER JOIN NODE_TABLE C
          ON C.PARENT_ID = H.NODE_ID
       WHERE H.DEPTH < 25
)
SELECT NODE_ID, PARENT_ID, DEPTH
  FROM HIERARCHY
 ORDER BY DEPTH, NODE_ID;

The anchor returns depth zero. The first iteration joins children to that root and gives them depth one. Later iterations repeat the same join for depths two, three, and so on. The condition H.DEPTH < 25 means that rows already at depth 25 cannot generate more children. This is a safety ceiling, not proof that the data is correct.

Walking an organization chart

The following query starts with a selected manager and walks downward through every reporting level. A PATH_NAME value makes the result understandable to people, while a numeric path can later help with cycle detection. CAST in the anchor deliberately establishes enough space for the longer values produced by later iterations.

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
WITH ORG (
  EMP_ID, EMP_NAME, MANAGER_ID, JOB_TITLE, DEPTH, PATH_NAME
) AS (
      SELECT E.EMP_ID,
             E.EMP_NAME,
             E.MANAGER_ID,
             E.JOB_TITLE,
             0,
             CAST(E.EMP_NAME AS VARCHAR(1000))
        FROM EMPLOYEE_TREE E
       WHERE E.EMP_ID = :START_EMP_ID
 
    UNION ALL
 
      SELECT C.EMP_ID,
             C.EMP_NAME,
             C.MANAGER_ID,
             C.JOB_TITLE,
             O.DEPTH + 1,
             O.PATH_NAME CONCAT ' > ' CONCAT C.EMP_NAME
        FROM ORG O
        INNER JOIN EMPLOYEE_TREE C
          ON C.MANAGER_ID = O.EMP_ID
       WHERE O.DEPTH < 20
)
SELECT EMP_ID, EMP_NAME, MANAGER_ID, JOB_TITLE, DEPTH, PATH_NAME
  FROM ORG
 ORDER BY DEPTH, EMP_NAME;

Changing the anchor changes the subtree. A root predicate such as MANAGER_ID IS NULL can seed every top-level employee, producing a forest if the table contains several independent organizations. A host variable is usually more reusable because the same static SQL shape can begin from any employee.

Do not assume recursive output arrives in tree order. SQL result order is undefined without ORDER BY. Ordering by DEPTH gives a breadth-like report grouped by level. Ordering by a stable key path can approximate a depth-first display. A name path is friendly, but names are not necessarily unique, so production sorting often carries a separate path built from fixed-width or otherwise sortable identifiers.

Cycle prevention: more than a depth limit

A malformed row that reports to itself is the simplest cycle. The predicate C.EMP_ID <> O.EMP_ID rejects it, but it does not catch A reporting to B, B to C, and C back to A. A maximum depth eventually stops that loop, yet duplicate rows still appear until the limit is reached. A stronger technique carries a delimited list of visited keys and rejects a candidate that is already present.

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_SAFE (
  EMP_ID, EMP_NAME, MANAGER_ID, DEPTH, VISITED_PATH
) AS (
      SELECT E.EMP_ID,
             E.EMP_NAME,
             E.MANAGER_ID,
             0,
             CAST('/' CONCAT VARCHAR(E.EMP_ID) CONCAT '/' AS VARCHAR(2000))
        FROM EMPLOYEE_TREE E
       WHERE E.EMP_ID = :START_EMP_ID
 
    UNION ALL
 
      SELECT C.EMP_ID,
             C.EMP_NAME,
             C.MANAGER_ID,
             O.DEPTH + 1,
             O.VISITED_PATH CONCAT VARCHAR(C.EMP_ID) CONCAT '/'
        FROM ORG_SAFE O
        INNER JOIN EMPLOYEE_TREE C
          ON C.MANAGER_ID = O.EMP_ID
       WHERE O.DEPTH < 50
         AND LOCATE(
               '/' CONCAT VARCHAR(C.EMP_ID) CONCAT '/',
               O.VISITED_PATH
             ) = 0
)
SELECT EMP_ID, EMP_NAME, MANAGER_ID, DEPTH
  FROM ORG_SAFE
 ORDER BY DEPTH, EMP_ID;

Delimiters matter. Without them, key 12 could falsely match key 112. The path also has a finite VARCHAR length, so choose a documented maximum depth and size the path for the longest key representation. The most reliable production approach combines several controls: validate parent assignments when data changes, reject direct self-links, detect previously visited keys, and keep a reasonable depth ceiling.

IBM documentation specifically cautions against infinite recursion. Db2 looks for the recognizable pattern of an integer counter incremented by a constant together with a limiting predicate such as counter_column < constant or host variable. If the expected termination pattern is absent, Db2 can issue a warning. Treat that warning as a design problem rather than assuming that the data will always terminate.

Bill-of-materials recursion

A bill of materials stores an assembly, its component, and the quantity needed at that edge. To explode a product, the anchor finds direct components of the chosen assembly. The recursive member then treats each component as a possible assembly and multiplies quantities along the route. This is the same seed-and-step pattern as the organization chart, even though the business meaning is different.

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
WITH BOM (
  TOP_PART, PARENT_PART, COMPONENT_PART, DEPTH, REQUIRED_QTY
) AS (
      SELECT B.ASSEMBLY_PART,
             B.ASSEMBLY_PART,
             B.COMPONENT_PART,
             1,
             DECIMAL(B.QUANTITY, 31, 6)
        FROM BILL_OF_MATERIAL B
       WHERE B.ASSEMBLY_PART = :TOP_PART
 
    UNION ALL
 
      SELECT X.TOP_PART,
             B.ASSEMBLY_PART,
             B.COMPONENT_PART,
             X.DEPTH + 1,
             DECIMAL(X.REQUIRED_QTY * B.QUANTITY, 31, 6)
        FROM BOM X
        INNER JOIN BILL_OF_MATERIAL B
          ON B.ASSEMBLY_PART = X.COMPONENT_PART
       WHERE X.DEPTH < 30
)
SELECT COMPONENT_PART,
       SUM(REQUIRED_QTY) AS TOTAL_REQUIRED_QTY
  FROM BOM
 GROUP BY COMPONENT_PART
 ORDER BY COMPONENT_PART;

Notice that SUM and GROUP BY are in the outer query, not in the recursive member. IBM documents restrictions on each fullselect in the recursion cycle: it must not contain aggregate functions, GROUP BY, or HAVING; it starts with SELECT or SELECT ALL rather than SELECT DISTINCT; and it contains only one reference to the recursive CTE in its FROM clause. Subqueries also must not participate in the recursion cycle. Keep the iterative member simple, then summarize the expanded rows outside it.

Quantity multiplication deserves deliberate numeric types. If the anchor infers a narrow decimal, later multiplication can overflow or lose scale. An explicit DECIMAL gives anchor and recursive expressions compatible types and enough precision for the expected product depth. Test worst-case quantities, not only a small demonstration assembly.

Ancestors, descendants, and direction

The join determines direction. To find descendants, join child.PARENT_ID to the current NODE_ID. To find ancestors, carry the current PARENT_ID and join it to the parent row’s NODE_ID. The CTE machinery is unchanged; only the edge direction changes.

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

Distance zero is the selected employee, distance one is the direct manager, and larger values move toward the root. If the report should contain managers only, filter DISTANCE > 0 in the outer SELECT. Keeping that display choice outside the recursion makes the traversal easier to reason about.

Correctness and performance checklist

• Index the relationship used by the recursive join. A descendant walk commonly needs an index beginning with PARENT_ID; an ancestor lookup usually benefits from the primary key on NODE_ID.
• Make the anchor selective. Starting from one root is cheaper and easier to verify than accidentally seeding every row.
• Select only columns needed during traversal. Wide paths and descriptions enlarge the intermediate result and work-file demand.
• Use compatible, intentionally sized anchor expressions. Strings may grow through concatenation, depth must remain numeric, and multiplied quantities need sufficient precision.
• Prevent cycles with data validation and query guards. UNION ALL does not remove a looping row, and replacing it with UNION is not the Db2 recursive pattern.
• Apply filtering as early as correctness permits. If only active nodes can be followed, put that predicate in the recursive member so inactive branches do not expand.
• Use EXPLAIN and test representative depth and fan-out. A five-row sample cannot reveal the cost of a production hierarchy in which every node has many children.

Recursive SQL can grow quickly. A tree with ten children per row has 10 rows at the first level, 100 at the second, and 1,000 at the third. Depth is only half the risk; branch width matters too. Restrict roots, prune irrelevant branches, and return only the rows the application needs. For frequently requested, mostly static hierarchies, a maintained path or closure design can be worth considering, but it trades simpler reads for more complex updates.

Common mistakes

Forgetting the stopping logic

“The hierarchy has no loops” is not a termination strategy. Add a depth counter and cycle detection, then monitor warnings and unexpectedly large results.

Using incompatible anchor types

A short anchor string cannot safely hold a long recursive path. CAST the anchor to the intended final type. Do the same for decimals whose precision changes through multiplication.

Expecting UNION ALL to remove duplicates

UNION ALL keeps every generated row. That is required for the recursive cycle, but it means converging graph routes can reach the same node more than once. Decide whether routes or unique nodes are the business result, and perform appropriate deduplication after expansion when needed.

Relying on accidental row order

The engine can choose different access paths over time. Always provide an outer ORDER BY for a stable report, and carry a path key when simple depth ordering is insufficient.

Explain it like I'm five

Imagine a family of toy boxes. The first box is the anchor. You open it and find more boxes. The rule that says “open every new box you find” is the recursive member. UNION ALL puts the first box and all later boxes on one list. A depth counter says “never open more than 20 layers,” and a visited list says “do not open the same box twice.” When there are no new boxes, the job is finished.

Exercises

• Write a recursive CTE that starts with every employee whose MANAGER_ID is null and returns EMP_ID, MANAGER_ID, and DEPTH.
• Change the organization query to return only rows at depths one through three without preventing those levels from being generated.
• Add a sortable identifier path to the employee traversal and use it in the final ORDER BY.
• Create sample rows A → B, B → C, and C → A. Explain how the visited-path predicate stops the cycle and how a depth limit alone behaves differently.
• Modify the bill-of-materials query to keep the full component path, then identify components reached through more than one route.
• Reverse a descendant query into an ancestor query by changing only the recursive join. Label the starting row distance zero.

Frequently asked questions

Is recursion always the best hierarchy model?

No. An adjacency list plus recursive CTE is flexible and easy to update. A closure table or stored path can make repeated reads faster, but each relationship change requires more maintenance. Choose from measured workload needs rather than hierarchy size alone.

Can a recursive query start from several roots?

Yes. The anchor can return multiple rows. Carry a ROOT_ID column through every iteration so each output row remains associated with the root that generated it.

What happens when no child matches?

That branch ends naturally. When the entire latest iteration produces no rows, recursion finishes and the outer query reads the accumulated result.