Write CTEs in DB2 for z/OS

Prerequisites and the basic WITH form

Before writing a CTE, be comfortable with SELECT, FROM, WHERE, GROUP BY, joins, and aliases. A CTE does not replace those clauses; it organizes them. The ordinary form begins with WITH, followed by a table-like name, optional result-column names, AS, and a parenthesized fullselect. The main fullselect follows the closing parenthesis.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
WITH ACTIVE_EMPLOYEES AS (
  SELECT EMPNO,
         FIRSTNME,
         LASTNAME,
         WORKDEPT
    FROM EMP
   WHERE EMP_STATUS = 'A'
)
SELECT EMPNO,
       FIRSTNME,
       LASTNAME,
       WORKDEPT
  FROM ACTIVE_EMPLOYEES
 ORDER BY WORKDEPT, LASTNAME;

Read this statement in two stages. ACTIVE_EMPLOYEES defines the rows and columns that form the intermediate result. The main SELECT consumes that named result. The name is visible only within this statement: another SELECT submitted afterward cannot refer to ACTIVE_EMPLOYEES. No CREATE statement runs, no catalog row is added, and there is nothing to DROP.

• Give the CTE a name that describes its result, such as ACTIVE_EMPLOYEES or MONTHLY_SALES, rather than a procedural step name such as TEMP1.
• Keep predicates near the data they logically constrain. A filter that defines “active” belongs naturally in ACTIVE_EMPLOYEES; a filter used only by one final report may be clearer in the outer SELECT.
• Qualify columns when several sources expose the same name. CTEs improve structure, but they do not remove ordinary ambiguity rules.
• Do not confuse the leading WITH that defines a CTE with a trailing isolation clause such as WITH UR or WITH CS. Position and grammar reveal which meaning applies.

Name CTE result columns deliberately

You can name output columns inside the fullselect with AS aliases, or place an explicit column list immediately after the CTE name. An explicit list is a compact declaration of the CTE's result shape. It is especially helpful when the fullselect contains aggregate functions, arithmetic, constants, scalar functions, or other expressions whose generated names would be unclear.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
WITH DEPARTMENT_PAY
     (DEPT_NO, EMPLOYEE_COUNT, TOTAL_SALARY, AVERAGE_SALARY) AS (
  SELECT WORKDEPT,
         COUNT(*),
         SUM(SALARY),
         DECIMAL(AVG(SALARY), 11, 2)
    FROM EMP
   GROUP BY WORKDEPT
)
SELECT DEPT_NO,
       EMPLOYEE_COUNT,
       TOTAL_SALARY,
       AVERAGE_SALARY
  FROM DEPARTMENT_PAY
 WHERE EMPLOYEE_COUNT >= 5
 ORDER BY TOTAL_SALARY DESC;

The four names after DEPARTMENT_PAY correspond by position to the four expressions in its fullselect. The number of names must match the number of result columns, and each name in that list must be unique. The data types still come from the corresponding expressions; writing AVERAGE_SALARY in the list does not change its type. The explicit DECIMAL cast controls the type and scale while the column list controls the name.

You could instead write COUNT(*) AS EMPLOYEE_COUNT and similar aliases inside the SELECT. Both forms can be readable. Prefer one consistent style within a CTE. The explicit list is particularly useful when readers should see the whole result contract before reading a long fullselect. Inline aliases can be easier when each name should sit beside the expression that creates it.

Write multiple CTEs in dependency order

One WITH clause can introduce multiple CTEs. Separate definitions with commas; do not repeat WITH before every definition. A later ordinary CTE can reference a CTE defined earlier in the same list. That rule makes ordering important: define source-like results first, transformations second, and the final result last. Each CTE name in the statement must be unique.

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
WITH PAID_ORDERS AS (
  SELECT O.ORDER_ID,
         O.CUSTOMER_ID,
         O.ORDER_DATE,
         O.ORDER_TOTAL
    FROM CUSTOMER_ORDER O
   WHERE O.PAYMENT_STATUS = 'PAID'
),
CUSTOMER_TOTALS AS (
  SELECT CUSTOMER_ID,
         COUNT(*) AS ORDER_COUNT,
         SUM(ORDER_TOTAL) AS LIFETIME_VALUE
    FROM PAID_ORDERS
   GROUP BY CUSTOMER_ID
),
HIGH_VALUE_CUSTOMERS AS (
  SELECT CUSTOMER_ID,
         ORDER_COUNT,
         LIFETIME_VALUE
    FROM CUSTOMER_TOTALS
   WHERE ORDER_COUNT >= 3
     AND LIFETIME_VALUE >= 10000
)
SELECT C.CUSTOMER_ID,
       C.CUSTOMER_NAME,
       H.ORDER_COUNT,
       H.LIFETIME_VALUE
  FROM HIGH_VALUE_CUSTOMERS H
  INNER JOIN CUSTOMER C
    ON C.CUSTOMER_ID = H.CUSTOMER_ID
 ORDER BY H.LIFETIME_VALUE DESC;

PAID_ORDERS has no CTE dependency, so it appears first. CUSTOMER_TOTALS consumes it, and HIGH_VALUE_CUSTOMERS consumes CUSTOMER_TOTALS. The main SELECT adds the customer name only after the qualifying customer IDs are known. This ordering creates a readable top-to-bottom explanation of the data transformation.

Dependency order is not permission to split every clause into a separate CTE. Too many tiny layers force readers to jump between names to reconstruct a simple query. Give each layer a coherent business meaning. If a CTE merely renames SELECT * from another CTE without clarifying or transforming anything, it is probably unnecessary.

Reuse one named result carefully

IBM notes that a CTE can be referenced many times in its statement and that those references share the CTE's result table. That logical behavior is valuable when two parts of a fullselect must use the same result definition. It also avoids copying a long subquery and risking subtle differences when one copy is later changed.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
WITH TEAM_PAY AS (
  SELECT WORKDEPT,
         SUM(SALARY) AS TOTAL_PAY
    FROM EMP
   GROUP BY WORKDEPT
)
SELECT A.WORKDEPT,
       A.TOTAL_PAY,
       B.WORKDEPT AS COMPARED_DEPT,
       B.TOTAL_PAY AS COMPARED_PAY
  FROM TEAM_PAY A
  INNER JOIN TEAM_PAY B
    ON B.TOTAL_PAY > A.TOTAL_PAY
 WHERE A.WORKDEPT = :HV_DEPT
 ORDER BY B.TOTAL_PAY;

TEAM_PAY appears twice under different correlation names. Logically, both references use the result defined by the same CTE. Do not turn that statement-level rule into a physical promise that Db2 executes the text once or always stores one copy. The optimizer decides how to implement the query while preserving the required result.

Understand merge and materialization

A CTE is SQL syntax, not a command to create a work file. Db2 may merge a table expression by incorporating its logic into the surrounding query. Merging can expose predicates and joins to broader optimization. Db2 may instead materialize an intermediate result, typically in a work file, when semantics require it or the estimated cost favors it. Operations such as duplicate-removing set operations, some complex joins, grouping, sorting, and reuse can affect the available choices, but there is no reliable rule that every CTE is materialized or every simple CTE is merged.

This distinction matters when tuning. A developer might expect a CTE predicate to reduce rows before a join, yet Db2 can legally transform the query. Another developer might expect a reused CTE to be calculated only once, yet physical processing remains an access-path decision. Use EXPLAIN to inspect query blocks, access methods, join order, estimated cardinalities, and work-file indicators. IBM's PLAN_TABLE documentation identifies TABLE_TYPE values used to distinguish non-materialized query blocks from work-file materialization in relevant access paths.

• Run RUNSTATS as appropriate so cardinality and distribution estimates represent the production data.
• Compare equivalent CTE and derived-table forms with EXPLAIN rather than assuming one syntax is faster.
• Measure elapsed time, CPU, getpages, sorts, and work-file use under representative parameter values and data volumes.
• Preserve stageable, indexable predicates. Readability improvements do not compensate for wrapping an indexed column in an avoidable function.
• Rebind or prepare statements according to normal change controls after SQL, statistics, index, or subsystem changes that can affect access paths.

Choose CTE, derived table, view, or temporary table

These features can all name or contain intermediate query logic, but their scope and operational behavior differ. Select the smallest tool that matches the required lifetime and reuse.

CTE

Use a CTE when one statement benefits from a named intermediate result, several parts of a fullselect must share one definition, host variables help construct a result, or recursion is required. It keeps related logic beside the statement and requires no persistent object. It cannot be reused by the next statement.

Derived table

A derived table, also called a nested table expression, is a parenthesized fullselect in the FROM clause. It is concise when used once and close to its consumer. A CTE is often easier to read when the expression is long, referenced more than once, or one of several named stages. Neither spelling alone guarantees a better access path.

View

A view is a catalog object that many statements and users can reference. Choose it for a durable abstraction, centralized security model, or shared interface. Creating and changing it requires object management and authorization. A CTE is preferable when general reuse is unnecessary and the definition belongs with one statement.

Declared global temporary table

A temporary table suits a multi-statement process. The application can populate it, read it in later statements, change the staged rows, and sometimes define supporting indexes according to the selected temporary-table design. That explicit lifecycle can be useful, but it adds statements, logging and commit considerations, and operational choices. A CTE keeps the work declarative and lets Db2 optimize one complete statement.

Use CTEs with DML only where Db2 for z/OS supports them

IBM documents common table expressions wherever a fullselect is accepted, including SELECT, SELECT INTO, INSERT, and CREATE VIEW examples. A practical data-change pattern is an INSERT whose source fullselect reads from a CTE. In Db2 for z/OS INSERT syntax, the WITH clause follows the target and target-column list, then the source fullselect consumes the CTE.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
INSERT INTO MONTHLY_DEPT_SUMMARY
       (SUMMARY_MONTH, DEPT_NO, ORDER_COUNT, SALES_TOTAL)
WITH MONTH_ORDERS AS (
  SELECT DEPT_NO,
         ORDER_ID,
         ORDER_TOTAL
    FROM CUSTOMER_ORDER
   WHERE ORDER_DATE >= :HV_MONTH_START
     AND ORDER_DATE <  :HV_NEXT_MONTH
),
DEPT_TOTALS AS (
  SELECT DEPT_NO,
         COUNT(*) AS ORDER_COUNT,
         SUM(ORDER_TOTAL) AS SALES_TOTAL
    FROM MONTH_ORDERS
   GROUP BY DEPT_NO
)
SELECT :HV_MONTH_START,
       DEPT_NO,
       ORDER_COUNT,
       SALES_TOTAL
  FROM DEPT_TOTALS;

This single statement derives monthly rows and inserts them into the summary table. The CTE is the source, not the target. Validate target-column types, nullability, generated-column rules, duplicate-key behavior, and authorization exactly as you would for any INSERT fullselect.

Do not copy a WITH UPDATE or WITH DELETE example from Db2 LUW, another database, or a newer grammar and assume it is valid on your Db2 for z/OS function level. IBM's common-table-expression guidance specifically presents the feature as an alternative to a view when positioned updates or deletes are not used. If an UPDATE or DELETE must identify rows through complex logic, verify the target statement's z/OS SQL reference and use its supported subquery, searched condition, MERGE, or data-change-table-reference forms. Platform name and release matter.

Write for readability without hiding cost

Good CTE names make SQL read like a data explanation. Put foundational row selection before aggregation, aggregation before business qualification, and enrichment joins near the point where their columns are needed. Select only useful columns rather than carrying SELECT * through every stage. A narrow result is easier to understand and can reduce unnecessary data movement if the access path materializes it.

Readability and performance are partners, not automatic synonyms. A clear structure makes predicates, join keys, and cardinality changes easier to review, but each CTE boundary can also hide an accidental many-to-many join, an unnecessary DISTINCT, or an aggregation performed before the right filter. Annotate unusual business rules in source control, not every obvious SQL keyword. Then confirm behavior with EXPLAIN and production-like measurements.

1. State the required final rows and columns before choosing CTE boundaries.
2. Define each named result in one sentence; if you cannot, its purpose may be mixed.
3. Place CTEs in dependency order and use unique, business-oriented names.
4. Project only columns needed by later stages.
5. Check join cardinality at every stage, especially before aggregation.
6. Compare the access path before and after a substantial rewrite.
7. Test empty input, nulls, duplicate keys, skewed values, and high-volume cases.

Common CTE errors and how to fix them

Column count or name mismatch

If an explicit list has three names but the fullselect returns four columns, the CTE result cannot be mapped. Duplicate names in the explicit list are also invalid. Count expressions carefully, make names unique, and remember that SELECT * can change when the underlying table definition changes.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- Incorrect: two names for three result columns
WITH DEPT_PAY (DEPT_NO, TOTAL_PAY) AS (
  SELECT WORKDEPT, COUNT(*), SUM(SALARY)
    FROM EMP
   GROUP BY WORKDEPT
)
SELECT *
  FROM DEPT_PAY;
 
-- Correct
WITH DEPT_PAY (DEPT_NO, EMPLOYEE_COUNT, TOTAL_PAY) AS (
  SELECT WORKDEPT, COUNT(*), SUM(SALARY)
    FROM EMP
   GROUP BY WORKDEPT
)
SELECT DEPT_NO, EMPLOYEE_COUNT, TOTAL_PAY
  FROM DEPT_PAY;

Forward reference or duplicate CTE name

A non-recursive CTE cannot depend on a later definition. Move the dependency earlier. Also ensure every CTE name is defined only once. If a CTE name matches a catalog table name, it can hide that unqualified object within the statement; qualify the base table with its schema or choose a less confusing CTE name.

Missing parentheses, comma, or AS

Every CTE fullselect is enclosed in parentheses. A comma separates adjacent CTEs, but there is no comma between the last CTE and the main fullselect. Format each definition consistently so punctuation is visible.

Ambiguous columns

Joining a CTE to a table that exposes the same column name can produce ambiguity. Assign correlation names and qualify references, such as C.CUSTOMER_ID and T.CUSTOMER_ID. This also tells reviewers which source owns each value.

Unexpected duplicate rows

A CTE does not remove duplicates unless its fullselect explicitly does so. Before adding DISTINCT, inspect join predicates and expected relationship cardinality. An incomplete join often multiplies rows, and DISTINCT can hide the defect while adding sort or hashing cost.

Unexpected poor performance

Check whether predicates remain indexable, whether statistics describe the data, whether a result was materialized, and whether estimated row counts match reality. Compare against a derived-table or direct-join form only after confirming logical equivalence. The remedy may be statistics, indexing, predicate design, or join correction rather than removing WITH.

Verify the result and access path

Verification has two parts. First prove correctness with known test data. For each CTE, temporarily make that CTE the source of the final SELECT and inspect its rows. Check boundary dates, null handling, duplicate business keys, empty departments, negative amounts, and host-variable extremes. Reconcile aggregates to a trusted query or independently calculated total.

Second, prove operational suitability. Run EXPLAIN in the target environment and review access paths with your site's established tooling. Confirm matching index use, join order, estimated cardinality, sorts, materialization, parallelism, and work-file demand. Execute with representative volumes and parameter values. A query that is fast for one department can behave differently for all departments.

The IBM Db2 13 for z/OS documentation pages titled “Common table expressions”, “INSERT statement”, and “Using EXPLAIN to determine UNION, INTERSECT, and EXCEPT activity and query rewrite” are primary references for the scope, INSERT grammar, and merge-versus-materialization concepts covered here. Always select the documentation version that matches the subsystem release and active function level.

Explain it like I'm 5

Imagine making lunch from a crowded kitchen. First you put all the washed vegetables in a bowl and label it WASHED_VEGETABLES. Then you take that bowl, add cheese, and label the new bowl SALAD. Finally you put SALAD on the table. Those labels are like CTE names: they help you describe each part of one lunch-making job.

When lunch is finished, the labels do not become permanent cupboards. Db2 may not even use real bowls; it can combine steps if that is faster, as long as the same lunch appears. A view is more like a recipe posted for everyone to reuse. A temporary table is more like a container kept around while several separate jobs use its contents.

Exercises

1. Rewrite a derived table that totals salary by WORKDEPT as a CTE named DEPARTMENT_PAY. Give every aggregate result an explicit column name.
2. Build three CTEs in dependency order: current-year orders, customer totals, and customers above a host-variable threshold. Explain why reversing the first two definitions fails.
3. Reference one department-total CTE twice to compare a selected department with departments having a greater total. Qualify every repeated column.
4. Write an INSERT fullselect that loads a reporting table from a grouped CTE. Verify target column order, types, nullability, and expected row count.
5. Take a five-layer CTE statement and remove layers that have no distinct business meaning. Confirm that the old and new forms return the same rows.
6. Use EXPLAIN on equivalent CTE and derived-table forms. Record whether Db2 merges or materializes relevant query blocks and compare estimated cardinalities.
7. Create test data for empty input, duplicate join keys, null salary, and a department with unusually many employees. Predict each result before executing.
8. Diagnose a CTE with a mismatched explicit column list, a duplicate CTE name, a forward reference, and an ambiguous outer column. Write the corrected statement.