IBM i SQL

DB2 SQL GLOBAL TEMPORARY TABLE


Global Temporary Table

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

  1. As DECLARE GLOBAL TEMPORARY TABLE creates table in QTEMP lib so
    • It cannot be shared by other sessions.
    • It does not appear in the system catalog.
    • When you end your session, the rows of the table are deleted and the table is dropped.

  2. Basic syntax of DECLARE GLOBAL TEMPORARY TABLE is almost 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 iSeries.

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
Options Option Extension Details
WITH REPLACE DB2 will automatically drop the existing temporary table and re-create it with the new definition
ON COMMIT DELETE ROWS [DEFAULT] When COMMIT is issued delete all rows of this table.
PRESERVE ROWS When COMMIT is issued don't delete any data from this table.
NOT LOGGED To indicate that data changes in this table are not logged
ON ROLLBACK DELETE ROWS [DEFAULT]When ROLLBACK is issued delete all rows of this table.
PRESERVE ROWS When COMMIT is issued don't delete any data from this table.

IBM i developer.

View Comments
There are currently no comments.