Fast export in unix

UDA

Fast export in unix

/opt/teradata/client/bin/fexp <<EOP

.LOGTABLE load_work.datastore_log1 ;
.LOGON BOX/userid,password;
.BEGIN EXPORT
SESSIONS 20;
.EXPORT OUTFILE $path MODE RECORD FORMAT TEXT;

select
trim(cast(PROC_YYMM_DT as char(5))) (title ''),
cast('~' as char(1)),
trim(cast(ACCNO as char(21))) (title ''),
cast('~' as char(1)),
(CASE
WHEN ACC_CO_NO IS NULL
THEN CAST('?' AS CHAR(1))
ELSE trim(CAST(ACC_CO_NO AS char(21)))
END) (title ''),
cast('~' as char(1)),
trim(cast(ACC_APPSYS_ID as char(3))) (title ''),
cast('~' as char(1)),
(CASE
WHEN ACC_CHFGL_MT_TR_AM IS NULL
THEN CAST('?' AS CHAR(1))
ELSE trim(CAST(ACC_CHFGL_MT_TR_AM as char(18)))
END) (title ''),
cast('~' as char(1)),
(CASE
WHEN ACC_CHFGL_LT_TR_AM IS NULL
THEN CAST('?' AS CHAR(1))
ELSE trim(CAST(ACC_CHFGL_LT_TR_AM as char(18)))
END) (title ''),
cast('~' as char(1)),
(CASE
WHEN ACC_RECGL_LT_TR_AM IS NULL
THEN CAST('?' AS CHAR(1))
ELSE trim(CAST(ACC_RECGL_LT_TR_AM as char(18)))
END) (title ''),
cast('~' as char(1)),
(CASE
WHEN BNKRPT_NOTIFTN_DT IS NULL
THEN CAST('?' AS CHAR(1))
ELSE CAST(CAST(BNKRPT_NOTIFTN_DT AS FORMAT 'YYYY-MM-DD')
AS CHAR(10))
END) (title '')
from vrtl.core_rcvry
where
proc_yymm_dt = 10811 and
acc_appsys_id in ('599');

.END EXPORT ;
.LOGOFF;
EOP

~ as delimiter

i am using the following query to fast export.. but i getting some junk characters prefixed to each column value ... what is the problem??.... it queries perfectly in teradata though..
1 REPLY

Re: Fast export in unix

That is the result of the variable length offset's being placed there by FastLoad.

You can strip them out as a post process or concatenate the fields and delimiter together and cast it to a fixed length.

Try this:

select CAST( PROC_YYMM_DT || '~' ||

ACCNO || '~' ||

(CASE WHEN ACC_CO_NO IS NULL

THEN '?'

ELSE ACC_CO_NO

END) || '~' ||

ACC_APPSYS_ID || '~' ||

(CASE WHEN ACC_CHFGL_MT_TR_AM IS NULL

THEN '?'

ELSE ACC_CHFGL_MT_TR_AM

END) || '~' ||

(CASE WHEN ACC_CHFGL_LT_TR_AM IS NULL

THEN '?'

ELSE ACC_CHFGL_LT_TR_AM

END) || '~' ||

(CASE WHEN ACC_RECGL_LT_TR_AM IS NULL

THEN '?'

ELSE ACC_RECGL_LT_TR_AM

END)

(CASE WHEN BNKRPT_NOTIFTN_DT IS NULL

THEN '?'

ELSE CAST(CAST(BNKRPT_NOTIFTN_DT AS FORMAT 'YYYY-MM-DD') AS CHAR(10))

END)

AS CHAR( ) (TITLE '')

from vrtl.core_rcvry

where

proc_yymm_dt = 10811 and

acc_appsys_id in ('599');