Db2 temporary tables overview

Why temporary tables exist

SQL is powerful, but complex programs often need a named holding area: load a subset of keys, transform them in steps, then join back to real tables. You could CREATE a real table for that, but then you own cleanup, authorization sprawl, and leftover rows after abends. Temporary tables give you SQL-friendly storage whose data lifetime matches the application process (with commit-related options on declared tables), while keeping permanent schemas focused on lasting business objects.

IBM describes two types on z/OS: created temporary tables and declared temporary tables. Both are “global” in naming heritage, which confuses beginners—global refers to the statement family, not “one shared copy of the rows for everyone.” Each application process still gets its own instance of the data.

Created global temporary tables (CGTT)

Use CREATE GLOBAL TEMPORARY TABLE when you want a permanent, shareable description of the table but only need to store data for the life of an application process. Db2 records the description in the catalog (you will see entries resembling other tables, with type information identifying a global temporary table). Programs that know the name can open their own empty instance and INSERT into it.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CREATE GLOBAL TEMPORARY TABLE HR.EMP_KEYS (
    EMPNO CHAR(6) NOT NULL,
    WORKDEPT CHAR(3)
);
 
-- Later, in an application process:
INSERT INTO HR.EMP_KEYS (EMPNO, WORKDEPT)
SELECT EMPNO, WORKDEPT
  FROM HR.EMPLOYEE
 WHERE WORKDEPT = 'A00';
 
SELECT K.EMPNO, E.LASTNAME
  FROM HR.EMP_KEYS K
  JOIN HR.EMPLOYEE E
    ON E.EMPNO = K.EMPNO;

Db2 does not fully log operations on created temporary tables the way it logs permanent base tables, which is one reason they can be efficient. Classic CGTT restrictions matter in design reviews: traditionally you do not create indexes on them, you cannot UPDATE rows the way you do on base tables, DELETE is an all-rows style limitation, defaults other than null are constrained, and utilities do not treat them like normal user tables. Always confirm the exact rules for your Db2 version in IBM documentation before locking a design—the product evolves, but the “lighter, more restricted scratch table” identity remains the teaching core.

When CGTT shines

• Many programs, one shape — catalogized definition reused everywhere
• Hot paths that hate DECLARE cost — description already exists; instances are cheap to open comparatively
• Simple insert-then-select patterns — sequential access is enough

Declared global temporary tables (DGTT)

Use DECLARE GLOBAL TEMPORARY TABLE when you need to store data for the life of an application process but do not need a permanent, shareable catalog description. Unlike most DECLARE statements, this one is executable—you can embed it in a program, run it interactively, or prepare it dynamically. After DECLARE, Db2 creates an empty instance you populate with INSERT. You can SELECT, UPDATE, and DELETE with much more flexibility than classic CGTT rules allow, and you can create indexes on the declared table.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
DECLARE GLOBAL TEMPORARY TABLE SESSION.EMP_KEYS (
    EMPNO CHAR(6) NOT NULL,
    WORKDEPT CHAR(3)
) ON COMMIT PRESERVE ROWS;
 
INSERT INTO SESSION.EMP_KEYS
SELECT EMPNO, WORKDEPT
  FROM HR.EMPLOYEE
 WHERE SALARY > 50000;
 
CREATE INDEX SESSION.IX_EMP_KEYS
  ON SESSION.EMP_KEYS (EMPNO);
 
SELECT * FROM SESSION.EMP_KEYS
 ORDER BY EMPNO;

The qualifier is SESSION. The instance is known only to the application process that declared it, so two concurrent programs can both use SESSION.EMP_KEYS without sharing rows. The definition exists while the process runs; you can DROP TABLE earlier if you want to release it before process end. Commit behavior is controlled with options such as ON COMMIT PRESERVE ROWS or delete-oriented commit options—read those clauses carefully so batch commits do not empty your scratch table by surprise.

Work file prerequisite

Before declared temporary tables work, the subsystem needs a work file database with at least one table space that has a 32 KB page size. That is a DBA setup item. Application teams that invent DGTTs in a sandbox without 32 KB work files learn this the hard way. See the work file database overview for the broader capacity story—DGTT traffic and sorts share that space.

When DGTT shines

• Rich DML — update and delete individual rows as algorithms evolve
• Indexes — accelerate lookups into larger temporary sets
• Process-private shapes — no need to permanently catalog every scratch layout
• AS SELECT style definitions — declare from a query result shape when supported patterns fit your version

CGTT vs DGTT at a glance

Naming is the historical trap: both are called “global temporary,” yet neither shares row instances across processes. If someone says “use a global temp,” ask whether they mean CREATE or DECLARE. The wrong choice shows up as missing UPDATE support, unexpected catalog objects, or DECLARE failures from work file setup gaps.

Logging, locking, and performance awareness

Temporary tables are attractive partly because Db2 can avoid the full logging and locking tax of permanent tables—exact behavior depends on type and options. That efficiency is not free capacity: rows still occupy work file pages. A popular anti-pattern is declaring huge temporary sets inside stored procedures that run with high concurrency, stressing work files and statement cache behavior. Sometimes a CGTT (description created once) reduces per-call DECLARE and compile overhead. Measure before standardizing.

Also remember what temporary tables are not: they are not a substitute for proper base tables when data must survive process end, be recovered across outages like enterprise data, or be shared as the system of record. If auditors need a durable trail, design permanent tables and retention—not SESSION scratch pads.

1
2
3
4
5
6
Decision sketch
---------------
Need durable shared business data?     -> base table
Need shared description, light DML?    -> CGTT
Need flexible DML / indexes / SESSION? -> DGTT
Need only one SQL step?                -> maybe derived table / CTE first

Explain It Like I'm Five

A temporary table is like a personal whiteboard. With a created temporary table, the school hangs a standard whiteboard template on the wall (the catalog description), and every student gets their own blank copy to scribble on. With a declared temporary table, you bring your own portable whiteboard labeled SESSION when class starts, draw on it, and take it away when you leave. Nobody else sees your scribbles, and you should not use the whiteboard as the permanent attendance book.

Exercises

1. Write a CGTT definition for a two-column key staging table and a DGTT equivalent using SESSION.
2. List three classic CGTT restrictions and say whether DGTT usually improves each.
3. What happens to work file demand if 500 concurrent tasks each fill a large DGTT?
4. Why is “global” a misleading word for beginners learning temporary tables?
5. Pick CGTT or DGTT for a stored procedure that must UPDATE staged rows and use an index—defend the choice in three sentences.