fastload error "not enough fields in vartext data record number".

Tools & Utilities
Enthusiast

fastload error "not enough fields in vartext data record number".

Hi,

I am getting fastload error "not enough fields in vartext data record number".

Below is the script

LOGON userid/password;

drop table abc;

drop table abc_err;

drop table abc_err1;

create table abc

(emp_no int

,dept_no smallint

,last_nm varchar(20)

,first_nm varchar(20)

,sal decimal(15,2)

,s_date date format 'yyyy-mm-dd')

unique primary index(emp_no);

set record vartext ",";

define emp_no (varchar(30))

,dept_no (varchar(30))

,last_nm (varchar(30))

,first_nm (varchar(30))

,sal (varchar(30))

,s_date (varchar(30))

file = emp_table.txt;

show;

begin loading abc

ERRORFILES abc_err, abc_err1

checkpoint 1;

insert into abc

values

(:emp_no

,:dept_no

,:last_nm

,:first_nm

,:sal

,:s_date)

;

end loading;

.LOGOFF;

Records are as below:

1,2,jones,alex,260000,2015/12/12

2,1,simon,patrick,860000,2015/12/12

Any suggestions

Thanks,

Rohit


7 REPLIES
Teradata Employee

Re: fastload error "not enough fields in vartext data record number".

Is there a new line at the end of the second record? There needs to be...

Enthusiast

Re: fastload error "not enough fields in vartext data record number".

yes, i went to the next line after the second row has ended.

Teradata Employee

Re: fastload error "not enough fields in vartext data record number".

Hi,

I could succesfully execute the same flod script using the same data file from my side, please find the out listed below.

Please double check if there are some extra characters in the data file, like a tab etc,,,

--Ivy.

......

**** 11:53:31 Number of AMPs available: 4

**** 11:53:31 Current CLI or RDBMS allows maximum row size: 64K

**** 11:53:31 Character set for this job: ASCII

0002 drop table abc;

**** 11:53:32 RDBMS error 3807: Object 'abc' does not exist.

0003 drop table abc_err;

**** 11:53:32 RDBMS error 3807: Object 'abc_err' does not exist.

0004 drop table abc_err1;

**** 11:53:33 RDBMS error 3807: Object 'abc_err1' does not exist.

0005 create table abc

     (emp_no int

     ,dept_no smallint

     ,last_nm varchar(20)

     ,first_nm varchar(20)

     ,sal decimal(15,2)

     ,s_date date format 'yyyy-mm-dd')

     unique primary index(emp_no);

**** 11:53:34 Command completed successfully

0006 set record vartext ",";

**** 11:53:34 Now set to read 'Variable-Length Text' records

**** 11:53:34 Delimiter character(s) is set to ','

**** 11:53:34 Command completed successfully

0007 define emp_no (varchar(30))

     ,dept_no (varchar(30))

     ,last_nm (varchar(30))

     ,first_nm (varchar(30))

     ,sal (varchar(30))

     ,s_date (varchar(30))

     file = emp_table.txt;

**** 11:53:34 FDL4803 DEFINE statement processed

0008 show;

     FILE = emp_table.txt

     EMP_NO                           OFFSET =      0 LEN =    30 VARCHAR

     DEPT_NO                          OFFSET =     32 LEN =    30 VARCHAR

     LAST_NM                          OFFSET =     64 LEN =    30 VARCHAR

     FIRST_NM                         OFFSET =     96 LEN =    30 VARCHAR

     SAL                              OFFSET =    128 LEN =    30 VARCHAR

     S_DATE                           OFFSET =    160 LEN =    30 VARCHAR

     TOTAL RECORD LENGTH = 192

0009 begin loading abc

     ERRORFILES abc_err, abc_err1

     checkpoint 1;

**** 11:53:51 Number of FastLoad sessions connected = 4

**** 11:53:51 FDL4808 LOGON successful

**** 11:53:51 Number of AMPs available: 4

**** 11:53:51 BEGIN LOADING COMPLETE

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

     =                                                                 =

     =          Insert Phase                                           =

     =                                                                 =

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

0010 insert into abc

     values

     (:emp_no

     ,:dept_no

     ,:last_nm

     ,:first_nm

     ,:sal

     ,:s_date)

     ;

**** 11:53:51 Number of recs/msg: 347

**** 11:53:51 Starting to send to RDBMS with record 1

**** 11:53:51 Sending row 1

**** 11:53:51 Sending row 2

**** 11:53:52 Finished sending rows to the RDBMS

**** 11:53:52 Acquisition Phase statistics:

              Elapsed time: 00:00:01 (in hh:mm:ss)

              CPU time:     0 Seconds

              MB/sec:       0.00

              MB/cpusec:    N/A

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

     =                                                                 =

     =          End Loading Phase                                      =

     =                                                                 =

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

0011 end loading;

**** 11:53:52 END LOADING COMPLETE

     Total Records Read              =  2

     Total Error Table 1             =  0  ---- Table has been dropped

     Total Error Table 2             =  0  ---- Table has been dropped

     Total Inserts Applied           =  2

     Total Duplicate Rows            =  0

     Start:   Wed Aug 26 11:53:52 2015

     End  :   Wed Aug 26 11:53:53 2015

**** 11:53:52 Application Phase statistics:

              Elapsed time: 00:00:00 (in hh:mm:ss)

0012 .LOGOFF;

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

     =                                                                 =

     =          Logoff/Disconnect                                      =

     =                                                                 =

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

**** 11:53:54 Logging off all sessions

**** 11:53:56 Total processor time used = '1.17001 Seconds'

     .        Start : Wed Aug 26 11:53:26 2015

     .        End   : Wed Aug 26 11:53:56 2015

     .        Highest return code encountered = '0'.

**** 11:53:56 FDL4818 FastLoad Terminated

Enthusiast

Re: fastload error "not enough fields in vartext data record number".

Hi Ivyuan,

Thanks for reply. 

I ran the script again and now i am getting error saying

"not enough fields in vartext data record number:3"

It's strange becasue i have only 2 records in input file.

Teradata Employee

Re: fastload error "not enough fields in vartext data record number".

Does record number 2 have a proper end-of-record marker at the end?

And then is there a proper end-of-file after that?

If there happens to have a garbage character on the 3rd line/record, that might be mistaken for data.

-- SteveF
Enthusiast

Re: fastload error "not enough fields in vartext data record number".

Hi Steve,

not sure what do you mean by proper end-of-record marker at the end. 

There are two records in a notepad and its just as its in below format. 

1,2,jones,alex,260000,2015/12/12

2,1,simon,patrick,860000,2015/12/12

I have not placed any full stop or anything after the second record. Also there is no space after 2nd record ends. Although i have tried with space also and got same error.

Thanks,

Rohit

Teradata Employee

Re: fastload error "not enough fields in vartext data record number".

There must be some garbage characters in your data file somewhere after the 2nd record that makes FastLoad think there is a 3rd record to read, and that garbage (possibly unprintable characters) is not enough to fulfill the number of columns for the job.

-- SteveF