Teradata export file contains junk characters

Tools
Enthusiast

Teradata export file contains junk characters

Hi,
I have written the following code and I am expecting only a single date value.

.MAXERROR 1;

.SET FORMAT ON
.SET ECHOREQ OFF
.EXPORT DATA FILE=${DW_OUT}/${JOB_ENV}/${SUBJECT_AREA}/lastunsuccdate.dat,open
Select DATA_START_DATE From ${readDB}.DW_TM_USR_LSTG_BATCH Where BATCH_STATUS IS NULL;
.EXPORT DATA FILE=${DW_OUT}/${JOB_ENV}/${SUBJECT_AREA}/lastunsuccdate.dat,close

.QUIT;

But my output file gives me one line of junk. When I cat there is a 0 and when I vi it I can see other characs. Does anyone know what can be done? Please refer to the outputs below. So though the query gives a date in Queryman/SQL Assistant, it writes this junk value to the file when I run it using the above code. So after each run it adds one line of junk to the output file. Please let me know.

cat lastunsuccdate.dat
0

vi lastunsuccdate.dat
^D^P0\220
5 REPLIES
Enthusiast

Re: Teradata export file contains junk characters

While using the "DATA" keyword in an "EXPORT" command you should consider the following:

"BTEQ returns data in Record mode and stores it in FastLoad format output file. Use this format to interchange data between BTEQ, FastLoad, and FastExport. MultiLoad also accepts the FastLoad format."
(From the Basic Teradata Query Reference manual)

Those "junk" characters basically contain the line length in binary format.

To get rid of 'em, try by rewriting the script like:

.MAXERROR 1;

.SET ECHOREQ OFF
.EXPORT DATA FILE=${DW_OUT}/${JOB_ENV}/${SUBJECT_AREA}/lastunsuccdate.dat
.SET INDICDATA OFF
Select
cast((DATA_START_DATE (format 'yyyy-mm-dd'))
From ${readDB}.DW_TM_USR_LSTG_BATCH Where BATCH_STATUS IS NULL;
.EXPORT RESET

.QUIT;

Enthusiast

Re: Teradata export file contains junk characters

Thanks Daniel, but I realized that there is more to the problem. Initially I had typed in the date value for testing but I realized that the date never got overwritten. Infact it stayed there and junk characters kept on appending to the file after each run. So the query is also not returning the date value. I need help.
Enthusiast

Re: Teradata export file contains junk characters

Whenever you export data to a file with the mentioned method, data is being appended to the file.
You have to manually delete the file before running the script or issue an ".OS" command inside the BTEQ like:

Under Windows:

.OS del filename

Under Unix:

.OS rm filename

The script would then look like:

.MAXERROR 1;
.SET ECHOREQ OFF
.OS rm ${DW_OUT}/${JOB_ENV}/${SUBJECT_AREA}/lastunsuccdate.dat
.EXPORT DATA FILE=${DW_OUT}/${JOB_ENV}/${SUBJECT_AREA}/lastunsuccdate.dat
.SET INDICDATA OFF
Select cast((DATA_START_DATE (format 'yyyy-mm-dd')) From ${readDB}.DW_TM_USR_LSTG_BATCH Where BATCH_STATUS IS NULL;
.EXPORT RESET
.QUIT
Enthusiast

Re: Teradata export file contains junk characters

Hi Daniel,
Just removed the format part in the select statement and it worked. It returned the date with 8 leading spaces. Figuring a way to get rid of those in the select itself.
Thanks for your help.
Enthusiast

Re: Teradata export file contains junk characters

Using the bteq utility, I'm exporting the data from the DB to the flat file. However junk characters as below is getting exported to the file.

^WM- ^@^@^@^FI}M-4 M-^?M-^?M-^?M-^?{M-FM-^Vh^@^@^@^@^@^@^@^@10141211052521R_NUMBERM-9`^@^@^@^FM-^MGM-~H^@^@^@^FIM-^C<`^...- M-^?M-^?M-^?M-^?{M-FM-^Vh^@^@^@^@^@^@^@^@20141211050147TUS^@^@^FJ@M-NM- ^@^@^@^FM-^MGM-~M-^X^@^@^@^FIM-6M-~M- ^@^@^@H^VM-:oM-8^@^@^@^FM-^MHM-/`^@^@^@^FJNM-y`M-^?M-^?M-^?M-^?{M-FM-^Vh^@^@^@^@^@^@^@^@CTN_SUBMARKE... ^@^@^@HM-^M--M->M-o^@^@^@^FM-^LM-6fp^@^@^@^@^@^@^@^@M-^?M-^?M-^?M-^?{M-FM-^V

Below is the SELECT statement, I'm using:

SELECT

ACCT_NBR ||'|'||

acct_type_cd ||'|'||

acct_sub_type_cd ||'|'||

ent_curr_acct_sts_cd ||'|'||

acct_subsrptn_eff_dt ||'|'||

curr_srv_accs_nbr ||'|'||

srv_accs_id ||'|'||

orgnl_srv_dt ||'|'||

src_sys_orgnl_srv_dt ||'|'||

TRIM(curr_subsrptn_sts_cd) ||'|'||

CURR_IMSI ||'|'||

PRD_CD ||'|'||

PRD_DESC ||'|'||

PRD_CAT_CD ||'|'||

blng_eff_dt ||'|'||

blng_end_dt ||'|'||

CAST(entry_dt_tm AS VARCHAR(20)) ||'|'||

SUBSRPTN_STS_RSN_CD ||'|'||

SUBSRPTN_STS_RSN_DESC ||'|'||

CURR_IMEI (title '')

FROM SBP_PREPAID_SUBS_INFO;

Please assist