Fastexport - how to export without beginning two bytes ?

Tools & Utilities
Enthusiast

Fastexport - how to export without beginning two bytes ?

Hi,

i have to export repeatidly some 30 M lines in a "text" format and variable length if possible.
Fastexport (V2R6) adds a two-bytes indicator (as specified in the Fastexport doc.) at the beginning of the records, wathever the mode, Indicator or Record.
Does anybody know a way not to keep those two bytes ?
I tried to cast the whole chain - a concatenation of cast(columns...) - but the fact is the two bytes stay ...

Thanks for any suggestion
6 REPLIES
Teradata Employee

Re: Fastexport - how to export without beginning two bytes ?

Create a small OUTMOD to strip off the two-byte VARCHAR length field.

Re: Fastexport - how to export without beginning two bytes ?

hi Fred, could you please give an example how to create such a outmod?
Enthusiast

Re: Fastexport - how to export without beginning two bytes ?

Another alternative if you are in a UNIX environment is to use AWK or SED on the file after it has been output to strip the leading to characters from each record.

Alternatively, you can concatenate each field together and cast it to a fixed length, pipe delimited field. If your output is fixed length the two-byte variable should not be generated.

SELECT CAST(TRIM(field1) || '|' || TRIM(field2) AS CHAR(255)
FROM MyDB.MyView
SAMPLE 10
;
Teradata Employee

Re: Fastexport - how to export without beginning two bytes ?

Robpaller is right! Thanks.

I would like to add an example:

If we export a table using TEXT format in record mode. The data exported does not contains 2 byte length, if the fields are of type CHAR().

For example: if table t1 has 2 columns, c1 is of type char(3), c2 is of type char(4)

If we do:

select c1, c2 from t1;

The records exported will not contains 2-byte length at the beginning.

But if we do:

select c1 || c2 from t1

The records exported will contain 2 byte length at the beginning, because concatenation returns VARCHAR() type not CHAR() type.

The SOLUTION, for not having 2-byte length at the beginning of records, is casting the concatenation to CHAR() type:

select cast(c1 || c2) as char(7) from t1;

Enthusiast

Re: Fastexport - how to export without beginning two bytes ?

Hi,

I have an issue while exporting and loading the data from teradata tables through a delimited text file ! The sample export file is as follows:

DEFINE JOB EXTRACT_DATA_LOAD

DESCRIPTION 'DATA EXPORT JOB'

(

INCLUDE 'SOURCE_TABLE_NAME.schema';

DEFINE OPERATOR SQL_SELECTOR

DESCRIPTION 'SQL OPERATOR'

TYPE  SELECTOR

SCHEMA SOURCE_TABLE_NAME_SCHEMA

ATTRIBUTES

(

VARCHAR PrivateLogName = 'MyLog',

VARCHAR TdpId = 'EDWUAT',

VARCHAR UserName = 'itunes_stg_user',

VARCHAR UserPassword = '$edwmnt1',

VARCHAR ReportModeOn = 'Y',

VARCHAR SelectStmt = 'select col1 || col2 || col3 || col4 || col5 from ITUNES_STG.ITS_SONG_UCODE;'

);

DEFINE OPERATOR FILE_WRITER

DESCRIPTION 'DataConnector WRITER'

TYPE DATACONNECTOR CONSUMER

SCHEMA *

ATTRIBUTES

(

VARCHAR PrivateLogName = 'dataconlog',

VARCHAR DirectoryPath = 'path where the data file will get generated and placed',

VARCHAR FileName = 'filename.txt',

VARCHAR IndicatorMode = 'N',

VARCHAR OpenMode = 'Write',

VARCHAR Format = 'DELIMITED',

VARCHAR TextDelimiter = '|',

VARCHAR ReportModeOn = 'Y'

);

LOAD  INTO OPERATOR

( FILE_WRITER  [1] )

SELECT * FROM OPERATOR

( SQL_SELECTOR [1] );

);

I am not able to get the data exported from the teradata table  as i am faced with errors like :

FILE_WRITER: TPT19007 DataConnector Consumer operator Instances: 1

FILE_WRITER: TPT19003 ECI operator ID: FILE_WRITER-17727714

SQL_SELECTOR: connecting sessions

SQL_SELECTOR: restarting the job

FILE_WRITER: TPT19404 pmOpen failed. Requested file not found (4)

FILE_WRITER: TPT19304 Fatal error opening file.

Please help.

Regards,

Varsha

Teradata Employee

Re: Fastexport - how to export without beginning two bytes ?

Try:

APPLY INTO OPERATOR

instead of

LOAD INTO OPERATOR

-- SteveF