All Forums Database
l.k 75 posts Joined 10/08
14 Apr 2010
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?

Suma.Manu 15 posts Joined 11/09
14 Apr 2010

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 75 posts Joined 10/08
15 Apr 2010

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.

Suma.Manu 15 posts Joined 11/09
15 Apr 2010

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 75 posts Joined 10/08
16 Apr 2010

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?

Jim Chapman 449 posts Joined 09/04
16 Apr 2010

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.

Jimm 298 posts Joined 09/07
16 Apr 2010

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;

Suma.Manu 15 posts Joined 11/09
18 Apr 2010

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

ganmku 9 posts Joined 04/10
19 Apr 2010

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.

Ganmku

DiEgoR 33 posts Joined 08/06
23 Apr 2010

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

input output putput

lazycat 3 posts Joined 05/10
28 May 2010

If it help I know how to do this with tpt. I met with the same problem in fastexp. The solution is that you need to cast the all concatenation chane to fix char. That works and it doesn't have special characters. But it increase the size of the flat file. you can run .os command a sed if it is in unix. The easiest way is with tpt. If you need i can send a script which works.

Srie 1 post Joined 11/10
30 May 2011

Hi, Could u please send me the TPT script which works for the FastExp.. I'
m getting few errors, when i created the TPt with the export operator... It would be great, if u could send it at the earliest... Thanks in Advance.

brindamaiti 11 posts Joined 10/11
19 Oct 2011

Hi , Can you please send the tpt script as well to me.

Thanks a lot

You must sign in to leave a comment.