fexp data doubt

Tools & Utilities
Enthusiast

fexp data doubt

Hello,
I have experience with SQL but I am new to Teradata and fexp tool, I am given a task to download data from a Teradata database, I have started with a simple query like below:
.LOGTABLE cmpdb.lg1;
.LOGON db1/nr2876,pwd;
.BEGIN EXPORT;
.EXPORT OUTFILE tbl1 format text mode record;
SELECT trim(transaction_dt) FROM table1;
.END EXPORT;
.LOGOFF;

But I am getting extra characters in the beginning of each record(shown below). What is this character and how I could avoid this character in my data download? Your help will be appreciated. I have tried select statement with CAST too, but I got the same result. I also tried different varchar and integer fields with same result(extra characters in the front).

^H20050907
^H20050912
^H20050917
^H20050925
^H20050923
^H20050923
^H20050730
^H20050730
^H20050730

Also I just have this basic question, what tool is better to download huge data(around 30 million records each with 70 int/date/varchar fields) from Teradata, Bteq or fexp or any other tools? My query contains lot of create, insert and select statements.

Thank you for your time,
nishi
4 REPLIES
Enthusiast

Re: fexp data doubt

yap.. Nishi.
Open the exported file in Text pad and delte the first Junk..it will work fine
regards
Satya
Teradata Employee

Re: fexp data doubt

The datatype of the result of TRIM is VARCHAR, so the exported record has a two-byte binary length field in front of the character data. For this specific example, it's easy enough to CAST the value to fixed CHAR (in fact, there is no real need for TRIM here, it's just doing implicit CAST):

SELECT CAST(transaction_dt as CHAR(8) FORMAT 'YYYYMMDD')
from table1;

If you are trying to use FastExport to create a delimited text file with multiple fields, it gets trickier. Typical approach is to do something like this:

SELECT trim(fld1)||','||trim(fld2)||...

That way you don't have length fields embedded in the middle of the data but you still have the two-byte prefix. One way to get rid of it would be to CAST the entire expression to a very large fixed CHAR but then you potentially have lots of trailing spaces in your output file - not exactly an improvement. Post-processing the flat file with some utility to strip two bytes off each record is another possibility. You could also use an OUTMOD to delete the two leading bytes from each record as it is being written.
Enthusiast

Re: fexp data doubt

====
I meet the same problems. But all the ways you guys suggested don't work for me :-(

"That way you don't have length fields embedded in the middle of the data but you still have the two-byte prefix. " - that's what I met.

"One way to get rid of it would be to CAST the entire expression to a very large fixed CHAR but then you potentially have lots of trailing spaces in your output file - not exactly an improvement."
-I did what you said, but the two-byte prefix still there.

" Post-processing the flat file with some utility to strip two bytes off each record is another possibility. "
- I hate to do that, the file is huge.

"You could also use an OUTMOD to delete the two leading bytes from each record as it is being written. "
-Could anybody tell me what does this mean? how could I do that? Thanks a lot!!!

Enthusiast

Re: fexp data doubt

Hi Fred,

i am going to configure Teradata Appliance Backup Utility, could you please provide configuration steps.

Thanks,

Kamma