Let’s see how to preserve, empty or drop temporary tables when a transaction succeeds (
COMMIT) or fails (
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.
ROLLBACK, all the row changes (
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 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