Volatile/Global Temporary table!!

Database
N/A

Volatile/Global Temporary table!!

Hi friends,

During the table creation of either volatile/Global temporary table say,

CREATE MULTISET GLOBAL TEMPORARY TABLE RSS.abc ,NO FALLBACK ,
CHECKSUM = DEFAULT,
LOG
(
emp_id INTEGER NOT NULL
,first_name VARCHAR(10)
,last_name VARCHAR(10)
,designation VARCHAR(10)
,salary DECIMAL(10,0)
,dob DATE
)
PRIMARY INDEX (emp_id)
ON COMMIT DELETE ROWS;

What does the last statement "ON COMMIT DELETE ROWS" mean??
The explanation which I found says 'at the end of a transaction, the table rows
should be deleted'. If I delete, how can I perform some calculation which I need frequently(within a single session).

Can anyone explain me in an easy way, to understand this??

Thanks & Regards,
Rakesh

3 REPLIES

Re: Volatile/Global Temporary table!!

Hi,

Do ON COMMIT PRESERVE ROWS; instead of ON COMMIT DELETE ROWS;

i think it would be helpfull for your calculations.

Thanks,
N/A

Re: Volatile/Global Temporary table!!

Hi,
By default Global temporary table will be considered as ON COMMIT DELETE ROWS. You don't need to specify explicitly during table creation.

just create the global temporary table without ON COMMIT DELETE ROWS.

immediately after that give this statement.

ALTER TABLE RSS.abc, ON COMMIT PRESERVE ROWS;

sothat your table will be availbale for the complete session irrespective number of transactions you perform on the table.

Hope this helps.

-Meena
N/A

Re: Volatile/Global Temporary table!!

Hi,
I have used global temp table on commit preserve rows.
During initialization of the screen i'm creating temp tables and all the manipulation done on
screen has been stored on temp table ,
at the end of all the insert and delete operation i'm retrieving it from temp table and displaying
it in the screen,at some point of time the temp table value is lost.So nothing is been displayed in
screen.
I tried to find out the problem according to me the session got expires coz of that the value in
the temp table becomes empty and last value inserted is stored.
can anyone help me out to solve this problem it will be great to get a help.