DB2 SQL Global Temporary Tables top Usage

Global temporary tables (GTTs) – if the data being processed will persist within the application’s logical unit of work rather than repeatedly constituting or materializing data. If the same data is being retrieved or materialized multiple times throughout a program’s execution, you can load the data one time into a GTT and reference that table elsewhere in the code.

Global Temporary Table.

Global temporary tables frequently used in Data intensive projects, Data Warehousing projects and Analytics projects.

For example, a program has multiple cursors, and each cursor contains multiple table joins. Also, each cursor contains the same table, Table1, that happens to be the driver table in each query. As each cursor gets processed, the same rows may have to be retrieved from the database for processing on Table1. This causes extra I/O against the same data.

You May Also Like: Key differences of JOINs vs Sub-Query

So the idea is to retrieve only Table1 data and insert it into a GTT, and then reference the GTT in each cursor. This eliminates the duplicate I/O on Table1. GTTs are also great for creating a table of summarized data that is then referenced in other queries within the logical unit of work.

Key points of Global Temporary Tables.

1. Can have Indexes and check constraints
2. Can issue update statements
3. Can issue SELECT on Declared temp tables

Sample SQL query for Global Temporary Tables.

DECLARE GLOBAL TEMPORARY TABLE SESSION.TEMP_EMP1
 (EMPNO CHAR(6) NOT NULL,
 FIRSTNME VARCHAR(12) NOT NULL,
 MIDINIT CHAR(1) NOT NULL,
 LASTNAME VARCHAR(15) NOT NULL,
 WORKDEPT CHAR(3),
 PHONENO CHAR(4)
 )
 ON COMMIT DROP TABLE or
 ON COMMIT DELETE / PRESERVE ROWS
 ;

CREATE INDEX SESSION.EMPX1 ON SESSION.TEMP_EMP1
 (LASTNAME ASC)
 ;

INSERT INTO SESSION.TEMP_EMP1
 SELECT EMPNO,
 FIRSTNME,
 MIDINIT,
 LASTNAME,
 WORKDEPT,
 PHONENO
 FROM EMP
 ;
SELECT *
FROM SESSION.TEMP_EMP1;

Keep Reading