Viewing Fastload Errors

Tools
Enthusiast

Viewing Fastload Errors

I've tried using BTEQ to export the data from the _e1 error table that fastload creates, but the data comes out in what I believe is fastload format, which doesn't make it very easy for my to analyze.

Is there any way to conver this back to the format of the original file?

The original file is to large to get the record out of (not on Uniz, so grep is not an option).

Here's what I did in BTEQ:

BTEQ -- Enter your DBC/SQL request or BTEQ command:
.set recordmode on

.set recordmode on
BTEQ -- Enter your DBC/SQL request or BTEQ command:
.export data file=weblog_0911_2683.txt

.export data file=weblog_0911_2683.txt
*** To reset export, type .EXPORT RESET
BTEQ -- Enter your DBC/SQL request or BTEQ command:
select * from staging.weblog_e1
where errorcode=2683;

select * from staging.weblog_e1
where errorcode=2683;

*** Success, Stmt# 1 ActivityCount = 19
*** Query completed. 19 rows found. 3 columns returned.
*** Total elapsed time was 1 second.

Here's what the file looks like:
[Y9Gÿÿÿÿê?  ÿÿÿÿÿÿÿÿÿÿÿÿý
1158001696 ss zh-tw 2006-09-11 15:08:16 4851137037954209665 726780682063314945  N
140.129.59.16 0 * http://www
...

The original file was tab-delimited.

Thanks,

James
12 REPLIES
Teradata Employee

Re: Viewing Fastload Errors

Export only the DataParcel column (don't use SELECT *), then remove (or ignore) the first two bytes from each record in the export file.
Enthusiast

Re: Viewing Fastload Errors

using FastExport?
Teradata Employee

Re: Viewing Fastload Errors

I would use BTEQ (RECORDMODE, DATA options as in your example) because the table should be small. But FastExport (FORMAT UNFORMAT, MODE RECORD) would work too. Either way, since DataParcel is VARBYTE, the exported file will have a two-byte unsigned binary length field added to the beginning of each record.
Teradata Employee

Re: Viewing Fastload Errors

If you use SQL Assistant you can browse the records. SQL Assistant will place the data-parcel in a text file and open it when you click the data-parcel button.

At least it does with the version we are using: 7.1.0
Enthusiast

Re: Viewing Fastload Errors

I've tried both those approaches, but the value in the data parcel itself looks to be encoded. It all comes out in hex. I convert back to ASCII, but I still get a bunch of strange characters.
Teradata Employee

Re: Viewing Fastload Errors

That's as close as you can get - unless you want to write a program that will convert binary values to ascii. I've done it before but it's an ugly time-consuming process.
Enthusiast

Re: Viewing Fastload Errors

Thanks everyone. I appreciate the responses.

Think I'll scour the web for a binary to ascii converter...
Teradata Employee

Re: Viewing Fastload Errors

Oh, I didn't pick up on the fact that your input was FORMAT VARTEXT with TAB delimiters.

The DataParcel field is the record sent to Teradata, but that is after FastLoad client converted the original text to VARCHAR fields, more or less in "FastLoad format" as you guessed: two-byte length for the record, two-byte length of the first text value, first text field, two-byte length of the second text value, second text field, etc.

I suppose you could EXPORT the DataParcel field to a file using BTEQ Or FastExport, then FastLoad it back to a table with all fields defined as VARCHAR, then EXPORT from that table as tab-delimited using SQL Assistant.
Enthusiast

Re: Viewing Fastload Errors

I was hoping there was a more streamlined way of doing that. Sort of an "unformat" function.

Thanks again.