All Forums Database
16 Jul 2013
Creation of Volatile table

Hi All,
I work on V2R5. When i try to create a volatile table in database(cly_db), it gives me below error.  user with which i logged into teradata is "cly_dba".
create multiset volatile table cly_db.vol_table, LOG
(
col1 varchar(10)
,col2 integer
)
on commit preserve rows
"5340: database name, if specified, must be a log in user name for a volatile table".
Then i created the same table under the user "cly_dba" from which i logged into Teradata and now it was created successfully as below,
create multiset volatile table jedi_cly_dba.zam_vol_table
,
LOG
(
col1 varchar(10)
,col2 integer
)
on commit preserve rows
Is that we cant create a volatile table in a database(in my case "cly_db" which is a child database of user "cly_dba")?
Please help me in this.

Mahesh Abbigeri
shiyamala 15 posts Joined 12/06
16 Jul 2013

Volatile tables are always created in a users spool space. Hence user name is the db for the volatile table

TD_Raj 50 posts Joined 05/10
24 Jul 2013

As volatile tables are created in user space and need only spool space, you should mention your username to create volatile tables.

teraforsk 11 posts Joined 05/12
05 Aug 2013

As long as your default database is your username, you can just create a volatile table straight-off without using any db qualifications. Here is how you change your default database to your username and create a volatile table.

MODIFY USER <USERNAME> AS 

DEFAULT DATABASE = <USERNAME>;


CREATE VOLATILE TABLE <TABLENAME>

(COLUMNS....

)

ON COMMIT PRESERVE ROWS;

This genertally helps during your batch process.
Hope that helps.

You must sign in to leave a comment.