Systems Engineering and RDBMS

Temporary Tables – DB2 LUW

Posted by decipherinfosys on May 5, 2007

In the last 2 blog posts, we had covered temporary tables implementation in Oracle and MS SQL Server. In this one, we will cover the basics of the same feature in DB2 LUW.  The way to define temporary tables in DB2 is to use ‘DECLARE GLOBAL TEMPORARY TABLE’ syntax. Temporary tables are widely used in stored procedure and functions. We can either declare or create global temporary tables. Syntax to create global temporary table is as under.

DECLARE GLOBAL TEMPORARY TABLE SESSION.TEMP_TABLE
(
COL1   INTEGER,
COL2   VARCHAR(30),
COL3   TIMESTAMP
) IN USERTEMP1 ON COMMIT DELETE ROWS WITH REPLACE NOT LOGGED;

In above syntax, when declared,
•    Temporary table should always be prefixed with SESSION as schema name. If it is not specified, then it is assumed. But if you specify anything other than SESSION, it will return an error.
•    USERTEMP1 is the name of user temporary tablespace.
•    WITH REPLACE will make sure that if table already exists in the current session, then it will be replaced and won’t return error SQL0601N (Identical object exists)
•    NOT LOGGED indicates that any DML operation are not be logged.
•    ON COMMIT indicates the action to be taken when COMMIT happens. In this case, we are deleting rows when commit occurs. We can specify to ‘PRESERVE ROWS’ also instead of ‘DELETE ROWS’.

Since it is easier to explain it using stored procedure rather than SQL/PL block, we will show you, usage of temporary tables using stored procedure.

Open DB2 command editor or any other editor and execute following code to create stored procedure. Make sure that statement terminator is defined as ‘@’.

CREATE PROCEDURE TEMP_PROC1
()
DYNAMIC RESULT SETS 1
LANGUAGE SQL
BEGIN
DECLARE GLOBAL TEMPORARY TABLE SESSION.TEMP_TABLE
(COL1   INTEGER,
COL2   VARCHAR(30),
COL3   TIMESTAMP
) ON COMMIT DELETE ROWS NOT LOGGED WITH REPLACE;

INSERT INTO SESSION.TEMP_TABLE VALUES(1,’Decipher’,current timestamp);
INSERT INTO SESSION.TEMP_TABLE VALUES(1,’Information’,current timestamp);
INSERT INTO SESSION.TEMP_TABLE VALUES(1,’Systems’,current timestamp);

COMMIT;

–Get the data from the table to display it back to client application
BEGIN
DECLARE c2 CURSOR WITH RETURN TO CLIENT FOR
SELECT col1, col2, col3
FROM SESSION.TEMP_TABLE;

OPEN c2;
END;
END
@

In above procedure, we are inserting data into temporary table, committing it and returning data set to calling program.  We have specified to delete the rows when commit happens so before returning record set, we are also committing the changes. Issue following command to execute the stored procedure from command editor.

call temp_proc1

Result is shown as under.

COL1        COL2                           COL3
———– —————————— ————————–

0 record(s) selected.

Since we have specified to delete rows on commit and issued commit before returning dataset, we don’t see any records returned back to calling program. Now change above procedure and change ‘ON COMMITE DELETE ROWS’ to ‘ON COMMIT PRESERVE ROWS’. Drop and re-create the stored procedure and execute it. This time we will see the result set as we are preserving rows even after commit. Here is the result of execution.

COL1        COL2                           COL3
———– —————————— ————————–
1 Decipher                       2007-03-29-20.21.32.777000
1 Information                    2007-03-29-20.21.32.777000
1 Systems                        2007-03-29-20.21.32.777000

3 record(s) selected.

Now open another session and create following stored procedure. It is different procedure but uses the same temporary table name.

CREATE PROCEDURE TEMP_PROC2
()
DYNAMIC RESULT SETS 1
LANGUAGE SQL
BEGIN
DECLARE GLOBAL TEMPORARY TABLE SESSION.TEMP_TABLE
(COL1   INTEGER,
COL2   VARCHAR(30),
COL3   TIMESTAMP
) ON COMMIT PRESERVE ROWS WITH REPLACE NOT LOGGED;

INSERT INTO SESSION.TEMP_TABLE VALUES(1,’Oracle’,current timestamp);
INSERT INTO SESSION.TEMP_TABLE VALUES(1,’SQLServer’,current timestamp);
INSERT INTO SESSION.TEMP_TABLE VALUES(1,’DB2′,current timestamp);

COMMIT;
–Get the data from the table to display it back to client application
BEGIN
DECLARE c2 CURSOR WITH RETURN TO CLIENT FOR
SELECT col1, col2, col3
FROM SESSION.TEMP_TABLE;
OPEN c2;
END;
END
@

Execute stored procedure using following command. It is followed by result.

call test_proc2

COL1        COL2                           COL3
———– —————————— ————————–
1 Oracle                         2007-03-29-21.12.52.855000
1 SQLServer                      2007-03-29-21.12.52.855000
1 DB2                            2007-03-29-21.12.52.855000

3 record(s) selected.

Here we have executed two different stored procedures from two different sessions having same temporary table name. But it still returns the data pertaining to specific session only. Each session has its own copy of temporary table when it is defined as a same name in different session.

One interesting thing, we noticed that in earlier version of DB2 (version 8.2 and lower), during declaring temporary tables in user temporary tablespace which does not exist, it will not give you run time error when you execute the stored procedure but it will not return any data. Apparently it is fixed in version 9. We have performed above tests in version 9 and when we assigned user temporary tablespace which does not exist in the database, it gives error SQL0204N (<tablespace> is an undefined name).

For complete syntax and authorization to declare global temporary tables, please refer to DB2 SQL Reference Manual.

In general following are the key points when temporary tables are used in DB2
•    If schema is specified then it should always be SESSION. Any other schema prefix will give an error.
•    WITH REPLACE clause is required to avoid error when global temporary table, we are creating alerady exists.
•    Temporary tables should be created in user temporry tablespace.
•    Specific action can be performed on both ON COMMIT and ON ROLLBACK clause.
•    When ON COMMIT DELETE ROWS is used, make sure that COMMIT is the last statement. If commit is used somewhere in between and temporary table is referenced after commit, all data from the temporary table will be gone.

Sorry, the comment form is closed at this time.

 
%d bloggers like this: