All Forums Tools
Pierre 10 posts Joined 10/04
02 Feb 2009
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

Fred 1096 posts Joined 08/04
02 Feb 2009

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

jackyerror 2 posts Joined 10/10
11 Oct 2010

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

robpaller 159 posts Joined 05/09
12 Oct 2010

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
;

ThomasNguyen 30 posts Joined 04/09
14 Oct 2010

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;

varshajoshi 5 posts Joined 05/12
07 Jun 2012

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
 

feinholz 1234 posts Joined 05/08
07 Jun 2012

Try:

APPLY INTO OPERATOR

instead of

LOAD INTO OPERATOR

 

--SteveF

You must sign in to leave a comment.