GTT Global Temporary table : Java Interview

What are Global temporary tables and its uses :

 I worked on Global temporary table functionality recently and wanted to share a few experience.

Temporary tables are tables which live for a session only.The data for them is available for a session. There are various other options available with them which we will discuss below by taking an example of IBM DB2.

There are two ways of creating a Temporary table.

1) Declaring Global temporary table.
2) Creating Global Temporary table :


1) Declare GTT : Declare GTT is like a table created for a session only and its description is dropped
after session ends with DB. It has various advantages over Create GTT like

• It does not need to worry about dropping it after our work is done.
• The description can unique per session.
• Constraints and Indexes can be created on Declared GTT.
• Its like a table created per session and lives only for that session. The same is applicable for its data also.

2) Create GTT : Create GTT is a table which is created as a table , you will find its definition and description after the session ends and we connect to the DB again.

• It needs to be dropped of you do not need it forward.
• The description resides in DB after the session ends , so it is not unique per session. The data in it is unique per session.
• Constraints and indexes can NOT be created.
• Its like a table created in DB only and the data is created per session. So data from one session is unique instead on description as compared to DGTT. The data entered in one session will not be seen by other.

Example :

 DECLARE GLOBAL TEMPORARY TABLE MY_TEMP
    (ID BIGINT NOT NULL,
    AGE INTEGER) 
    ON COMMIT PRESERVE ROWS
    NOT LOGGED WITH REPLACE ON ROLLBACK DELETE ROWS ; 

Let me explain the main features which are used mostly : 

 ON COMMIT PRESERVE ROWS : On Committing the data the rows are preserve in session.

 I found this pretty useful in  case  of you are doing batch save and sending transaction in batches while saving large amount of data.

 ON COMMIT PRESERVE ROWS : save a result set in it and use it then delete.

WITH REPLACE : If a table with that name exists already, it will be deleted and created again. You can say it as truncate. This is not able if you are using CREATE  GLOBAL TEMPORARY TABLE

ON ROLLBACK DELETE ROWS : Once a  Rollback to a save point, the rows will be deleted.

GTT are  also used in Store procedure if you dont want to make a query to fetch something you have already fetched once, store it in a GTT .

Happy Coding.