MODE and FORMAT - TD Utilities.

Database
Enthusiast

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 ? 

14 REPLIES
Enthusiast

Re: MODE and FORMAT - TD Utilities.

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.

Senior Apprentice

Re: MODE and FORMAT - TD Utilities.

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

Enthusiast

Re: MODE and FORMAT - TD Utilities.

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

Enthusiast

Re: MODE and FORMAT - TD Utilities.

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

Senior Apprentice

Re: MODE and FORMAT - TD Utilities.

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. 

Enthusiast

Re: MODE and FORMAT - TD Utilities.

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. :-)

Senior Apprentice

Re: MODE and FORMAT - TD Utilities.

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.

Enthusiast

Re: MODE and FORMAT - TD Utilities.

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

Enthusiast

Re: MODE and FORMAT - TD Utilities.

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 ;