Created on Aug. 30, 2021, 8:28 a.m. - by Ravi, Pasupuleti
what is temporary table in db2?what is usage of it?
Temporary tables are used to sort large volumes of data and to query that data. Then, when you have identified the smaller number of rows that you want to store permanently, you can store them in a base table.
You have to declare a temp table in DB2 before you can use it. Either with the same query you are running:
DECLARE GLOBAL TEMPORARY TABLE SESSION.YOUR_TEMP_TABLE_NAME AS ( SELECT COLUMN_1, COLUMN_2, COLUMN_3 FROM TABLE_A ) DEFINITION ONLY
Or "manually" define the columns:
DECLARE GLOBAL TEMPORARY TABLE SESSION.YOUR_TEMP_TABLE_NAME ( COLUMN_1 CHAR(10) ,COLUMN_2 TIMESTAMP ,COLUMN_3 INTEGER )
Then populate it:
INSERT INTO SESSION.YOUR_TEMP_TABLE_NAME SELECT COLUMN_1, COLUMN_2, COLUMN_3 FROM TABLE_A WHERE COLUMN_1 = 1 AND COLUMN_2 = 2
It's not quite as straight-forward as in SQL Server.
And even though it's called a "global" temporary table, it only exists for the current session. Note that all temp tables should be prefixed with the
SESSION schema. If you do not provide a schema name, then
SESSION will be implied.