Volatile table use

General

Volatile table use

What is the use of "on commit delete rows" in creating volatile tables ?
4 REPLIES
Senior Apprentice

Re: Volatile table use

Hi,

It means "when the transaction ends (commit) delete all rows from the table". This is the default.

The alternative is "on commit preserve rows".

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com

Re: Volatile table use

Hi Dave, Thanks. But my question is that which are the scenarios we will be using "delete" option. I cannot see any scenario in which we wont be using preserve rows or is there any ?
Enthusiast

Re: Volatile table use

If you have multiple statements in one transaction and don't need the data from the volatile table once the transaction is commited, you may use that option.

You are probably referring to BTET mode, where transaction start and end are implicit, and thus 1 query=1 transaction. In that case, of course, it makes no sense to use the "delete" option.

Senior Apprentice

Re: Volatile table use

IMHO the typical use of the default is when using ANSI mode.

 

In this mode the first request starts the transaction which is then continued until you COMMIT, Rollback or logoff.

 

So your code might be;

create volatile table VT1...on commit delete rows;

commit;

insert into VT1...

...other processing...

commit;

At the end of the transaction all data in VT1 will be deleted.

 

HTH

Dave

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com