DB2 SQL GLOBAL TEMPORARY TABLE

DECLARE GLOBAL TEMPORARY TABLE gives you a handy option to create a table in QTEMP lib with some handy options.

1. As DECLARE GLOBAL TEMPORARY TABLE creates a table in QTEMP lib so

• Other sessions cannot share it.
• It does not appear in the system catalog.
• When you end your session, the table rows are deleted, and the table is dropped.

2. Basic syntax of DECLARE GLOBAL TEMPORARY TABLE is almost the same as CREATE TABLE statement.

• For example, same as CREATE TABLE statement, You LIKE or AS clause can be used.

3. They can store data with minimal infrastructure and resource requirements

4. They are not persistent database objects.

5. You can issue SELECT, INSERT, UPDATE, and DELETE statements against this table, the same as any other table.

6. You can drop this table by issuing the DROP TABLE statement

DECLARE GLOBAL TEMPORARY TABLE syntax for IBM I.

DECLARE GLOBAL TEMPORARY TABLE table-name
{column-definition(s) | LIKE base-table-name | AS subquery }
[GTT-options]

For example

DECLARE GLOBAL TEMPORARY Table tempnewproj
 (projname VARCHAR(24) , projsdate DATE, projedate DATE))
      ON COMMIT PRESERVE ROWS
      NOT LOGGED                   
      ON ROLLBACK PRESERVE ROWS     
      WITH REPLACE
DECLARE GLOBAL TEMPORARY TABLE TEMP_EMPLOYEE LIKE EMPLOYEE

GTT-options details