DB2 CTEs and recursive SQL constructs

WITH can mean two different things

SQL grammar assigns several jobs to common words. In this topic, the most important reading skill is noticing where WITH appears. A CTE begins before the main fullselect and is followed by a name, an optional column list, AS, and a parenthesized query. An isolation clause follows the query and contains a two-letter isolation code. The first form creates a name that the SQL can reference; the second controls how DB2 coordinates the read with concurrent units of work.

1
2
3
4
5
6
7
8
9
WITH ACTIVE_DEPTS AS (
  SELECT DEPTNO, DEPTNAME
    FROM DEPARTMENT
   WHERE ACTIVE_FLAG = 'Y'
)
SELECT DEPTNO, DEPTNAME
  FROM ACTIVE_DEPTS
 ORDER BY DEPTNO
 WITH CS;

This statement contains both forms. WITH ACTIVE_DEPTS AS (...) at the front defines the CTE. WITH CS at the end requests cursor-stability isolation. ACTIVE_DEPTS can be named in the FROM clause; CS cannot. Conversely, the CTE does not override the package isolation. Reading from left to right and identifying each clause by its position prevents a common source of confusion.

What a common table expression does

An ordinary, non-recursive CTE behaves like a temporary named query inside one statement. It does not add an object to the DB2 catalog, require CREATE authority, survive COMMIT, or hold data for a later statement. It is closer to a named nested table expression than to a declared global temporary table. The optimizer remains free to merge the CTE into the surrounding query or materialize an intermediate result when that is more efficient. Therefore, do not assume that writing a CTE forces a particular access path or guarantees that its query runs exactly once.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
WITH DEPARTMENT_TOTALS (WORKDEPT, EMPLOYEE_COUNT, TOTAL_PAY) AS (
  SELECT WORKDEPT,
         COUNT(*),
         SUM(SALARY)
    FROM EMPLOYEE
   GROUP BY WORKDEPT
)
SELECT D.DEPTNO,
       D.DEPTNAME,
       T.EMPLOYEE_COUNT,
       T.TOTAL_PAY
  FROM DEPARTMENT D
  INNER JOIN DEPARTMENT_TOTALS T
    ON T.WORKDEPT = D.DEPTNO
 WHERE T.EMPLOYEE_COUNT >= 5
 ORDER BY T.TOTAL_PAY DESC;

DEPARTMENT_TOTALS gives a meaningful name to the grouped result. Its explicit column list names the three output columns independently of the expressions inside the fullselect. The main SELECT then joins that result to DEPARTMENT as if it were a table. This structure lets a reader understand the aggregation first and the business filtering second.

Multiple CTEs and scope

One WITH clause can define multiple CTEs separated by commas. A later CTE can reference one defined earlier in the list, which makes it possible to build a query in stages. The CTE names exist only inside the containing statement. Choose names that do not accidentally hide an unqualified table name, and qualify base tables when ambiguity is possible.

1
2
3
4
5
6
7
8
9
10
11
12
13
WITH DEPARTMENT_TOTALS AS (
  SELECT WORKDEPT, SUM(SALARY) AS TOTAL_PAY
    FROM EMPLOYEE
   GROUP BY WORKDEPT
),
LARGE_DEPARTMENTS AS (
  SELECT WORKDEPT, TOTAL_PAY
    FROM DEPARTMENT_TOTALS
   WHERE TOTAL_PAY > 1000000
)
SELECT WORKDEPT, TOTAL_PAY
  FROM LARGE_DEPARTMENTS
 ORDER BY TOTAL_PAY DESC;

CTEs improve organization, but they should still represent useful logical steps. Splitting every expression into a separate CTE can make a short query harder to follow. Use one when a result is reused, when a stage has a clear business meaning, or when recursion is needed. Use EXPLAIN rather than guessing whether a rewritten query performs better.

Recursive CTE anatomy

A CTE becomes recursive when its definition refers to its own name. DB2 starts with one or more seed rows, uses those rows to find the next set, then repeats the recursive step with each newly produced set. This continues until the recursive member returns no rows. The conceptual process is iteration, although the SQL remains declarative: the optimizer and database engine decide how to execute it.

• The anchor member, also called the initial fullselect, returns the starting rows. It does not reference the recursive CTE.
• UNION ALL joins the anchor to the recursive member. For DB2 recursive CTE syntax, use UNION ALL rather than relying on UNION to remove duplicates.
• The recursive member, also called the iterative fullselect, references the CTE and normally joins its current rows to a base table to find children.
• An explicit CTE column list establishes the recursive result shape. Corresponding anchor and recursive expressions must have compatible data types and lengths.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
WITH ORG_TREE
     (EMP_ID, EMP_NAME, MANAGER_ID, TREE_LEVEL) AS (
  SELECT E.EMP_ID,
         E.EMP_NAME,
         E.MANAGER_ID,
         0
    FROM EMPLOYEE_ORG E
   WHERE E.EMP_ID = 100
 
  UNION ALL
 
  SELECT C.EMP_ID,
         C.EMP_NAME,
         C.MANAGER_ID,
         P.TREE_LEVEL + 1
    FROM ORG_TREE P
    INNER JOIN EMPLOYEE_ORG C
      ON C.MANAGER_ID = P.EMP_ID
   WHERE P.TREE_LEVEL < 20
)
SELECT EMP_ID, EMP_NAME, MANAGER_ID, TREE_LEVEL
  FROM ORG_TREE
 ORDER BY TREE_LEVEL, EMP_ID;

The anchor finds employee 100 and labels that row level zero. The recursive member treats rows from ORG_TREE as parents and joins EMPLOYEE_ORG to find direct reports. Those reports become input for another iteration, producing grandchildren and deeper descendants. The condition P.TREE_LEVEL < 20 is a defensive maximum-depth limit. If the organization has no deeper rows, normal termination occurs earlier because the join produces an empty set.

Why UNION ALL matters

UNION ALL preserves every row generated by the anchor and each iteration. It also avoids the duplicate-elimination work associated with UNION. More importantly, duplicate elimination is not a substitute for cycle detection. Two legitimate employees can share similar attributes, while one bad relationship can revisit the same employee through a longer path. Recursive logic should identify visited keys directly rather than hoping UNION will make malformed data safe.

RECURSIVE keyword and DB2 platform differences

You will often see examples from other products written as WITH RECURSIVE tree AS (...). Within the DB2 family, exact grammar differs by platform and release. Db2 for z/OS commonly expresses recursion with WITH and a self-referencing CTE; it does not require the RECURSIVE keyword in the standard z/OS pattern. Db2 LUW and other SQL implementations can show WITH RECURSIVE explicitly.

The important idea is not the extra keyword: it is the self-reference from the recursive member back to the CTE. When moving SQL between Db2 for z/OS, Db2 LUW, Db2 for i, or a different database, verify the target SQL reference. Do not add or remove RECURSIVE solely because an internet example uses a different product. The anchor, UNION ALL, recursive member, compatible result columns, and termination behavior remain the concepts to recognize.

Termination and cycle safeguards

A well-formed tree naturally terminates at leaf rows because no children match. Production data is not always a well-formed tree. An employee can accidentally manage itself, A can point to B while B points back to A, or a longer relationship can loop through several rows. Without a safeguard, the recursive member can keep rediscovering keys, consume work file space, and eventually fail after excessive processing.

• Add a maximum depth that is comfortably above the valid business depth. A 20-level cap is reasonable for an organization expected to have fewer than 12 levels.
• Carry a visited path and reject a child whose key is already present. Use unambiguous delimiters so employee 12 does not falsely match employee 112.
• Reject obvious self-links, such as C.EMP_ID <> C.MANAGER_ID, but remember that this alone does not catch A-to-B-to-A cycles.
• Enforce valid relationships with constraints and data-quality checks where the model permits. Query safeguards are a final defense, not a replacement for clean data.
• Test with missing parents, duplicate business keys, deep branches, leaf roots, and deliberate cycles before using the query in production.

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
WITH ORG_TREE
     (EMP_ID, EMP_NAME, MANAGER_ID, TREE_LEVEL, VISITED_PATH) AS (
  SELECT E.EMP_ID,
         E.EMP_NAME,
         E.MANAGER_ID,
         0,
         CAST('/' CONCAT DIGITS(E.EMP_ID) CONCAT '/' AS VARCHAR(2000))
    FROM EMPLOYEE_ORG E
   WHERE E.EMP_ID = 100
 
  UNION ALL
 
  SELECT C.EMP_ID,
         C.EMP_NAME,
         C.MANAGER_ID,
         P.TREE_LEVEL + 1,
         P.VISITED_PATH CONCAT DIGITS(C.EMP_ID) CONCAT '/'
    FROM ORG_TREE P
    INNER JOIN EMPLOYEE_ORG C
      ON C.MANAGER_ID = P.EMP_ID
   WHERE P.TREE_LEVEL < 20
     AND LOCATE(
           '/' CONCAT DIGITS(C.EMP_ID) CONCAT '/',
           P.VISITED_PATH
         ) = 0
)
SELECT EMP_ID, EMP_NAME, MANAGER_ID, TREE_LEVEL
  FROM ORG_TREE
 ORDER BY TREE_LEVEL, EMP_ID;

VISITED_PATH records each key with slash delimiters. Before accepting a child, LOCATE checks whether that delimited key already exists in the path. The exact CAST length and key formatting must fit the application's maximum depth and key type. This example combines cycle detection with a depth cap because independent safeguards provide better protection than either one alone.

Isolation clauses: WITH UR, CS, RS, and RR

Isolation controls what a reader can observe while other units of work insert, update, or delete rows. If a statement does not specify an isolation clause, the package or plan bind option supplies the isolation level. A trailing clause can request an allowed statement-level choice. Isolation does not change the rows generated by CTE logic in a single-user world; it changes the concurrency guarantees under which DB2 reads the base data.

WITH UR: speed with dirty-read risk

Uncommitted read can observe a value written by another unit of work before that writer commits. If the writer rolls back, the value seen by the report never becomes official. A multi-row result can also combine states that never existed as one committed snapshot. UR can be useful for approximate monitoring, exploratory counts, and reports where waiting is more harmful than seeing transient data. It is a poor choice for balances, inventory commitments, compliance decisions, or any result that drives an irreversible action.

WITH CS: common transactional balance

Cursor stability protects the row currently positioned under a cursor according to DB2 locking rules, but rows read earlier can change after the cursor moves. Repeating the search within the same unit of work can therefore return changed values or a different set. CS usually offers a practical compromise for online browsing because it limits how long many read locks are retained while still avoiding dirty reads.

WITH RS: stable qualifying rows

Read stability retains protection for rows that qualified when read, so another unit of work cannot change or delete those qualifying rows until the reader's unit of work ends. However, another transaction can insert a new row that satisfies the predicate. Repeating the query can therefore reveal a phantom. RS is useful when existing qualifying rows must not change during a business operation but preventing every new qualifying row would impose unnecessary contention.

WITH RR: strongest repeatability

Repeatable read protects the qualifying result and prevents phantom rows, giving the strongest of these four guarantees. The cost is broader or longer-lived locking and a greater chance that writers wait behind the reader. Large scans at RR can have a serious concurrency impact. Keep the unit of work short, index search predicates appropriately, and choose RR because the business rule requires it, not because stronger sounds safer.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
WITH ORG_TREE
     (EMP_ID, MANAGER_ID, TREE_LEVEL) AS (
  SELECT EMP_ID, MANAGER_ID, 0
    FROM EMPLOYEE_ORG
   WHERE EMP_ID = 100
 
  UNION ALL
 
  SELECT C.EMP_ID, C.MANAGER_ID, P.TREE_LEVEL + 1
    FROM ORG_TREE P
    INNER JOIN EMPLOYEE_ORG C
      ON C.MANAGER_ID = P.EMP_ID
   WHERE P.TREE_LEVEL < 20
)
SELECT EMP_ID, MANAGER_ID, TREE_LEVEL
  FROM ORG_TREE
 ORDER BY TREE_LEVEL, EMP_ID
 WITH RS;

Again, the first WITH introduces ORG_TREE; the final WITH RS selects read stability. Choosing RS does not fix a cycle, and adding cycle detection does not choose an isolation level. They address separate risks: recursive safeguards control traversal of the data, while isolation controls concurrent changes to data being read.

Choosing between CTEs and alternatives

Use a CTE when naming an intermediate result clarifies the statement, when the same logical result is referenced more than once, or when the query must walk an unknown number of hierarchy levels. A nested table expression can be simpler for a short result used once. A view is appropriate when many statements need a stable, authorized abstraction. A temporary table can be preferable when several separate statements must share staged data, when indexes on staged results are valuable, or when you need explicit control over when the intermediate result is populated.

Recursive SQL is especially valuable when hierarchy depth varies. If the business model guarantees exactly three levels, fixed self-joins might be easier to tune and understand. For an org chart, category tree, bill of materials, or dependency graph with variable depth, recursion avoids guessing how many joins to write. Always inspect EXPLAIN output and test representative depth and fan-out because a tiny sample hierarchy can hide the cost of a broad production tree.

Explain it like I'm 5

Imagine sorting a box of family photographs. A normal CTE is a sticky note that says “photos from this summer,” so you can use that pile by name while finishing one project. When the project is over, the sticky note and pile name disappear. A recursive CTE starts with one person, the anchor, then repeatedly asks, “Who are this person's children?” The recursive member keeps asking for the next generation until nobody new is found.

A cycle safeguard is a checklist of people already visited, so a mixed-up photo saying that a child is also their own grandparent does not make you walk in circles forever. WITH UR, CS, RS, and RR are different rules about whether someone else may rearrange the photos while you look. UR lets you peek before they finish; RR asks everyone to leave the whole relevant arrangement alone until you are done.

Exercises

1. Rewrite a query that repeats the same department salary aggregation twice so it defines DEPARTMENT_TOTALS once as a CTE and references that name from the main query.
2. Create an ORG_TREE recursive CTE starting with a manager supplied as a host variable. Return employee ID, manager ID, employee name, and tree level.
3. Label the anchor member, UNION ALL, and recursive member in your query. Explain why the anchor cannot reference ORG_TREE and why the recursive member must reference it.
4. Add both a maximum depth of 15 and a visited-path cycle check. Test the query with a self-link and with a three-row A-to-B-to-C-to-A cycle.
5. Run EXPLAIN for a nested table expression and an equivalent CTE. Compare access paths instead of assuming the CTE is always materialized.
6. Select an isolation level for an approximate dashboard count, an employee directory browse, a workflow that must retain already selected rows, and a reconciliation query that must prevent phantoms. Defend each choice.
7. Write one statement containing a CTE at the beginning and WITH CS at the end. In plain language, describe what each occurrence of WITH controls.
8. Find a WITH RECURSIVE example written for another database platform and adapt it to the documented Db2 for z/OS form. List every syntax assumption you verified.