BTEQ export FastLoad Import issue

Database
Enthusiast

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.


5 REPLIES
Teradata Employee

Re: BTEQ export FastLoad Import issue

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

Enthusiast

Re: BTEQ export FastLoad Import issue

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.

Teradata Employee

Re: BTEQ export FastLoad Import issue

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.

Enthusiast

Re: BTEQ export FastLoad Import issue

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.

Teradata Employee

Re: BTEQ export FastLoad Import issue

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.