FastLoad Error -incorrect number of bytes returned

Tools & Utilities

FastLoad Error -incorrect number of bytes returned

Error occured while running following script :

Error :incorrect number of bytes returned from a File Read
Expected 72,Received : 33

Script Source :
Chapter 1: Introduction
A FastLoad Example
Teradata FastLoad Reference 1 – 35

Error occured while running following script :

sessions 2;
errlimit 25;
logon tdpid/username,password;
CREATE TABLE employee (
EmpNo SMALLINT FORMAT ‘9(5)’ BETWEEN 10001 AND 32001 NOT NULL,
Name VARCHAR(12),
DeptNo SMALLINT FORMAT ‘999’ BETWEEN 100 AND 900 ,
PhoneNo SMALLINT FORMAT ‘9999’ BETWEEN 1000 AND 9999,
JobTitle VARCHAR(12),
Salary DECIMAL(8,2) FORMAT ‘ZZZ,ZZ9.99’ BETWEEN 1.00 AND 999000.00 ,
YrsExp BYTEINT FORMAT ‘Z9’ BETWEEN -99 AND 99 ,
DOB DATE FORMAT ‘MMMbDDbYYYY’,
Sex CHAR(1) UPPERCASE,
Race CHAR(1) UPPERCASE,
MStat CHAR(1) UPPERCASE,
EdLev BYTEINT FORMAT ‘Z9’ BETWEEN 0 AND 22,
HCap BYTEINT FORMAT ‘Z9’ BETWEEN -99 AND 99 )
UNIQUE PRIMARY INDEX( EmpNo ) ;
set record unformatted;
define
delim0(char(1)),
EmpNo(char(9)), delim1(char(1)),
Name(char(12)), delim2(char(1)),
DeptNo(char(3)), delim3(char(1)),
PhoneNo(char(4)), delim4(char(1)),
JobTitle(char(12)), delim5(char(1)),
Salary(char(9)), delim6(char(1)),
YrsExp(char(2)), delim7(char(1)),
DOB(char(11)), delim8(char(1)),
Sex(char(1)), delim9(char(1)),
Race(char(1)), delim10(char(1)),
MStat(char(1)), delim11(char(1)),
EdLev(char(2)), delim12(char(1)),
HCap(char(2)), delim13(char(1)),
newlinechar(char(1))
file=insert.input;
show;
begin loading employee errorfiles error_1, error_2;
insert into employee (
:EmpNo,
:Name,
:DeptNo,
:PhoneNo,
:JobTitle,
:Salary,
:YrsExp,
:DOB,
:Sex,
:Race,
:MStat,
:EdLev,
:HCap
);
end loading;
logoff;

Comments Appreciated
9 REPLIES
Teradata Employee

Re: FastLoad Error -incorrect number of bytes returned

Looks like you are using delimited data.
Any reason why you are not using SET RECORD VARTEXT?
-- SteveF
Junior Contributor

Re: FastLoad Error -incorrect number of bytes returned

Hi Manish,
are you running that script on a Teradata Express, i.e. Windows?

Then you have to change the newlinechar to a char(2), because on Unix it's just line feed '0A'xc whereas on Windows it's carriage return/line feed '0D0A'xc.

Or you change the format to "set record TEXT;" and remove the newlinechar.

Dieter
Enthusiast

Re: FastLoad Error -incorrect number of bytes returned

Hi dieter,

i am new to teradata and i am practicing from online teradata manual www.info.teradata.com

i am using windows without unix platform.

now i am getting the same error

FastLoad Error -incorrect number of bytes returned from a file read.

i am have the input data like this

|10021 |Brown, Jo |200|2312|Development |63000.00 |20|Jan 01 1955|F| |M|16| 0|

|10001 |Jones, Bill |100|5376|President |83000.00 |15|Jan 01 1960|M| |M|14| 0|

|10002 |Smith, Jim |100|4912|Sales |73000.00 |10|Jan 01 1970|M| |M|13| 1|

|10028 |Lee, Sandra |200|5844|Support |77000.00 | 4|Jan 01 1971|F| |M|18| 0|

|10029 |Berg, Andy |200|2312|Test |67000.00 |10|Jan 01 1967|M| |M|15| 0|

|10023 |Ayer, John |300|4432|Accounting |52000.00 | 8|Jan 01 1965|M| |M|13| 0|

 

now i am trying to create a new empty table and want to insert this data into empty table by using fastload script.

The table was created successfully but the data is not loaded.

I am facing this error(incoorect number of bytes returned from a file read )

please help me to come out of this.

 

i am pasting the fastlaod script here,

 

 

sessions 2;

errlimit 25;

logon tdpid/username,password;

CREATE TABLE employee (

       EmpNo SMALLINT FORMAT ‘9(5)’ BETWEEN 10001 AND 32001 NOT NULL,

       Name VARCHAR(12),

       DeptNo SMALLINT FORMAT ‘999’ BETWEEN 100 AND 900 ,

       PhoneNo SMALLINT FORMAT ‘9999’ BETWEEN 1000 AND 9999,

       JobTitle VARCHAR(12),

       Salary DECIMAL(8,2) FORMAT ‘ZZZ,ZZ9.99’ BETWEEN 1.00 AND 999000.00 ,

       YrsExp BYTEINT FORMAT ‘Z9’ BETWEEN -99 AND 99 ,

       DOB DATE FORMAT ‘MMMbDDbYYYY’,

       Sex CHAR(1) UPPERCASE,

       Race CHAR(1) UPPERCASE,

       MStat CHAR(1) UPPERCASE,

       EdLev BYTEINT FORMAT ‘Z9’ BETWEEN 0 AND 22,

       HCap BYTEINT FORMAT ‘Z9’ BETWEEN -99 AND 99 )

       UNIQUE PRIMARY INDEX( EmpNo ) ;

set record unformatted;

define

       delim0(char(1)),

       EmpNo(char(9)), delim1(char(1)),

       Name(char(12)), delim2(char(1)),

       DeptNo(char(3)), delim3(char(1)),

       PhoneNo(char(4)), delim4(char(1)),

       JobTitle(char(12)), delim5(char(1)),

       Salary(char(9)), delim6(char(1)),

       YrsExp(char(2)), delim7(char(1)),

       DOB(char(11)), delim8(char(1)),

       Sex(char(1)), delim9(char(1)),

       Race(char(1)), delim10(char(1)),

       MStat(char(1)), delim11(char(1)),

       EdLev(char(2)), delim12(char(1)),

       HCap(char(2)), delim13(char(1)),

       newlinechar(char(1))

file=insert.input;

show;

begin loading employee errorfiles error_1, error_2;

insert into employee (

       :EmpNo,

       :Name,

       :DeptNo,

       :PhoneNo,

       :JobTitle,

       :Salary,

       :YrsExp,

       :DOB,

       :Sex,

       :Race,

       :MStat,

       :EdLev,

       :HCap

);

end loading;

logoff;

Please help me..

thanks.....

Junior Contributor

Re: FastLoad Error -incorrect number of bytes returned

You define your input as fixed width, but the actual data is variable width.

Use set record VARTEXT;, change all fields to VARCHAR, remove all delims and the final newlinechar.

And because your first character is a delimiter you should define a dummy varchar(1).

Btw, why do you start learning FastLoad, this is an old legacy tool, better start with TPT.

Enthusiast

Re: FastLoad Error -incorrect number of bytes returned

Thanks Dieter,

for your advice to start with TPT..I will go for it.

But Now I am getting another as

I/O Error on File Read: 35, Text: EOF encountered before end of record

Please help me.

Thanks.

Junior Contributor

Re: FastLoad Error -incorrect number of bytes returned

Check if there's a final linebreak after the last line

Enthusiast

Re: FastLoad Error -incorrect number of bytes returned

hi dieter,

i am getting all 24 records and 5 columns while i am using td sql asst.

but when i am using bteq, the complete result set is missing by giving message as shown below.

why??

i dont know where it is returning columns..

please help..

 BTEQ -- Enter your DBC/SQL request or BTEQ command:

SEL * FROM PROJECT;

SEL * FROM PROJECT;

 *** Query completed. 24 rows found. 5 columns returned.

 *** Total elapsed time was 1 second.

 BTEQ -- Enter your DBC/SQL request or BTEQ command:

Thanks.

Enthusiast

Re: FastLoad Error -incorrect number of bytes returned

Check Bteq SHOW CONTROL setting for RETCANCEL and RETLIMIT

To see the current settings of formatting controls, specify:

.SHOW CONTROLS

Try after reseting the amount of display data using:

.SET RETLIMIT 0

Thanks!!

Enthusiast

Re: FastLoad Error -incorrect number of bytes returned

Hi All,

I am confused with the usage of multiset table in real time.

what exactly i mean is 

where do we require the duplicate rows and what is the use of complete duplicate row in real time.

why we store complete duplicate records and uses of it???

In which environment the complete duplicate record is useful??

 

please make me clear with this concept with any real time scenario usage of complete duplicate records.

Thanks All.

RATHOD.