467,081 Members | 1,183 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,081 developers. It's quick & easy.

HELP! declare global temporary tables?!?

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
  • viewed: 2326
Share:

Post your reply

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

Similar topics

2 posts views Thread by David Parker | last post: by
reply views Thread by dataguy | last post: by
2 posts views Thread by stefan.albert | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.