By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,017 Members | 979 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,017 IT Pros & Developers. It's quick & easy.

HELP! declare global temporary tables?!?

P: 1
Hi there,

hope anybody can help me:
I want to create a temporary table, want to fill that table with data and finally I want to use that table as my base-table for running some selects on it (DB2). So I used the following SQL:

DECLARE GLOBAL TEMPORARY TABLE SESSION.SG_ADVICE
(ID DECIMAL(11,0),
ZAEHLER INTEGER)
ON COMMIT PRESERVE ROWS
;
INSERT INTO SESSION.SG_ADVICE
SELECT ID,
ZAEHLER
FROM TABLE...
WHERE ...
;

The above script works fine. After that I just wanted to check the data with a SELECT * FROM SESSION.SG_ADVICE;

This works also fine, but after I run the SELECT *, I tried once more running the the same SELECT * but this time my table could not be found?!? Is a declared global temporary table only useful for one select? what do I have to do for using when I want to use a temporary table until my connection gets lost?

The database TERADATA has a similar feature, and I also can define an index on that temporary (volatile) table. The table can be used until I cut the connection (as many selects and updates as I want)...

CREATE VOLATILE TABLE username.SG_ADVICE
(ID DECIMAL(11,0),
ZAEHLER INTEGER)
PRIMARY INDEX (ID)
ON COMMIT PRESERVE ROWS
;
INSERT ...
;

What do I have to do in DB2, when I just want to use the same feature as it is in TERADATA. Please help me. What is my fault?

THANKS for all hints and tipps
regards
sgueder
Jul 13 '07 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.