Db2 temporary tables: ON COMMIT and ON ROLLBACK

Let’s see how to preserve, empty or drop temporary tables when a transaction succeeds (COMMIT) or fails (ROLLBACK).

Created temporary tables

In Db2, temporary tables created with CREATE GLOBAL TEMPORARY TABLE are created in the work file and their rows disappear at the end of each transaction. The disk space is reclaimed immediately.

CREATE GLOBAL TEMPORARY TABLE SESSION.NUMBERS (
    N INTEGER NOT NULL
)

Defined temporary tables

Defined temporary tables are created with DECLARE GLOBAL TEMPORARY TABLE.

For these tables, we can decide what happens ON COMMIT. The options are:

• ON COMMIT DELETE ROWS (default);
• ON COMMIT PRESERVE ROWS;
• ON COMMIT DROP TABLE.

Defined logged temporary tables

Like regular tables, defined temporary tables are LOGGED by default.

On ROLLBACK, all the row changes (INSERT, UPDATE, DELETE) made these tables are undone.

DECLARE GLOBAL TEMPORARY TABLE SESSION.NUMBERS (
    N INTEGER NOT NULL
)
    ON COMMIT DROP TABLE

Defined not logged temporary tables

For tables created with the NOT LOGGED clause, we can specify what happens ON ROLLBACK:

• ON ROLLBACK DELETE ROWS (default);
• ON ROLLBACK PRESERVE ROWS.

DECLARE GLOBAL TEMPORARY TABLE SESSION.NUMBERS (
    N INTEGER NOT NULL
)
    ON COMMIT DROP TASBLE
    ON ROLLBACK PRESERVE ROWS

Reference

Db2 documentation:

• CREATE GLOBAL TEMPORARY TABLE statement
• DECLARE GLOBAL TEMPORARY TABLE