All Forums Tools
BeingHuman 3 posts Joined 06/09
26 Jun 2009
FastLoad Error -incorrect number of bytes returned

Error occured while running following script :Error :incorrect number of bytes returned from a File ReadExpected 72,Received : 33Script Source :Chapter 1: IntroductionA FastLoad ExampleTeradata FastLoad Reference 1 – 35Error 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;definedelim0(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

Manish Teradata India
feinholz 1234 posts Joined 05/08
01 Jul 2009

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

--SteveF

dnoeth 4628 posts Joined 11/04
02 Jul 2009

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

Dieter

RATHOD 8 posts Joined 04/15
03 May 2015

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.....

dnoeth 4628 posts Joined 11/04
03 May 2015

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.
 

Dieter

RATHOD 8 posts Joined 04/15
03 May 2015

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.

dnoeth 4628 posts Joined 11/04
03 May 2015

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

Dieter

RATHOD 8 posts Joined 04/15
04 May 2015

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.

TDThrottle 51 posts Joined 11/11
05 May 2015

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!!

RATHOD 8 posts Joined 04/15
06 May 2015

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.

You must sign in to leave a comment.