Will volatile tables be dropped?

Database
Ambassador

Re: Will volatile tables be dropped?

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

Dieter
mjj
Teradata Employee

Re: Will volatile tables be dropped?

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,
Enthusiast

Re: Will volatile tables be dropped?

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

Re: Will volatile tables be dropped?

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
Enthusiast

Re: Will volatile tables be dropped?

Thanks Dieter :)

@mjj - It didnt work.
Teradata Employee

Re: Will volatile tables be dropped?

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!

Enthusiast

Re: Will volatile tables be dropped?

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




Ambassador

Re: Will volatile tables be dropped?

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

Not applicable

Re: Will volatile tables be dropped?

Hi,

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

Enthusiast

Re: Will volatile tables be dropped?

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