Creation of Volatile table

Database
Enthusiast

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.

3 REPLIES
Enthusiast

Re: Creation of Volatile table

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

Enthusiast

Re: Creation of Volatile table

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

Enthusiast

Re: Creation of Volatile table

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.