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
| 
