14 July 2011

DB2 : Creating a global temporary table using stored procedure

Global Temporary Table :

The DECLARE GLOBAL TEMPORARY TABLE statement defines a temporary table for the current session. The declared temporary table description does not appear in the system catalog. It is not persistent and cannot be shared with other sessions. Each session that defines a declared global temporary table of the same name has its own unique description of the temporary table. When the session terminates, the rows of the table are deleted, and the description of the temporary table is dropped.

Example :
DECLARE GLOBAL TEMPORARY TABLE SESSION.USER_TEMP_TABLE
(
    userid varchar(40),
    password varchar(40)
 ) ON COMMIT DELETE ROWS NOT LOGGED WITH REPLACE;


Description :

ON COMMIT
Specifies the action taken on the global temporary table when a COMMIT operation is performed.

DELETE ROWS
All rows of the table will be deleted if no WITH HOLD cursor is open on the table. This is the default.

NOT LOGGED
Specifies that insert, update, or delete operations against the table are not to be logged, but that the creation or dropping of the table is to be logged.  During a ROLLBACK (or ROLLBACK TO SAVEPOINT) operation:
* If the table had been created within a unit of work (or savepoint), the table is dropped
* If the table had been dropped within a unit of work (or savepoint), the table is recreated, but without any data

WITH REPLACE
    Indicates that, in the case that a declared global temporary table already exists with the specified name, the existing table is replaced with the temporary table defined by this statement (and all rows of the existing table are deleted).


Sample Stored procedure:

Suppose we are selecting rows from user table and storing them in global temporary table.
Then fetching all rows from global temporary table and returning them as cursor.

USERTABLE
---------------------------
USERNAME    VARCHAR(40)
PASSWORD    VARCHAR(40)
FULLNAME    VARCHAR(100)
DOB            DATE
----------------------------

Writing a stored procedure

CREATE OR REPLACE PROCEDURE USER_LIST(
            DOB DATE
        )
DYNAMIC RESULT SETS 1
LANGUAGE SQL
BEGIN

    DECLARE GLOBAL TEMPORARY TABLE SESSION.USER_TEMP
        (
            USERNAME VARCHAR(40)
        ) ON COMMIT DELETE ROWS NOT LOGGED WITH REPLACE;

    IF (DOB IS NULL) THEN
        SET DOB = current date;
    END IF;

    INSERT INTO SESSION.USER_TEMP (USERNAME) (
            SELECT USERNAME FROM USER_LIST WHERE DOB = DOB
        );

BEGIN
     DECLARE cursor1 CURSOR WITH RETURN TO CLIENT FOR
             SELECT * FROM SESSION.USER_TEMP;

    open  cursor1;

END;
END@


save file as user.sql and run using
$ db2 -td@ -f user.sql

running the stored procedure:
$db2 "call user_list('1983/01/11')"

1 comment:

  1. I like the valuable information you supply to your articles.
    I'll bookmark your blog and check again right here frequently.

    I'm reasonably certain I'll learn many new stuff proper right here!
    Good luck for the next!

    Feel free to visit my website ... automated income app

    ReplyDelete