Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Help Fast Load Problem

Status
Not open for further replies.
mpramods

mpramods

Technical User
Jun 3, 2003
50
US
Hi,
I am getting a peculiar problem while trying to load data using fastload. I am trying using the Demo Version of Teradata and the fastscript is straight from the teradata demo documentation.

Can anybody help me with what is the prolem here.

When I run this script I get the error as:

**** 17:53:53 Number of recs/msg: 698
**** 17:53:53 Starting to send to RDBMS with record 1
**** 17:53:53 Incorrect number of bytes returned from a File Read! Expected: 84, Received: 45

FastLoad - Enter your command:
end loading;
===========================================================
End Loading Phase
============================================================

0011 end loading;

**** 17:53:53 RDBMS error 2719: FASTLOAD END LOADING cannot be inside the Data Loading phase.


The script is as follows:

sessions 2;
errlimit 25;
logon dbc/dbc,dbc;
DATABASE pramod;
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=C:\insert.input.txt;
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;

The input flat file is :

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

Thanks
Pramod.
 
Sort by date Sort by votes
Try to use
SET RECORD VARTEXT;

instead of UNFORMATTED
 
Upvote 0 Downvote
Hi cici,
I used SET FORMAT VARTEXT instead of unformatted and got the following error. The target table is still empty.

**** 10:32:14 DEFINE Syntax Error
**** 10:32:14 Invalid column type for column: DELIM0
Only VARBYTE, VARCHAR or LONG VARCHAR column types supported for Variable-Length Text record type
===========================================================
*** 10:32:15 Total processor time used = '0.0701008 Seconds'
. Start : Wed Jun 04 10:32:12 2003
. End : Wed Jun 04 10:32:15 2003
. Highest return code encountered = '8'.
**** 10:32:15 FDL4818 FastLoad Terminated
 
Upvote 0 Downvote
In your define clause, change all the char data type to varchar.
 
Upvote 0 Downvote
Thanks cici and BillDHS
for answering my query. But I am still not able to run it.

Is is possible that somebody would try to run my fastload script and let me know how to solve the problem. I have the script and input flat file listed above.

Thank you very much,
mpramods.
 
Upvote 0 Downvote
You have to remove all but the first (the data starts with a '|') delimX fields:

set record vartext "|";

define
dummy(varchar(1)),
EmpNo(varchar(9)),
Name(varchar(12)),
DeptNo(varchar(3)),
PhoneNo(varchar(4)),
JobTitle(varchar(12)),
Salary(varchar(9)),
YrsExp(varchar(2)),
DOB(varchar(11)),
Sex(varchar(1)),
Race(varchar(1)),
MStat(varchar(1)),
EdLev(varchar(2)),
HCap(varchar(2))


That's it ;-)

Dieter
 
Upvote 0 Downvote
Hi Dieter,

I made changes in the script as per your suggestions. But still I am getting the following error.

**** 11:46:16 Number of recs/msg: 845
**** 11:46:16 Starting to send to RDBMS with record 1
**** 11:46:16 I/O Error on File Read: 35, Text: EOF encountered before end of record
============================================================
Logoff/Disconnect
============================================================
**** 11:46:16 Logging off all sessions
**** 11:46:17 Total processor time used = '0.100144 Seconds'
. Start : Thu Jun 05 11:46:12 2003
. End : Thu Jun 05 11:46:17 2003
. Highest return code encountered = '12'.
**** 11:46:17 FastLoad Paused.



Are there any other changes that need to be made. Your help is appreciated.

Thanks,
mpramods
 
Upvote 0 Downvote
The last record in your input file is producing that error, but if you use .SET RECORD VARTEXT it's usually a different one "Not enough fields..."

Could you please post your script again.

Dieter
 
Upvote 0 Downvote
Thanks for the fast reply Dieter,
Here is my script and input file.

sessions 2;
errlimit 25;

logon dbc/dbc,dbc;
DATABASE pramod;

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 VARTEXT "|";

define
dummy(varchar(1)),
EmpNo(varchar(9)),
Name(varchar(12)),
DeptNo(varchar(3)),
PhoneNo(varchar(4)),
JobTitle(varchar(12)),
Salary(varchar(9)),
YrsExp(varchar(2)),
DOB(varchar(11)),
Sex(varchar(1)),
Race(varchar(1)),
MStat(varchar(1)),
EdLev(varchar(2)),
HCap(varchar(2))


file=C:\insert.input.txt;

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;

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

 
Upvote 0 Downvote
Can't help you here, if i cut'n'paste your script everything runs ok.
Make shure that there's no linebreak after the last record.
If that doesn't help:
drop the table,
drop the error tables,
submit a DELETE FROM sysadmin.fastlog

If it's still not working, then i can't help you anymore ;-(

Dieter
 
Upvote 0 Downvote
Hi,
I am still not able to run the script. If anybody could help me with this, the help would be appreciated.

Thanks
mpramods
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

tmcrouse
  • Locked
  • Question
Tera SQL Update help
Replies
2
Views
131
tmcrouse
tmcrouse
peac3
  • Locked
  • Question
Dynamic Join help please... tough query
Replies
0
Views
107
peac3
peac3
btrini
  • Locked
  • Question
Fastload logon shows as invalid but I can log on to Teradata SQL Assistant with the same credentials
Replies
1
Views
126
SamBones
SamBones
abhijeetgupta29nov
  • Locked
  • Question
Help Sql: Please verify solution and let me know if any better to achieve this ....
Replies
0
Views
61
abhijeetgupta29nov
abhijeetgupta29nov
larry609
  • Locked
  • Question
Teradata Field names and DAO/ADO/OLEDB access problem
Replies
0
Views
60
larry609
larry609

Part and Inventory Search

Sponsor

Back
Top