Will volatile tables be dropped?

Database
Enthusiast

Will volatile tables be dropped?

Hi All,

I have some n No. of volatile tables in a SP. is there any scenario where a Volatile table will be dropped automatically? like wen i run out of spool space that n - (n-1)th table will be dropped. Because i am creating so many volatile tablesbut wen i try to use it throws error saying that this volatile table does not exist and that too randomly (First time it might be 1st table next time some other table).  Any thoughts.

19 REPLIES
Junior Contributor

Re: Will volatile tables be dropped?

When you run out of spool during the CREATE VOLATILE TABLE this specific table will not be created, but otherwise VTs will not be dropped automatically, only when the session logs off.

If you have lots of VTs and a complex SP, maybe creation/access is not always in the correct order?

Dieter

Teradata Employee

Re: Will volatile tables be dropped?

As you said you have not mentioned the number of volatile tables you are trying to create in that SP.

As far as i know, 64 volatile tables can be created at one time. 

Enthusiast

Re: Will volatile tables be dropped?

@Dieter    When you run out of spool during the CREATE VOLATILE TABLE this specific table will not be created -- It wont even throw error, it will just skip??

If you have lots of VTs and a complex SP, maybe creation/access is not always in the correct order? -- How can we make sure that it runs as we intended?

@Asad    I am well within the limits of 64...

Junior Contributor

Re: Will volatile tables be dropped?

Of course it will throw an error, maybe you catch the error with a CONTINUE handler?

It's up to you to implement the correct order, it's your source code :-)

The maximum number of VTs is way more than 64, it's 1000 plus 2000 materialized Global Temporary Tables per session.

Dieter

Enthusiast

Re: Will volatile tables be dropped?

Thanks Dieter for your timely responses :-)  How can we make sure that it runs as we intended? The reason for this question is - I have 7 VTs, i am first creating all the VT's and then inserting into that VT's from a permanent table; But during some insert statements its throwing an error as some VTs are not present.

Still i am confused why its throwing that error while inserting eventhough that VTs DDL was just above it! Any thoughts?

Junior Contributor

Re: Will volatile tables be dropped?

This is strange.

Is this all the time or randomly?

Is there error handling in the SP?

Could you show the SP?

Dieter

Teradata Employee

Re: Will volatile tables be dropped?

Are you using dynamic SQL to create VTs?

Enthusiast

Re: Will volatile tables be dropped?

Hi Dieter,

Its happenign all the time,
No, there is no error handling in the SP,

PFB the skeleton of the SP, Please give your thoughts on this

REPLACE PROCEDURE temp
(
IN Temp1 VARCHAR(50)
) SQL SECURITY OWNER

BEGIN
declare TEMp_cnt bigint;

Create Multiset Volatile table
TEMP_temp1 ,NO FALLBACK, NO JOURNAL, NO LOG
(
col1 BIGINT NOT NULL
)
PRIMARY INDEX(col1) on Commit Preserve Rows;

Create Multiset Volatile table
TEMP_temp2 ,NO FALLBACK, NO JOURNAL, NO LOG
(
col1 bigint not null,
/*30 columns*/
) PRIMARY INDEX(col1) on commit preserve rows;
Create Multiset Volatile table TEMP_temp3, NO FALLBACK, NO JOURNAL, NO LOG
(
/*8 Columns */
)
PRIMARY INDEX(col1) on commit preserve rows;
Create Multiset Volatile table TEMP_temp4 ,NO FALLBACK, NO JOURNAL, NO LOG
(
/*8 Columns */
)
PRIMARY INDEX(col1) on commit preserve rows;
Create Multiset Volatile table TEMP_cntry, NO FALLBACK, NO JOURNAL, NO LOG
(
/*2 Columns */
) PRIMARY INDEX(id) on commit preserve rows;
Create Multiset Volatile table TEMP_stte ,NO FALLBACK, NO JOURNAL, NO LOG
(
/*2 Columns */
) PRIMARY INDEX(id) on commit preserve rows;
Create Multiset Volatile table TEMP_zip, NO FALLBACK, NO JOURNAL, NO LOG
(
/*3 Columns */
) PRIMARY INDEX(id) on commit preserve rows;

insert into TEMP_cntry (id, country_cd)
select stmt;
insert into TEMP_stte (id, state_cd)
select stmt;
insert into TEMP_zip (id, cd1,cd2)
select stmt;
insert into TEMP_temp3
select stmt;
insert into TEMP_temp4
select stmt;
insert into TEMP_temp2
select stmt
from tb1 w
and some joins;

end;

And the problem is always with the temp3 & Temp2 table.
Enthusiast

Re: Will volatile tables be dropped?

@Adeel - No i am not using any Dynamic SQL's to create VT's. Even if i am using that, what kind of issues that will throw?