All Forums Database
barani_sachin 141 posts Joined 01/12
28 Feb 2013
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.

dnoeth 4628 posts Joined 11/04
28 Feb 2013

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

Dieter

asadali.khan 11 posts Joined 10/10
28 Feb 2013

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. 
 
 

Asad Ali Khan

barani_sachin 141 posts Joined 01/12
28 Feb 2013

@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...

dnoeth 4628 posts Joined 11/04
01 Mar 2013

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

Dieter

barani_sachin 141 posts Joined 01/12
01 Mar 2013

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?

dnoeth 4628 posts Joined 11/04
01 Mar 2013

This is strange.
Is this all the time or randomly?
Is there error handling in the SP?
Could you show the SP?
Dieter

Dieter

Adeel Chaudhry 773 posts Joined 04/08
02 Mar 2013

Are you using dynamic SQL to create VTs?

-- If you are stuck at something .... consider it an opportunity to think anew.

barani_sachin 141 posts Joined 01/12
03 Mar 2013

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.

barani_sachin 141 posts Joined 01/12
03 Mar 2013

@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?

dnoeth 4628 posts Joined 11/04
04 Mar 2013

Your skeleton looks ok, without seeing the actual source it's hard to tell what's going wrong.

Dieter

Dieter

mjj 23 posts Joined 03/10
04 Mar 2013

Hi,
Give it a try..
Set your database to your working database. then compile the sp. It will show warnings "table temp1,2,3 already exists".
Then drop all VTs manually and execute the sp. It will create VTs again and then run ins sel.

Regards,

barani_sachin 141 posts Joined 01/12
04 Mar 2013

sadly I cant show the full code; Thanks for u r time Dieter :) One more question on this, Lets say i dont have any Error handler in my code,
1) And all the VT's has been created
2) after that there is an error in one statement -- At this point will all the created VT's be dropped?

dnoeth 4628 posts Joined 11/04
04 Mar 2013

No, the tables will not be dropped.
Of course, due to the NO LOG, the affected table will be empty if it was target of a INS/UPD/DEL.

DIeter

Dieter

barani_sachin 141 posts Joined 01/12
05 Mar 2013

Thanks Dieter :)

@mjj - It didnt work.

Adeel Chaudhry 773 posts Joined 04/08
11 Mar 2013

We had an implementation where VTs were being created in dynamic-SQL .... and after some update patch installation same SPs running for months started to fail. On investigation we had to do following to make it work again:
 
DECLARE MyUser VARCHAR(30);
SELECT TRIM(USER) INTO MyUser;
CALL DBC.SysExecSQL('CREATE volatile table ' || MyUser || '.TableName1(Col1 BIGINT ) ON COMMIT PRESERVE ROWS;');
 
Try creating via Dynamic SQL.
HTH!

-- If you are stuck at something .... consider it an opportunity to think anew.

maniamenon 10 posts Joined 05/09
22 Mar 2013

Hi Dieter,

When I am trying to create a Volatile Table in the Procedure, it gives an error as below:
 
SPL1027:E(L71), Missing/Invalid SQL statement'E(3807):Object 'vt_TableTar' does not exist.'.
SPL1027:E(L170), Missing/Invalid SQL statement'E(3807):Object 'vt_TableSrc' does not exist.'.
 
In the SP, first it create the Volatile Table, then doing some inserts into these two Volatile tables. The Statements are placed in this order. In this case, it directly takes the Insert Statement without creating the Volatile Table and throws this error.
On the other hand, in each individual run it runs successfully.
Could you please help me the possible reasons for this?
Mani
 

 

dnoeth 4628 posts Joined 11/04
22 Mar 2013

Hi Mani,
don't care, this should be no error, but a warning message, i.e. the SP still gets created.
When the VT doesn't exist in the current session, there's this warning message for the INSERT, but when it exists you probably get a "table already exists" warning for the CREATE :-)
Dieter

Dieter

rhlchkrbrty 1 post Joined 03/13
24 Mar 2013

Hi,
Volatile tables won't get dropeed automatically, till you are logged in the session.

maniamenon 10 posts Joined 05/09
24 Mar 2013

Hi Dieter,
Thank you very much for the information.
I have created the Volitile Tables first and then created the Stored Procedure.
Now it gives warnings saying
SPL5000:W(L46), E(3803):Table 'vt_TableSrc' already exist.
This indicates a "Warning". Now I can call the Stored Procedure and it is working fine.
Mani

You must sign in to leave a comment.