VOLATILE TABLE - Where is it located?

Database
Junior Supporter

VOLATILE TABLE - Where is it located?

When you create a VOLATILE TABLE what location is it created in? I know it is a temp table that is gone when the session ends.

The following SQL will give me the SQL that was used to create the table:
Show table myVolatileTableName;

I would like to be able to look at the structure of VOLATILE TABLE in SQL Assistant or Teradata Administrator.
Tags (2)
12 REPLIES
Junior Supporter

Re: VOLATILE TABLE - Where is it located?

In SQL Data Definition Language – Syntax and Examples page 778 it shows:
HELP VOLATILE TABLE sales_temp;

The above didn’t work for me, but this SQL does:
HELP TABLE myTableName;

The result gives me the structure info, but I still would like to know where a VOLATILE TABLE is located when it is created so I can see it directly in SQL Assistant or Teradata Administrator.
Junior Contributor

Re: VOLATILE TABLE - Where is it located?

Volatile tables are stored in SPOOL and there's no info stored in any system table.

Thus the only way to retrieve the metadata of a VT is a HELP/SHOW TABLE.

If you forgot the name you can get all the existing VTs in your current session using
HELP VOLATILE TABLE;

Dieter
Enthusiast

Re: VOLATILE TABLE - Where is it located?

can i see contents of volatile table

create volatile table xyz.test;.....set of statements.

if say sel * from xyz.test...i am not getting any result.
Thanks
Sree
Senior Supporter

Re: VOLATILE TABLE - Where is it located?

did you specify ON COMMIT PRESERVE ROWS?

Enthusiast

Re: VOLATILE TABLE - Where is it located?

oh i missed that.thanks now i am able to....
Teradata Employee

Re: VOLATILE TABLE - Where is it located?

thanks!

Re: VOLATILE TABLE - Where is it located?

If you forgot the name you can get all the existing VTs in your current session using

HELP VOLATILE TABLE;

Thanks, dieter! Just what I was looking for. 

Initially I tried 

help database <user ID>;

which didn't go very well. 

Enthusiast

Re: VOLATILE TABLE - Where is it located?

Hi,

 

I am having a similar problem of not able to locate the volatile table created in the 'Database Explorer' of the 'Teradata SQL Assistant'. I want to select some column of the table just as the same way I can with the permanent database tables. I understand the volatile table is created in the user's SPOOL SPACE but where is this SPOOL SPACE loacted?

 

Can I see the volatile table created in the 'Database Explorer' of the 'Teradata SQL Assistant'?

 

Also, I know a volatile table can only be retained for the duration of the session (I assume it is the SQL session in 'Teradata SQL Assistant'), but if I created a volatile table thru SAS (using Teradata SQL connect), can I still see the volatile table data contents in 'Teradata SQL Assistant' if I don't end my SAS session? Just want to understand what do they referring a "session" as to? 

 

Can someone get me a quick answer?

 

Thanks

Stephen

Teradata Employee

Re: VOLATILE TABLE - Where is it located?

"where is this SPOOL SPACE located?"  That's kind of like asking where your Azure or AWS database is located - it's kind of vague.  All that can be said is that it is space dedicated to your user ID for this session.  The Database Hierarchy shown in SQLA does not include your spool space.  The only way to find it and display the structure is with the Help or Show SQL commands Dieter explained above.

Your SAS session is totally separate from your SQLA session.  A session is the sequence of commands from an application's logging on to its logging off.  (Furthermore, SQLA uses ODBC but SAS can use CLI.)

The intent of a volatile table is to simplify SQL processing by creating a reusable intermediate result set.  The idea is that the application that creates it can reuse it.  (Often this is done in a stored procedure, perhaps reused in a series of SPs.)  If you want something more shareable and less volatile, consider a Global Temp Table instead.