DB2 temporary tables: DGTT and CGTT

Two kinds of temporary table

“Global” in the statement names does not mean one shared copy of the rows for the whole subsystem. Each application process gets its own instance. Global means the statement family (and, for CGTT, a catalog description many programs can share).

Declared temporary tables (DGTT)

Use DECLARE GLOBAL TEMPORARY TABLE when you need scratch data for the life of an application process and do not need a permanent, shareable catalog description. Unlike most DECLARE statements, this one is executable: embed it, run it in SPUFI, or PREPARE it dynamically. Db2 creates an empty instance. You populate it with INSERT. You can SELECT, UPDATE, and searched or positioned DELETE. When the process ends, Db2 deletes the rows and drops the description.

Db2 performs limited logging and locking compared with a base table, which is why DGTTs are attractive for staging keys and multi-step batch logic.

SESSION schema

The qualifier for a declared temporary table is SESSION. If you write an unqualified name, Db2 still defines it as SESSION. After DECLARE, every later statement that refers to the table should use SESSION.table-name so you do not accidentally hit a permanent table with the same unqualified name.

1
2
3
4
5
6
7
8
9
10
11
12
DECLARE GLOBAL TEMPORARY TABLE SESSION.TEMP_EMP (
    EMPNO  CHAR(6)     NOT NULL,
    SALARY DECIMAL(9,2),
    COMM   DECIMAL(9,2)
) ON COMMIT PRESERVE ROWS;
 
INSERT INTO SESSION.TEMP_EMP (EMPNO, SALARY, COMM)
SELECT EMPNO, SALARY, COMM
  FROM HR.EMPLOYEE
 WHERE WORKDEPT = 'A00';
 
SELECT EMPNO FROM SESSION.TEMP_EMP;

Two programs can DECLARE SESSION.TEMP_EMP at the same time. Each has a private instance. The name is only unique inside the process, not across the subsystem.

How you describe the columns

You can list columns, use LIKE, or use AS (SELECT …) DEFINITION ONLY:

1
2
3
4
5
6
DECLARE GLOBAL TEMPORARY TABLE SESSION.TEMPPROD
  AS (SELECT * FROM BASEPROD)
  DEFINITION ONLY
  INCLUDING IDENTITY COLUMN ATTRIBUTES
  INCLUDING COLUMN DEFAULTS
  ON COMMIT PRESERVE ROWS;

DEFINITION ONLY copies the shape, not the rows. INCLUDING IDENTITY COLUMN ATTRIBUTES copies identity definitions so generated keys still work on the DGTT. INCLUDING COLUMN DEFAULTS copies defaults. LIKE a base table is the other common shortcut. You still INSERT to fill the instance.

ON COMMIT DELETE ROWS, PRESERVE ROWS, DROP TABLE

Choose DELETE ROWS when the scratch set is for one unit of work. Choose PRESERVE ROWS when a batch COMMITs between steps but must keep the working set. Remember the thread-reuse cost: CICS/IMS thread reuse is not available to a thread whose most recent commit still has an active PRESERVE DGTT. Choose DROP TABLE when the DECLARE is cheap and you want the object gone as soon as the unit of work ends.

LOGGED and NOT LOGGED

LOGGED is the default and the safe choice. NOT LOGGED (available from Db2 11) skips logging of data changes; CREATE and DROP of the table are still logged. Indexes inherit the table’s logging attribute.

A well-known trap: if a DGTT is NOT LOGGED and an INSERT fails with SQLCODE -803 on a unique index, Db2 may empty the table. On a LOGGED DGTT the previous rows remain. Prefer LOGGED unless you have measured log volume and understand rollback semantics.

1
2
3
4
DECLARE GLOBAL TEMPORARY TABLE SESSION.STAGING (
    KEYCOL INTEGER NOT NULL
) ON COMMIT DELETE ROWS
  NOT LOGGED ON ROLLBACK DELETE ROWS;

DGTT indexes and constraints

After DECLARE, you may CREATE INDEX on SESSION.table. Unique indexes are how you enforce “no duplicate keys” in the scratch set. Indexes live with the instance and disappear with it.

1
2
CREATE UNIQUE INDEX SESSION.STAGING_UX
  ON SESSION.STAGING (KEYCOL);

You can declare NOT NULL on DGTT columns. Primary key and referential constraint support on DGTTs is narrower than on base tables — treat unique indexes plus NOT NULL as the practical integrity toolkit, and confirm the exact constraint list for your Db2 version in the SQL Reference. You cannot expect a DGTT to be the parent of a permanent foreign key.

Created temporary tables (CGTT)

Use CREATE GLOBAL TEMPORARY TABLE when many programs should share one description but each process only needs its own empty bag of rows. Db2 records the definition in the catalog. SYSIBM.SYSTABLES shows TYPE = 'G'. The qualifier is a normal schema, not SESSION.

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)
);
 
-- 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;

Operations on created temporary tables are lightly logged. Classic CGTT restrictions still shape design reviews:

• No indexes
• No UPDATE of individual rows the way you UPDATE a base table
• DELETE is typically all-rows, not a searched single-row delete
• Defaults other than null are constrained
• Utilities do not treat CGTTs like ordinary user tables

CGTT shines when the shape is stable, many programs INSERT-then-SELECT, and you want to avoid DECLARE cost on a hot path. If you need indexes or UPDATE, use a DGTT.

Catalog registration

The CGTT description is permanent until DROP TABLE. GRANT and REVOKE apply to that catalog object the way they apply to other tables (with the usual temporary-table caveats). A DGTT never appears as a lasting SYSTABLES row for your SESSION instance. That is why tools that only look at the catalog can “see” CGTTs and miss DGTTs.

Session lifecycle

For a DGTT:

• DECLARE creates an empty instance in this application process
• INSERT / UPDATE / DELETE / SELECT work on that instance
• COMMIT applies ON COMMIT (delete, preserve, or drop)
• ROLLBACK applies LOGGED undo or the NOT LOGGED ON ROLLBACK option
• Process end drops the instance even if you never wrote DROP

For a CGTT, CREATE is a one-time DBA/DDL event. Each process that first uses the table gets an empty instance. When the process ends, that instance’s rows are gone; the catalog description remains for the next caller.

Application process means the Db2 thread / connection, not “a CICS transaction ID for all users.” Two users in two threads do not see each other’s temporary rows.

Temporary table spaces (work files)

Both families store instance data in the work file database. That is the same space SQL sorts and some other work files use. Before DECLARE GLOBAL TEMPORARY TABLE can succeed, IBM requires a WORKFILE database with at least one table space whose page size is 32 KB. Users of temporary tables need USE authority on that temporary table space.

DBA implications:

• Size work files for sort plus peak DGTT/CGTT volume, not sort alone
• Watch 32 KB work file table spaces; DGTTs need them
• A full work file is a production outage for both sorts and temporary tables
• Do not put temporary tables in a user table space you REORG like a base table

Choosing DGTT versus CGTT

• DGTT — private shape, indexes, UPDATE, ON COMMIT control, optional NOT LOGGED. Best default for new application scratch tables.
• CGTT — shared catalog shape, many callers, simple insert-then-read, lower per-call DECLARE overhead.

Neither replaces a permanent table when the data must survive a new connection, be shared between users, or be recovered with COPY/RECOVER. Temporary tables are session scratch, not system of record.

Explain It Like I'm Five

A temporary table is a whiteboard on your desk. A DGTT is a whiteboard you pull out of the closet when you sit down (DECLARE) and put back when you leave; it has the word SESSION on the frame so nobody confuses it with the classroom’s real blackboard. A CGTT is a printed blank form kept in the office supply cabinet (the catalog). Everyone may take a fresh copy to their own desk, but they do not write on each other’s copies. COMMIT can erase the whiteboard (DELETE ROWS), leave the notes (PRESERVE), or throw the whiteboard away (DROP TABLE). The paper lives in the work-file cupboard, which is also where Db2 keeps scrap paper for sorting — if that cupboard is full, nobody can sort or scribble.

Exercises

1. Write a DGTT SESSION.KEYS with EMPNO CHAR(6) NOT NULL, ON COMMIT PRESERVE ROWS, LOGGED.
2. Add a unique index on SESSION.KEYS(EMPNO) and explain why a -803 on a NOT LOGGED DGTT is more dangerous than on a LOGGED one.
3. Write CREATE GLOBAL TEMPORARY TABLE HR.EMP_KEYS with the same two columns, then list two DML operations that are easier on the DGTT than on this CGTT.
4. When would you choose ON COMMIT DROP TABLE instead of DELETE ROWS?
5. Why does a site need a 32 KB work file table space before DECLARE can succeed?
6. Does SET CURRENT SCHEMA = 'SESSION' replace writing SESSION.TEMP_EMP? Explain.