CTEs, recursive SQL, and WITH clauses in DB2

Three different WITH spellings

You can use more than one in the same statement: a CTE at the front, ORDER BY and FETCH FIRST WITH TIES in the middle, and WITH CS at the end. Read them as separate clauses, not as one “WITH block.”

Common table expressions

A CTE names a fullselect for the rest of the statement. It is not a created view and not a declared global temporary table. It lives only for that SQL statement. Use it to avoid repeating a subquery, to name an intermediate result, or to split a hard query into readable steps.

1
2
3
4
5
6
7
8
9
10
WITH DEPTPAY AS (
  SELECT WORKDEPT, AVG(SALARY) AS AVGSAL, COUNT(*) AS EMPCNT
    FROM DSN8C10.EMP
   GROUP BY WORKDEPT
)
SELECT D.DEPTNO, D.DEPTNAME, P.AVGSAL, P.EMPCNT
  FROM DSN8C10.DEPT D
  INNER JOIN DEPTPAY P
    ON P.WORKDEPT = D.DEPTNO
 WHERE P.AVGSAL > 50000;

Rules of thumb:

• Column names — specify them in the CTE heading or in the inner select list. Recursive CTEs require an explicit column list.
• Multiple CTEs — comma-separated. A later CTE may read an earlier one.
• Referenced more than once — the optimizer may materialize or inline. Do not assume a CTE is always “executed once” like a temp table; it is a named query.
• Versus nested table expression — a FROM (SELECT …) AS X subquery is similar. A CTE is nicer when the same result is needed twice or the SQL is long.

Recursive common table expressions

If a fullselect in the CTE refers to the CTE’s own name, the CTE is recursive. Queries using recursion support bill of materials, org charts, and network walks. The standard shape is:

• Initial (anchor) fullselect — the seed rows; must not reference the CTE.
• UNION ALL — required for the recursive cycle (not UNION DISTINCT).
• Iterative fullselect — reads the CTE and produces the next generation (children, next part, next level).

1
2
3
4
5
6
7
8
9
10
11
12
13
WITH RPL (LEVEL, DEPTNO, DEPTNAME, ADMRDEPT) AS (
      SELECT 1, DEPTNO, DEPTNAME, ADMRDEPT
        FROM DSN8C10.DEPT
       WHERE DEPTNO = 'A00'
    UNION ALL
      SELECT RPL.LEVEL + 1, D.DEPTNO, D.DEPTNAME, D.ADMRDEPT
        FROM RPL, DSN8C10.DEPT D
       WHERE D.ADMRDEPT = RPL.DEPTNO
         AND D.DEPTNO <> D.ADMRDEPT
)
SELECT LEVEL, DEPTNO, DEPTNAME, ADMRDEPT
  FROM RPL
 ORDER BY LEVEL, DEPTNO;

The seed is department A00. Each iteration attaches departments whose ADMRDEPT equals the previous level’s DEPTNO. LEVEL counts depth. The extra predicate DEPTNO <> ADMRDEPT avoids a row that points at itself looping forever.

Practical guards:

• Cycle detection — real org data can have loops. Keep a path string or stop when a key already appeared in the path.
• MAX depth — add WHERE LEVEL < 20 in the iterative branch as a safety cap while you learn the data.
• Column list — name every column; types of corresponding UNION ALL columns must be compatible.
• Restrictions — the iterative fullselect is limited (typically no DISTINCT, and GROUP BY / aggregation in the recursive member is restricted). Keep the step simple: join parent keys to the child table.

Recursion is not a substitute for a numbers table when you only need 1..N. It is the right tool when each row’s children are found by joining back to the same table.

WITH TIES

WITH TIES belongs to the fetch-first clause, not to CTEs. It changes what “first n rows” means when ORDER BY has ties at the cutoff.

1
2
3
4
SELECT WORKDEPT, LASTNAME, SALARY
  FROM DSN8C10.EMP
 ORDER BY SALARY DESC
 FETCH FIRST 5 ROWS WITH TIES;

FETCH FIRST 5 ROWS ONLY returns at most five rows. If the fifth and sixth employees share the same salary, ONLY can split that tie. WITH TIES keeps every row whose ORDER BY key matches the last included row, so you might get more than five rows. You need ORDER BY for WITH TIES to be meaningful. This is a Db2 12 pagination feature (application compatibility / function level permitting).

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

A SELECT can end with an isolation-clause that overrides the package bind option for that statement only.

1
2
3
4
5
6
7
8
9
SELECT EMPNO, LASTNAME, SALARY
  FROM DSN8C10.EMP
 WHERE WORKDEPT = 'A00'
  WITH UR;
 
SELECT EMPNO, LASTNAME
  FROM DSN8C10.EMP
 WHERE EMPNO = '000010'
  WITH RR;

• WITH UR — uncommitted read. You can see changes another transaction has not committed (and might roll back). Fast for dirty-tolerant reports and many read-only warehouses. Not for money you must see committed.
• WITH CS — cursor stability. The current row is stable while you hold it; previously fetched rows may change. Common bind default.
• WITH RS — read stability. Rows that satisfied the query stay stable until commit; new rows (phantoms) can still appear.
• WITH RR — repeatable read. Strongest isolation; phantoms are prevented. More locking, more contention.
• Implicit isolation — if you omit WITH UR/CS/RS/RR, the statement uses the isolation of the plan or package (ISOLATION(CS) and friends at bind time). Interactive tools often default to CS or UR depending on the shop.

Isolation is not a CTE. Putting WITH UR next to WITH mycte AS (…) is two clauses in one statement, not one keyword with two meanings. BIND still matters: a package bound UR already reads dirty unless a statement raises isolation with WITH CS/RS/RR.

Related read-only hints: FOR READ ONLY (or FOR FETCH ONLY) tells Db2 you will not use positioned UPDATE/DELETE on the cursor. Isolation and FOR READ ONLY solve different problems—locks versus cursor intent.

Explain It Like I'm Five

A CTE is a nickname for a pile of blocks you built so you can point at that pile twice without rebuilding it in the sentence. Recursive SQL is stacking blocks: start with the floor (seed), then keep stacking the next block that sits on the last one (UNION ALL) until no block fits. WITH TIES is “if two kids tied for fifth place, both get a ribbon.” WITH UR is “peek at the other table before they finish writing”—sometimes you see a scribble that gets erased.

Exercises

1. Rewrite a duplicated subquery (average salary by department used twice) as a CTE.
2. Write a recursive CTE that lists DEPTNO and LEVEL starting from a root department.
3. Add a predicate that stops recursion at LEVEL 8.
4. Explain the difference between FETCH FIRST 3 ROWS ONLY and FETCH FIRST 3 ROWS WITH TIES on SALARY DESC when two employees share third place.
5. Pick WITH UR or WITH RR for (a) a dirty-tolerant count of rows in a staging table and (b) a funds-transfer inquiry that must not see uncommitted updates.

Frequently asked questions

Is WITH RECURSIVE required on z/OS?

A CTE that references itself is recursive. Some products require the RECURSIVE keyword; on Db2 for z/OS the self-reference plus UNION ALL is the documented pattern. Follow your SQL Reference for your function level if RECURSIVE is accepted as optional syntax.

Can a CTE call itself twice in one step?

Keep the iterative member to a single reference of the CTE joined to a base table. Fancy multiple self-references are restricted. If you need two hierarchies, use two CTEs.

Does WITH UR skip logging?

No. UR is about read isolation, not about whether Db2 logs updates. It does not make INSERT unlogged.