The schema for declared global temporary table is always SESSION. We always need to qualify a temporary table with this schema or else DB2 will look for a table in the current schema.

To select the data from a temporary table temp_tbl, we can use following command:

SELECT * FROM SESSION.temp_tbl 

The preceding statement selects all records from the temp_tbl temporary table.

For all declared global temporary tables, the schema name is always SESSION. We can also create a regular table with the schema name as SESSION. In such cases, if we have a regular table (as in SESSION schema) and a temporary table with the same name, then DB2 will resolve the reference to the temporary table rather than the regular table. Hence, it is never recommended to create regular tables in the SESSION schema to avoid confusion.

The declared temporary tables are dropped when the application terminates the database connection. We can also drop them explicitly, by using the DROP command. For example:

DROP TABLE SESSION.temp_tbl

To drop a temporary table, we must specify SESSION as a schema name. Once the temporary table is dropped, it can no longer be referenced even if the transaction is not committed.

If a temporary table is defined within a transaction, then with the ROLLBACK command, the temporary table is also dropped. Otherwise, it is available until the application disconnects from the database. If we want to use a temporary table and its data across the transactions, then there is only one thing to think about, which is whether or not we want to preserve the data in the temporary table once it exits a transaction. This behavior can be specified by using the ON COMMIT clause:

DECLARE GLOBAL TEMPORARY TABLE sal_rise_temp (empno INT, sal_rise_date DATE, updated_sal BIGINT, manager_empno INTEGER) ON COMMIT DELETE ROWS NOT LOGGED IN user_temp_tbsp 

If the ON COMMIT DELETE ROWS clause is specified, then the rows will be deleted on COMMIT. This is the default. If the ON COMMIT PRESERVE ROWS clause is specified, then the rows will not be deleted on COMMIT.

We can use the SYSIBMADM.ADMINTEMPTABLES administrative view and the SYSPROC.ADMIN_GET_TEMP_TABLES table function to get information about the temporary table present in the database. The results can be filtered by specifying conditions, such as TEMPTABTYPE='D' or TEMPTABTYPE= 'C'. We can also filter the results through the application handle or application name:

SELECT TABSCHEMA, TABNAME, ONCOMMIT, ONROLLBACK, INSTANTIATION_TIME FROM SYSIBMADM.ADMINTEMPTABLES WHERE TEMPTABTYPE = 'D' AND INSTANTIATOR = SYSTEM_USER SELECT TABSCHEMA, TABNAME, ONCOMMIT, ONROLLBACK, INSTANTIATION_TIME FROM TABLE (SYSPROC.ADMIN_GET_TEMP_TABLES(APPLCATION_HANDLE, TABSCHEMA, TABNAME)) AS T 

The sample output is as follows: