Declaring a global temporary table in DB2 for i SQL

Declaring a global temporary table in DB2 for i SQL

We can create a global temporary table that is used with the current session only.

Create global temporary table

To create a global temporary table use the following SQL statement:

DECLARE GLOBAL TEMPORARY TABLE

This table does not appear in the system catalog and cannot be shared by other sessions.

When we end our session, the rows got deleted from the table and then the table is dropped.

This global temporary table is created in QTEMP and as each session has its own QTEMP library and therefore can be accessible to its own session only.

We can use this table by referring its schema name. So for that purpose we use either SESSION or QTEMP.

We can perform SELECT, INSERT, UPDATE and DELETE operation against this table same as any other table.

Also, we can drop this table by issuing the DROP TABLE statement at any time.

DROP TABLE Global_Temporary_Table_Name

The syntax to create this table similar to the CREATE TABLE statement and we can use LIKE and AS clause as well.

Syntax:

DECLARE GLOBAL TEMPORARY TABLE <table Name> (column/columns definition)

DECLARE GLOBAL TEMPORARY TABLE <table Name> LIKE <table Name/View Name> and optional copy options

DECLARE GLOBAL TEMPORARY TABLE <table Name> AS result-table and optional copy options

Additionally, we can define, below statements after above three statements

Any one out of below three:

CCSID ASCII

CCSID EBCDIC

CCSID UNICODE

Any one out of below three:

ON COMMIT DELETE ROWS

ON COMMIT PRESERVE ROWS

ON COMMIT DROP TABLE

Any one out of below three:

LOGGED

NOT LOGGED ON ROLLBACK DELETE ROWS

NOT LOGGED ON ROLLBACK PRESERVE ROWS

Copy Options:

EXCLUDING IDENTITY column attributes

INCLUDING IDENTITY column attributes

EXCLUDING COLUMN DEFAULTS

INCLUDING COLUMN DEFAULTS

USING TYPE DEFAULTS

these above clauses can be defined in any order, and EXCLUDING IDENTITY and INCLUDING IDENTITY column attributes should be defined with LIKE keyword only.

with AS result-table we use the following syntax

AS (full select) WITH NO DATA

WITH NO DATA specifies that the full select is not executed. We can use SQL INSERT INTO statement with the same full select specified in the AS clause to populate the global temporary table with the data retrieved from the full select.

This DECLARE GLOBAL TEMPORARY TABLE statement can be embedded in an application program or issued interactively. It is an executable statement that can be prepared dynamically.

Example 1: Create a global temporary table with column definition FLD1 and FLD2.

DECLARE GLOBAL TEMPORARY TABLE TEMP         
               (FLD1   CHAR(10) NOT NULL,   
                FLD2   INTEGER)             
       ON COMMIT DELETE ROWS                

we can write both the way, in below we just qualified with SESSION that is optional to use.

DECLARE GLOBAL TEMPORARY TABLE SESSION.TEMP         
               (FLD1   CHAR(10) NOT NULL,   
                FLD2   INTEGER)             
       ON COMMIT DELETE ROWS                

Once we execute above statement from the STRSQL session or the ACS Run SQL Script then a table named TEMP as mentioned in the above statement gets created in the session QTEMP library and is accessible to the current session only as the QTEMP library in itself is accessible to the current session. When commit happens on this table the default ON COMMIT DELETE ROWS is used, its default so we can write or ignore writing this. here i written just to mention and explain this.

Example 2: Create a global temporary table using LIKE

Assume that existing table PF1 exists and that it contains two columns, one of which is an identity column. Declare a temporary table that has the same column names and attributes (including identity attributes) as the PF1 table.

DECLARE GLOBAL TEMPORARY TABLE TEMP         
               LIKE PF1
               INCLUDING IDENTITY
       ON COMMIT PRESERVE ROWS                

Here, we created a global temporary table named temp similar to table PF1 and including its identity column attributes in the global temporary table as well and on commit operation on this table would preserve the rows.

Example 3: Create a global temporary table using AS

Use a sub-select to create a temporary table containing only the FLD1 and FLD2 of table PF1 where FLD2 is 1.

DECLARE GLOBAL TEMPORARY TABLE TEMP         
               AS SELECT FLD1, FLD2 FROM PF1
               WHERE FLD2 = 1
       ON COMMIT PRESERVE ROWS                

Here, we created a global temporary table named temp by sub-selecting FLD1 and FLD2 from table PF where FLD2 is 1 and on commit operation on this table would preserve the rows.