All Forums Database
DZanke 14 posts Joined 11/15
26 Jan 2016
BTEQ export FastLoad Import issue

I am trying to export data using BTEQ and import it using FastLoad. We are using Teradata version 14.00.07.12 using a windows 7 client. I read that BTEQs default export mode is FastLoad, yet I've made multiple attempts to load the data exported from BTEQ  using FastLoad w/o success . Every time I attempt a load, I get a message like "Bad file or data definition. The length of: definedvar in row 1 was greate than defined. Defined: 17, Received: 18519". My define statement looks ok, and teh exported file consists of 5 records, the first 2 bytes give the length, followed by the data column, a lf/cr, then the next column length, which looks like it's probably correct.
BTEQ export:

.logon ip/dzanke;

.Set RecordMode On;

.EXPORT Data File=DZanke2.dat;

Select  Vinnie

    From database.DZanke;

    

.EXPORT Reset;

.LOGOFF;

 

This exports a single column, 5 rows as described in the first paragraph.

 

FastLoad:

 

 

logon ip/dzanke;

 

Begin Loading databasename.DZanke

 Errorfiles databasename.ERR1,databasename.ERR2;

 

Define Vinnie ( VarChar(17))

File = DZanke2.dat ;

 

Insert Into databasename.DZanke

(

Vinnie

 )

Values (      :Vinnie

);

End Loading;

LogOff;

 

I've tried everything I can think of, looked in the manuals and on the web and still get this same error. I'm sure I missed something simple, and would appreciate it if someone could tell me what it is, because I can't figure it out.

 

 

Adeel Chaudhry 773 posts Joined 04/08
26 Jan 2016

Can you share the sample output file that is generated via export?

-- If you are stuck at something .... consider it an opportunity to think anew.

DZanke 14 posts Joined 11/15
27 Jan 2016

I could not upload the actual file, but here is a look at it in a Hex editor. To keep it simple, there is only 5 rows, one column, 17 characters long.

Fred 1096 posts Joined 08/04
27 Jan 2016

Is the column defined as CHAR(17), in which case you need to specify that in the DEFINE as well?
 
FastLoad format starts with a record length and if the field was VARCHAR then there would be a field length after that, followed by the data value.

DZanke 14 posts Joined 11/15
27 Jan 2016

Wow, good catch Fred! It was char 17 in the DB, but I read in one of my sources for FastLoad scripts that "FastLoad DEFINE statement allows only VARCHAR format". Either that is incorrect, or I misunderstood what was meant. I changed the define to char and it worked! Thanks, Fred, I was really spinning my wheels on this one, and thanks for the info on FastLoad format.

Fred 1096 posts Joined 08/04
27 Jan 2016

To clarify the restriction:

DEFINE must describe the input fields (not necessarily the same as the target table columns). And if the file format is specified as VARTEXT (delimited text), then the I/O module parses the input record and passes the fields to FastLoad as VARCHARs.

You must sign in to leave a comment.