Temporary Tables in Db2 for i – Efficient, Flexible, but Plan Carefully

Declared Global Temporary Tables, CTEs, QTEMP, TRUNCATE, and Indexes – What Really Matters on IBM i

What Are Temporary Tables?

On IBM i you work with DECLARE GLOBAL TEMPORARY TABLE (abbreviated DGTT). The table is not persistent, does not appear in the regular system catalog, and cannot be shared across application contexts. Each connection or relevant job context receives its own instance.

DECLARE GLOBAL TEMPORARY TABLE SESSION.TEMP_EMP (
    EMPNO CHAR(6) NOT NULL,
    SALARY DECIMAL(10, 2),
    DEPTNO CHAR(3)
)
WITH REPLACE
ON COMMIT PRESERVE ROWS
NOT LOGGED;

WITH REPLACE replaces any existing temporary table with the same name in the current application context and deletes its previous rows. ON COMMIT PRESERVE ROWS keeps the rows after a commit. Without this clause the default ON COMMIT DELETE ROWS applies – rows may disappear on commit unless a dependent WITH HOLD cursor remains open. NOT LOGGED reduces logging overhead, but changes to this table are not rolled back like normal table changes.

DGTT on IBM i: SESSION and QTEMP

A common misconception: because the table physically lands in QTEMP, developers sometimes declare it that way. That is not the correct approach for DECLARE GLOBAL TEMPORARY TABLE on IBM i. The qualifier must be SESSION. If you omit the qualifier, Db2 for i implicitly uses SESSION.

Optional: FOR SYSTEM NAME

When you work with long SQL names you can optionally assign an IBM i system name. This name is limited to ten characters and can be useful in very low-level scenarios. For normal SQL usage the option is rarely needed.

DECLARE GLOBAL TEMPORARY TABLE SESSION.TEMP_EMPLOYEE_REPORT (
    EMPNO CHAR(6),
    DEPTNO CHAR(3),
    TOTAL_SALARY DECIMAL(15,2)
)
FOR SYSTEM NAME TEMPEMP01
WITH REPLACE
ON COMMIT PRESERVE ROWS
NOT LOGGED;

SQL Examples to Replicate

1. Derive Structure from an Existing Table

You can derive the structure of a DGTT from a query or adopt it directly from an existing table:

-- Variant A: Using SELECT
DECLARE GLOBAL TEMPORARY TABLE SESSION.TEMP_EMP AS (
  SELECT EMPNO, SALARY, DEPTNO
  FROM MYLIB.EMPLOYEES
)
WITH NO DATA
ON COMMIT PRESERVE ROWS
NOT LOGGED;

-- Variant B: Using LIKE (very clean)
DECLARE GLOBAL TEMPORARY TABLE SESSION.TEMP_EMP
LIKE MYLIB.EMPLOYEES
WITH REPLACE
ON COMMIT PRESERVE ROWS
NOT LOGGED;

2. CTE for a One-Time Intermediate Result

If you need an intermediate result only inside a single SQL statement, a CTE is often more elegant:

WITH TempSales AS (
  SELECT ORDERID, SUM(AMOUNT) AS TOTAL
  FROM MYLIB.SALES
  GROUP BY ORDERID
)
SELECT *
FROM TempSales
WHERE TOTAL > 1000;

3. Combining CTE and Temporary Table

For multi-stage processing a DGTT is often useful: populate once, evaluate multiple times, and optionally create indexes.

DECLARE GLOBAL TEMPORARY TABLE SESSION.TEMP_AVGSAL (
  DEPTNO CHAR(3),
  AVG_SAL DECIMAL(11,2)
)
WITH REPLACE
ON COMMIT PRESERVE ROWS
NOT LOGGED;

INSERT INTO SESSION.TEMP_AVGSAL
WITH Aggregated AS (
  SELECT DEPTNO, DECIMAL(AVG(SALARY), 11, 2) AS AVG_SAL
  FROM MYLIB.EMPLOYEES
  GROUP BY DEPTNO
)
SELECT DEPTNO, AVG_SAL
FROM Aggregated;

TRUNCATE TABLE vs. WITH REPLACE

WITH REPLACE is convenient when you want to robustly redeclare a temporary table. For repeated processing steps that use the same structure, it is usually cleaner to create the table once and then empty it with TRUNCATE TABLE.

DECLARE GLOBAL TEMPORARY TABLE SESSION.MONTHLY_SALES (
    MONTH_ID INT,
    TOTAL_SALES DECIMAL(15,2)
)
WITH REPLACE
ON COMMIT PRESERVE ROWS
NOT LOGGED;

-- Load January
INSERT INTO SESSION.MONTHLY_SALES
SELECT 1, SUM(AMOUNT)
FROM MYLIB.SALES
WHERE ORDER_DATE BETWEEN DATE('2026-01-01') AND DATE('2026-01-31');

SELECT * FROM SESSION.MONTHLY_SALES;

-- Empty the table for the next step
TRUNCATE TABLE SESSION.MONTHLY_SALES REUSE STORAGE;

-- Load February
INSERT INTO SESSION.MONTHLY_SALES
SELECT 2, SUM(AMOUNT)
FROM MYLIB.SALES
WHERE ORDER_DATE BETWEEN DATE('2026-02-01') AND DATE('2026-02-28');

Indexes on Temporary Tables

Temporary tables are not automatically well optimized. When you filter, join, or sort larger intermediate results multiple times, an index on the DGTT can help.

CREATE INDEX SESSION.IDX_TEMP_EMP_DEPT
ON SESSION.TEMP_EMP (DEPTNO, EMPNO);

When Should You Use Temporary Tables?

Practical Example: Monthly Sales Report

Scenario: A company on IBM i produces a monthly sales report by department. The raw data lives in MYLIB.SALES. Instead of running the same aggregation repeatedly, the aggregated result is stored in a temporary table.

DECLARE GLOBAL TEMPORARY TABLE SESSION.TEMP_SALES_REPORT (
    DEPTNO CHAR(3),
    TOTAL_SALES DECIMAL(15,2)
)
WITH REPLACE
ON COMMIT PRESERVE ROWS
NOT LOGGED;

INSERT INTO SESSION.TEMP_SALES_REPORT
SELECT DEPTNO, SUM(AMOUNT) AS TOTAL_SALES
FROM MYLIB.SALES
WHERE ORDER_DATE BETWEEN DATE('2026-05-01') AND DATE('2026-05-31')
GROUP BY DEPTNO;

CREATE INDEX SESSION.IDX_TEMP_SALES_DEPT
ON SESSION.TEMP_SALES_REPORT (DEPTNO);

SELECT *
FROM SESSION.TEMP_SALES_REPORT
ORDER BY TOTAL_SALES DESC;

Advantages in This Scenario

• Readability: Aggregation is cleanly separated from the final output.
• Reusability: The same intermediate result can feed multiple evaluations.
• Isolation: Every job works with its own temporary table.
• Optional tuning: You can add an index on the temporary table when needed.

Disadvantages and Limitations

• Extra step: The table must be populated. This is not worth doing for every small SELECT.
• Planning required: Without an index or with the wrong index a DGTT can stay slow.
• Volatility: Everything disappears when the application context ends. For audit, debugging, or restart you need other mechanisms.
• Commit behavior: Without ON COMMIT PRESERVE ROWS a commit can delete your rows.

Comparison: DGTT, CTE, View, Persistent Table

The following table is deliberately qualitative. Fixed millisecond numbers without concrete hardware, data volume, PTF level, job configuration, and SQL plan are quickly misleading.

Db2 Is Not the Same on Every Platform: IBM i, LUW, z/OS

Platform matters with temporary tables. Db2 for i, Db2 LUW, and Db2 for z/OS use partly similar terminology but not always the same syntax. On IBM i, DECLARE GLOBAL TEMPORARY TABLE is the central mechanism. Do not blindly copy syntax and behavior from LUW or z/OS articles to IBM i.

Best Practices for IBM i Developers

DROP TABLE SESSION.TEMP_SALES_REPORT;

Conclusion

Sources