All Forums Tools
kriti1000 4 posts Joined 10/15
24 Nov 2015
Fast load Issue

Hi experts, this is the error I got while executing the fastload script
0002 .logon 127.0.0.1/dbc,

**** 16:43:30 Teradata Database Release: 14.10.01.02

**** 16:43:30 Teradata Database Version: 14.10.01.01

**** 16:43:30 Number of AMPs available: 2

**** 16:43:30 Current CLI or RDBMS allows maximum row size: 64K

**** 16:43:30 Character set for this job: ASCII

 

0003 set record vartext ",";

 

**** 16:43:30 Now set to read 'Variable-Length Text' records

**** 16:43:30 Delimiter character(s) is set to ','

**** 16:43:30 Command completed successfully

 

0004 begin loading retail.employee;

 

**** 16:43:30 Number of FastLoad sessions requested = 4

**** 16:43:30 Number of FastLoad sessions connected = 2

**** 16:43:30 FDL4808 LOGON successful

**** 16:43:30 RDBMS error 3706: Syntax error: expected something

              between the word 'employee' and the 'WITH' keyword.

     ====================================================== =============

     =                                                                 =

     =          Logoff/Disconnect                                      =

     =                                                                 =

     ====================================================== =============

 

**** 16:43:30 Logging off all sessions

**** 16:43:30 Total processor time used = '0.27 Seconds'

     .        Start : Sat Nov 21 16:43:30 2015

     .        End   : Sat Nov 21 16:43:30 2015

     .        Highest return code encountered = '12'.

**** 16:43:30 FDL4818 FastLoad Terminated

TDExpress14.10.01_Sles10:~ #

 

The script that i used is as follows

 

.sessions 4;

.logon 127.0.0.1/dbc,dbc;

set record vartext ",";

begin loading retail.employee;

 

define 

Empno (varchar(15))

Empname (varchar(18))

Address (varchar(40))

Phone (varchar(15))

Deptno (varchar(2))

Salary (varchar(12))

YrsExp (varchar(1))

DOB (varchar(10))

Medstat (varchar(1))

Edllev (varchar(1))

Note (varchar(79))

 

File = Incoming.txt;

Insert into retail.employee

values(

:Empno,

:Empname,

:Address,

:Phone,

:Deptno,

:Salary,

:YrsExp,

:DOB,

:Medstat,

:Edllev,

:Note

);

end loading;

.logoff;

 

 

can somebody please help me to figure out the mistake.. please.. please.. please..

 

 

CarlosAL 512 posts Joined 04/08
25 Nov 2015

Hi.
Commas missing after field definitions in the DEFINE section.
HTH
Cheers.
Carlos.

Fred 1096 posts Joined 08/04
25 Nov 2015

You will need the commas also, but didn't even get that far.
While the reference to "'WITH' keyword" is confusing, the 3706 error is because your BEGIN LOADING command does not include the required ERRORFILES table1, table2 clause

CarlosAL 512 posts Joined 04/08
26 Nov 2015

Fred:
Yup. Of course you're right. I overlooked the ERRORFILES part.
Cheers.
Carlos.

kriti1000 4 posts Joined 10/15
27 Nov 2015

Thank you for your help.. It worked... :) Can you please help me with some other issues. If my file includes column header names how do i skip them so that they will not be loaded into my database. Also, is there any way i can concatenate two or more fields from my file and load it into the database with fastload. Example;
File structure:
Empno, Efirstname, Emiddlename, Elastname, Deptno
Database structure:
Empno, Ename, Deptno
(Would I be able to concatenate first,middle, and last name field in the fastload script and load it to the ename field in my database)
Thank you for your help in advance.. :)

Fred 1096 posts Joined 08/04
27 Nov 2015

You can use RECORD 2; command to skip the first record.
FastLoad does not allow you to concatenate the fields or extract part of a field. You can ignore fields from the input record, but can only insert a field to a column (or substitute NULL, using NULLIF clause).

kriti1000 4 posts Joined 10/15
28 Nov 2015

Thank you Fred.. :)

feinholz 1234 posts Joined 05/08
30 Nov 2015

I could be wrong but I do not think the RECORD command will work with header rows.
FastLoad expects every row to have the same format, so the header rows would have to be in the same format as the data rows (which does not always happen).
 
TPT, on the other hand, will just blindly read until the EOR when skipping rows and not care about the content of the row. TPT can also perform concatenation of columns.

--SteveF

anishgoyal007 7 posts Joined 02/14
30 Nov 2015

if you are using UNIX/LINUX to run script, then use can easily remove the 1st record or last records using sed

Anish Goyal

You must sign in to leave a comment.