Junk Characters in FASTEXPORTED Flat file..

Database
l_k
Enthusiast

Junk Characters in FASTEXPORTED Flat file..

hi,

I was exporting the DBC table DBQLSQLTBL using FASTEXPORT.But it is generating some junk characters
in the flat file.The below is the Fastexport script i used.

FASTEXPORT :
==========
begin export

sessionS 8;

.export outfile D:\Teradata\DBC_Scripts\SCRIPT_FILES\dbqlexplaintbl.txt
FORMAT TEXT MODE RECORD
outlimit 100;

SELECT
cast(cast(ProcID AS CHAR(5))||'|'||
cast(CollectTimeStamp AS CHAR(19))||'|'||
cast(QueryID AS CHAR(18))||'|' ||
cast(ExpRowNo AS CHAR(4))||'|' ||
cast(ExplainText AS CHAR(31000))as char(31400))
FROM
dbc.dbqlexplaintbl;

.end export;

.logoff;

The column explainText contains very long text and special character also.Can anyone help me to avoid the junk characters in the exported flat file from the above SELECT query?
12 REPLIES
Enthusiast

Re: Junk Characters in FASTEXPORTED Flat file..

Hi,

Try with this query...

SELECT cast(ProcID AS CHAR(5))||'|'||
cast(CollectTimeStamp AS CHAR(19))||'|'||
cast(QueryID AS CHAR(18))||'|' ||
cast(ExpRowNo AS CHAR(4))||'|' ||
cast(ExplainText AS VARCHAR(31000))
FROM
dbc.dbqlexplaintbl;
l_k
Enthusiast

Re: Junk Characters in FASTEXPORTED Flat file..

Hi,

Again Junk characters are in the Output when i execute the fastload script.

and also in the EXPLAINTEXT column, there is a junk character for every carriage return(Junk character at each end of the line).

output :
======
16382|2010-04-15 02:56:38|163823497217095035|1 | 1) First, we lock a distinct POSSTAGING_LANDING."pseudo table" for
write on a RowHash to prevent global deadlock for
POSSTAGING_LANDING.ISS_CHECKSUM.
2) Next, we lock POSSTAGING_LANDING.ISS_CHECKSUM for write.
3) We do an all-AMPs DELETE from POSSTAGING_LANDING.ISS_CHECKSUM by
way of an all-rows scan with no residual conditions.
4) We spoil the parser's dictionary cache for the table.
-> No rows are returned to the user as the result of statement 1.

Enthusiast

Re: Junk Characters in FASTEXPORTED Flat file..

When TEXT format is used to export from fexp, each record will have arbitrary number of bytes, followed
by an end-of-record marker, which is a:
• Line feed (X'0A') on UNIX platforms
• Carriage-return/line feed pair (X'0D0A') on Windows platforms.So what you are seeing as junk character is actually the EOR marker.

l_k
Enthusiast

Re: Junk Characters in FASTEXPORTED Flat file..

When we look at the above output file,there is a junk character before the first column's value.

ie., 16382

I used trim but again it is showing the same..can you please tell me how to avoid this?
Enthusiast

Re: Junk Characters in FASTEXPORTED Flat file..

It appears that the utility is ignoring the FORMAT clause in your EXPORT statement and defaulting to FASTLOAD format. In that mode, each record is preceded by a 2-byte binary record length indicator.

Seems like a bug to me.
Enthusiast

Re: Junk Characters in FASTEXPORTED Flat file..

The pipe constant you are using as a separator defaults to a varchar, making the whole string a varchar. Cast the whole string:

SELECT
cast(cast(cast(ProcID AS CHAR(5))||'|'||
cast(CollectTimeStamp AS CHAR(19))||'|'||
cast(QueryID AS CHAR(18))||'|' ||
cast(ExpRowNo AS CHAR(4))||'|' ||
cast(ExplainText AS CHAR(31000))as char(31400)) as Char(31050))
FROM
dbc.dbqlexplaintbl;
Enthusiast

Re: Junk Characters in FASTEXPORTED Flat file..

Jimm,If the query is cast to char(31400) will it not have too many spaces at the end of each record?
Enthusiast

Re: Junk Characters in FASTEXPORTED Flat file..

Suma,lavakumar,

Jimm is absolutely right, casting the complete result to char(length) will not give any junk character.

Sima >Spaces will be based on the record length, If you know the maximum length of the row you better specify that number.

Kumar >> it is giving junk character because it is taking it as a varchar so first two characters of the each row will be accompined with junk characters,,, actually it is showing how many bytes in each record.
Enthusiast

Re: Junk Characters in FASTEXPORTED Flat file..

To not to waste the space with trailing spaces I have created the following selects to aid writing the casting part for an arbitrary table. Just run the first three selects and paste the results in the fourth one.

SELECT '''cast("' ||TRIM(ColumnName)||'" AS CHAR(''||'||'trim(max(length("' ||ColumnName||'")))||'''||'))||''''|''''||''||'
FROM dbc.COLUMNS WHERE tablename = 'YOUR_TABLE_NAME' ORDER BY ColumnID;

SELECT 'max(length("' ||TRIM(ColumnName)||'"))+'
FROM dbc.COLUMNS WHERE tablename = 'YOUR_TABLE_NAME' ORDER BY ColumnID;

SELECT COUNT(*) -1 FROM dbc.COLUMNS WHERE tablename = 'YOUR_TABLE_NAME';

SELECT
'cast("cust_id" AS CHAR('||TRIM(MAX(LENGTH("cust_id ")))||'))||''|''||'
as column_casts
, MAX(LENGTH("cust_id")) + 0
as overall_lenth
from YOUR_TABLE_NAME