All Forums Database
Raj kumar T 7 posts Joined 04/12
03 Jan 2014
MODE and FORMAT - TD Utilities.

Hi Folks, 
Can anyone explain the significance of MODE and FORMAT in Utilities like Fast Export and Multi load ?
And what are the various MODE and FORMAT are avilable at this end ?
I have seen some scenario's where RECORD has been used as MODE.
And Fastload, Text, Vartext has been used as FORMAT. What is the difference between all these various format and mode ? 

Thanks, Raj kumar T
Raja_KT 1246 posts Joined 07/09
04 Jan 2014

In fastexport you can see the syntax as from manual:

 

FORMAT  FASTLOAD

               BINARY

               TEXT

               UNFORMAT

MODE     INDICATOR

               RECORD

 

The FORMAT options apply only to UNIX and Windows platforms.

The default, if a FORMAT option is not specified, is FASTLOAD.

 

The INDICATOR mode is not recommended when using TEXT record

format. Please use UNFORMATTED record format instead.

 

I suggest you go through the manual to get a clear picture.

 

Yes, there are some commands which are same in other utilities.

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

dnoeth 4628 posts Joined 11/04
04 Jan 2014

There's a manual for each Load Utility which describes FORMAT and MODE in full detail.

Dieter

Raj kumar T 7 posts Joined 04/12
04 Jan 2014

Thanks a bunch for your response raja and dnoeth.
I had a chance to look at those manuals. and I found
The default FORMAT is FASTLOAD.
BINARY format is a two-byte integer, followed by data.
TEXT format is an arbitrary number of bytes followed by an end-of-record marker.
UNFORMAT format is exactly as received from CLIv2 without any client modifications.
Here comes my next question, My table has VARCHAR, SMALLINT, DATE and INTEGER as data types. When I try to execute my fast export script with MODE RECORD FORMAT TEXT. My output file consumes unidentified things like "Ã^]{^C^@^@^@^@D".
I could understand if my table has only char data type FORMAT TEXT will work fine.
But what's the correct MODE and FORMAT for table which is having different data types ?
The above question is related to mode and format, So I've posted here. If needed I can post as a new topic. Please advice.
 
Thanks,
Raj kumar T

Thanks,
Raj kumar T

Raja_KT 1246 posts Joined 07/09
04 Jan 2014

Hi Raj,

You have not shared your script. You have not shown the data.

Have you tried with FORMAT FASTLOAD or even TEXT,VARTEXT MODE RECORD. Also you can try thus:

 

select CAST( field1 || '|' ||field2 || '|' ||

 

(CASE WHEN field3 IS NULL THEN '?' ELSE field3) || '|' || 

 

......

 

CAST(trim(CAST(fieldn  AS CHAR(10))))....

 

AS CHAR( ) (TITLE '')

 

from table1------ casting the whole thing

 

The manual gives us many options.

 

The manual says:

 

 

 

TEXT format should only be specified for character data. Do not

 

specify TEXT format for binary data, such as, INTEGER, BYTEINT,

 

PERIOD, and other binary data. Depending on the actual byte values of

 

the binary data, unexpected results may occur.

 

 

Cheers,

 

Raja

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

dnoeth 4628 posts Joined 11/04
04 Jan 2014

Hi Raj,
the "unidentified things" are binary data like INT, which are no readable text.
You can simply CAST any datatype to a CHAR to be able to use TEXT format. 
But you didn't tell how the data should look like in your exported file.
If it should be delimited text i would recommed switching to TPT, in the latest versions there's a VARTEXT export without doing any typecasts. 

Dieter

Raj kumar T 7 posts Joined 04/12
06 Jan 2014

 

Hi Raja and Dnoeth,

 

Dnoeth as you said,

"You can simply CAST any datatype to a CHAR to be able to use TEXT format. "  

 

I've decided to CAST everything into CHAR.

 

As Raja said ------ casting the whole thing into CHAR.

 

Based on your suggestions I've come up something like below.

 

My fast export Script : 

 

.LOGON statement

 

.LOGTABLE CUST_ID_RXT_LOG;

 

.SYSTEM 'rm -f "cust_id_rxt.dat"';

 

.BEGIN EXPORT SESSIONS 10;

 

.EXPORT OUTFILE 'cust_id_rxt.dat'

 

MODE RECORD

 

FORMAT TEXT;

 

SELECT CAST(

 TRIM(CAST(CUST_ID AS CHAR(10))) || '|' ||

 TRIM(CAST(CAST(CUST_PRTY_ID AS INTEGER) AS CHAR(18))) || '|' ||

 TRIM(CAST(CUST_SRC_SYS_CD AS CHAR(20))) || '|' ||

 TRIM(CAST(EFF_BGN_DT AS CHAR(40))) || '|' ||

 TRIM(CAST(EFF_END_DT AS CHAR(40))) || '|' ||

 TRIM(CAST(SRC_SYS_TYP_CD AS CHAR(20))) AS CHAR(50))

FROM CUST_ID_RXT;

 

.END EXPORT;

 

.LOGOFF;

 

Expected Result(sample):

 

0203300000|58400222|68|2013-11-25|9999-12-31|9

12172371|54604245|21|2013-11-25|9999-12-31|9

 

Question:

 

Since I've been setting my char length(whole thing) to CHAR(50). In my output some spaces are added in the end.

 

0203300000|58400222|68|2013-11-25|9999-12-31|9(4 spaces added)

12172371|54604245|21|2013-11-25|9999-12-31|9(5 spaces added). 

 

When I tried to trim the char(50). Output would be something like below. Unidentified things added in the begining.

 

.^@0203300000|58400222|68|2013-11-25|9999-12-31|9

,^@12172371|54604245|21|2013-11-25|9999-12-31|9

 

Hope I explained clearly my scenario, I want my output to be without any space or special char. and it can be delimted file.

 

Provide your suggestions. :-)

 

 

Thanks,
Raj kumar T

dnoeth 4628 posts Joined 11/04
06 Jan 2014

Hi Raj,
again, those "unidentified things" are two bytes indicating the length of the resulting VarChar. This is a well-known problem with FExp, there's no built-in way to get delimited data without additional steps, usually there's an OUTMOD (or Unix script) simply stripping of the first two bytes.
For a small result set you can utilize to BTEQ (which never returns trailing blanks in REORT mode) or better switch to TPT.

Dieter

Raja_KT 1246 posts Joined 07/09
06 Jan 2014

you can use vi editor to search and replace  unwanted characters --s/ //g

 or you can use sed -e 's/ //g'

 

Also, I hope you are trying tpt:

 

 

Please check the syntax.

DEFINE JOB EXPORT_DELIMITED

DESCRIPTION 'Export TD table to a delimited file'

(

DEFINE SCHEMA SOURCE_SCHEMA

(

CUST_ID      VARCHAR(10),

CUST_PRTY_ID       VARCHAR(18),

CUST_SRC_SYS_CD    VARCHAR(20),

EFF_BGN_DT         VARCHAR(40),

EFF_END_DT         VARCHAR(40),

SRC_SYS_TYP_CD     VARCHAR(40)

);

DEFINE OPERATOR SQL_SELECTOR

TYPE SELECTOR

SCHEMA SOURCE_SCHEMA

ATTRIBUTES

(

VARCHAR PrivateLogName = 'abc_log',

VARCHAR TdpId = 'mmmmmmmm',

VARCHAR UserName = 'xxxxxx',

VARCHAR UserPassword = 'xxxxxxx',

VARCHAR SelectStmt = 'SELECT CAST(

 

 TRIM(CAST(CUST_ID AS CHAR(10))) || '|' ||

 TRIM(CAST(CAST(CUST_PRTY_ID AS INTEGER) AS CHAR(18))) || '|' ||

 TRIM(CAST(CUST_SRC_SYS_CD AS CHAR(20))) || '|' ||

 TRIM(CAST(EFF_BGN_DT AS CHAR(40))) || '|' ||

 TRIM(CAST(EFF_END_DT AS CHAR(40))) || '|' ||

 TRIM(CAST(SRC_SYS_TYP_CD AS CHAR(20)))

FROM CUST_ID_RXT;',

VARCHAR ReportModeOn='Yes'

);

DEFINE OPERATOR FILE_WRITER

TYPE DATACONNECTOR CONSUMER

SCHEMA *

ATTRIBUTES

(

VARCHAR PrivateLogName = 'def_log',

VARCHAR DirectoryPath = '/home',

VARCHAR FileName = 'test.csv',

VARCHAR Format = 'DELIMITED',

VARCHAR OpenMode = 'Write',

VARCHAR TextDelimiter = '|',

VARCHAR TRACELEVEL='ALL'

);

APPLY TO OPERATOR (FILE_WRITER)

 

SELECT * FROM OPERATOR (SQL_SELECTOR);

);

 

Cheers,

Raja

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

Raj kumar T 7 posts Joined 04/12
06 Jan 2014

Thanks dnoeth !!! For time being I would go with stripping of the first two bytes using simple unix command. I will explore TPT later.
But, I've read in the manual stating that, 
The default FORMAT is in a UNIX or LAN environment. FASTLOAD format has a two-byte integer, followed by the data, followed by an end-of-record marker. It is called because the data is exported in a format ready for FASTLOAD.
So I've tried Fast export with format fastload using that output file I've passed that as input file to fastload script. I'm getting error like Unexpected data format. Will it be a one another drawback ? Find the below scripts i've used.
Fast Export script:
.EXPORT OUTFILE 'cust_id_rxt_tst.dat'
MODE RECORD

FORMAT FASTLOAD;
 

.EXPORT OUTFILE 'cust_id_rxt.dat'

 

MODE RECORD

FORMAT FASTLOAD;

 

SELECT 

 CUST_ID

,CUST_PRTY_ID

,CUST_SRC_SYS_CD

,EFF_BGN_DT

,EFF_END_DT 

,SRC_SYS_TYP_CD 

FROM CUST_ID_RXT;

 

.END EXPORT;

 

Output :

^R^@02033000000666307CÃ^]{^C^@^@^@^@D^@uB^Q^@ÿÃÃ^D      ^@^H^@51686304tì<82>^C^@^@^@^@^T^@uB^Q^@ÿÃÃ^D   ^@^H^@22495665<

 

Fastload Script:

 

FILE=cust_id_rxt_tst.dat;

 

SHOW;

 

BEGIN LOADING CUST_ID_RXT_LD

ERRORFILES CUST_ID_RXT_CPERR,CUST_ID_RXT_CPUV

;

 

INSERT INTO CUST_ID_RXT_LD

(

         CUST_ID

        ,CUST_PRTY_ID

        ,CUST_SRC_SYS_CD

        ,EFF_BGN_DT

        ,EFF_END_DT

        ,SRC_SYS_TYP_CD

 

)

values

(

         :CUST_ID

        ,:CUST_PRTY_ID

        ,:CUST_SRC_SYS_CD

        ,:EFF_BGN_DT               (FORMAT'YYYY-MM-DD')

        ,:EFF_END_DT               (FORMAT'YYYY-MM-DD')

        ,:SRC_SYS_TYP_CD

);

 

END LOADING ;

 

Thanks,
Raj kumar T

Raj kumar T 7 posts Joined 04/12
06 Jan 2014

Hi Raja,
I'm really appreciating your help for sharing TPT method, Currently I just want to explore Fast Export, Fast Load, Multiload TD Utilities. 
As per my previous comment, Will it possible to use a output file of a fast export script as input file to a fastload script ?

Thanks,
Raj kumar T

Raja_KT 1246 posts Joined 07/09
06 Jan 2014

Very much you can do.  Also try to cast with the exact number of bytes for the entire  columns when you cast for the entire select and see. 
Cheers,
 

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

Raj kumar T 7 posts Joined 04/12
07 Jan 2014

Thanks Raja and dnoeth. You guys have been really helpful.. !!!

Thanks,
Raj kumar T

Fred 1096 posts Joined 08/04
08 Jan 2014

RE: Using output of FastExport as input to FastLoad
If there is a chance you have NULL values, use INDICATOR mode (the default) on the export and specify INDICATORS (NOT the default) on the BEGIN LOADING.

SmarakDas 51 posts Joined 02/12
16 May 2014

Thanks to everyone, especially Raja & Dieter for this post. Very good in terms of FastExport.
 
Thanks Fred for clearing this situation, where the INDICATOR Mode must be used for FastExport & FastLoad whenever NULL Values have encountered.
 
Also, Raj Kumar...I use the provided script for exporting & loading using FastExport & FastLoad using their default FORMAT (I use pipe functionality to execute the Export & Load simultaneously without intermediate file creation):

FastExport:

.LOGTABLE <DatabaseName>.<TableName>;  
.LOGON Server/UserName,PassWord;  
.BEGIN EXPORT SESSIONS 4;  
.EXPORT OUTFILE/tmp/Temporarypipe FORMAT FASTLOAD MODE INDICATOR;  
SELECT * FROM Source_Database.Target_Table;  
.END EXPORT;  
.LOGOFF; 



FastLoad:

SESSIONS 10;  
.LOGON Server/UserName,PassWord;  
BEGIN LOADING Target_Database.Target_Table ERRORFILES Target_Database.Target_Table_ERROR1,Target_Database.Target_Table_ERROR2 INDICATORS;  
SET RECORD FORMATTED;  
DEFINE FILE =/tmp/Temporarypipe;  
INSERT Target_Database.Target_Table.*;  
.END LOADING;  
.LOGOFF;  
.QUIT;

 

You must sign in to leave a comment.