Using FastLoad for Volatile Table

Tools & Utilities
Enthusiast

Using FastLoad for Volatile Table

I'm Trying to load data from a file into a volatile table. Following my FastLoad Job Script:

sessions 2;
errlimit 25;
logon xxxx/yyyy,zzzz;
CREATE VOLATILE TABLE yyyy.vt_employee (
 EmpNo SMALLINT,
 Name VARCHAR(12))
 UNIQUE PRIMARY INDEX( EmpNo ) ON COMMIT PRESERVE ROWS;
set record unformatted;
define
 delim0(char(1)),
 EmpNo(char(9)), delim1(char(1)),
 Name(char(12)), delim2(char(1)),
 newlinechar(char(1))
file=insert.input;
show;
begin loading yyyy.vt_employee errorfiles error_1, error_2;
insert into yyyy.vt_employee(
 :EmpNo,
 :Name,
);
end loading;
logoff;

When I run the script, I receive the message "RDBMS error 5341: Volatile table 'vt_employee' not allowed in statement"

Does that mean that it is not allowed at all to use FastLoad with volatile tables? Or is there another problem in my code? When the script runs, I receive the output "CREATE VOLATILE TABLE yyyy.vt_employee ... Command completed successfully". But if I try to show the created table in the Teradata SQL assistant, I get the message that this table doesn't exist. Is it the case that, after the script finished, the volatile table gets destroyed?

2 REPLIES
Junior Contributor

Re: Using FastLoad for Volatile Table

A Volatile table only exists within the current session and automatically dropped at logoff.

The VT is created by the FastLoad control session (i wonder why this doesn't fail) and then the FastLoad sessions logon.

Don't try to switch to Global Temporary Tables instead, they get also dropped at logoff.

You have to use permananet tables to load using FastLoad.

DIeter

Teradata Employee

Re: Using FastLoad for Volatile Table

The table creation did not fail because the CREATE TABLE occurred prior to the BEGIN LOADING statement.

The BEGIN LOADING statement is the statement that initiates the FastLoad protocol on Teradata.

Prior to that, the DBS would think it is any regular SQL request.

-- SteveF