Fast Export - Fast Load issue

[Karam]

I am trying to do bulk table transfer from production to test box using FAST EXPORT and FAST LOAD. Export is ok with data saved on unix files but while loading data into test box , the null values of the source table are getting displayed as blank for CHAR and 0 for integer.
I'm using RECODE mode ( in FAST EXPORT) and it is working fine when no NULL values are present in a table. On changing the mode to INDICATOR (BOTH in FAST EXPORT AND FAST LOAD) , got the error - The length of: COLUMN1in row: 1 was greater than defined.
Defined: 240, Received: 25409

Here are the scripts. Kindly help.

------------------------------------------------
FAST EXPORT SCRIPT
------------------------------------------------

.LOGTABLE LOGDATABASENAME.LOGTABLENAME;
.LOGON PRODDB/USERID,PASSWORD;

DATABASE DATABASENAME

.BEGIN EXPORT ;

.EXPORT OUTFILE UNIX DIRECTORY LOCATION/FILENAME FORMAT FASTLOAD MODE RECORD;

LOCKING DATABASENAME.TABLENAME FOR ACCESS
SEL * FROM DATABASENAME.TABLENAME ;

.END EXPORT ;

.LOGOFF;

------------------------------------------------
FAST EXPORT SCRIPT
------------------------------------------------
ERRLIMIT 100000000;
TENACITY 4;
SESSIONS 4;
SLEEP 6;

.LOGON TESTDB/USERID,PASSWORD;

DROP TABLE LOGDATABASENAME.ET_TABLENAME;
DROP TABLE LOGDATABASENAME.UV_TABLENAME;

DEFINE FILE= UNIX DIRECTORY LOCATION/FILENAME;

SHOW;

BEGIN LOADING DATABASENAME.TABLENAME
ERRORFILES LOGDATABASENAME.ET_TABLENAME
,LOGDATABASENAME.UV_TABLENAME

CHECKPOINT 0 ;

INS DATABASENAME.TABLENAME.*;

.END LOADING;

.LOGOFF;

[dnoeth]

Hi Karam,
i don't see indicator mode in your scripts?

FastExport:
.EXPORT ...
MODE INDICATOR -- default in FastExport
FORMAT FASTLOAD -- default in FastExport

FastLoad:
.BEGIN LOADING ... INDICATORS; -- NO default in FastLoad
.SET RECORD FORMATTED; -- default in FastLoad

Dieter

[Karam]

Hello Dieter

Thanks for the reply. I mentioned RECODE mode in my 1st posting as it is causing issues with source table NULLs by displaying 0 for integer and blank for character in target table ( same DDL, different box)
Also, as earlier stated , on using INDICATOR mode in FE/FL scripts , i am getting this error:
Bad file or data definition.
**** 11:32:45 The length of: COLUMN_N in row: 1 was greater than defined.
Defined: 240, Received: 25409
Please find script using INDICATOR mode below:

------------------------------------------------
FAST EXPORT SCRIPT
------------------------------------------------

.LOGTABLE LOGDATABASENAME.LOGTABLENAME;
.LOGON PRODDB/USERID,PASSWORD;

DATABASE DATABASENAME

.BEGIN EXPORT ;

.EXPORT OUTFILE UNIX DIRECTORY LOCATION/FILENAME FORMAT FASTLOAD MODE INDICATOR;

LOCKING DATABASENAME.TABLENAME FOR ACCESS
SEL * FROM DATABASENAME.TABLENAME ;

.END EXPORT ;

.LOGOFF;

------------------------------------------------
FAST EXPORT SCRIPT
------------------------------------------------
ERRLIMIT 1000;
TENACITY 4;
SESSIONS 4;
SLEEP 6;

.LOGON TESTDB/USERID,PASSWORD;

DROP TABLE LOGDATABASENAME.ET_TABLENAME;
DROP TABLE LOGDATABASENAME.UV_TABLENAME;

.SET RECORD FORMATTED; /* using it or not , iam getting same error as mentioned above */

DEFINE FILE= UNIX DIRECTORY LOCATION/FILENAME;

SHOW;

BEGIN LOADING DATABASENAME.TABLENAME
ERRORFILES LOGDATABASENAME.ET_TABLENAME
,LOGDATABASENAME.UV_TABLENAME

CHECKPOINT 0
INDICATORS;

INS DATABASENAME.TABLENAME.*;

.END LOADING;

.LOGOFF;

[dnoeth]

Hi Karam,
strange, the scripts use matching layout definitions.

Just a shot in the dark:
Could it be caused by different character set definitions?
Maybe set in a configuration files or as a runtime parameter?

Did you already look at the actual data in the file?

Dieter

[Karam]

Hello Dierter

Got the resolution.Not in mentioned scripts but during testing i began using .DATEFORM ANSIDATE command in FAST LOAD script. On reading this from manual:

"When you use the ANSIDATE specification, you must convert ANSI/SQL DateTime data types to fixed-length CHAR data types when specifying the column/field names in the FastLoad DEFINE command."

Therefore i remove the command from the script (with INDICATOR mode) and it worked.

Many thanks for your prompt assitance.

[Pushkar_B]

Hello Karam,

I am facing the same problem. I didn't understand your resolution could you please explain the steps again.

P.S. I am using date datatype in my table but in the DEFINE section I have created the variables as VARCHAR(21) this worked when I used FL alone.. how ever when I did a FE and then tried to use this data file to FL then it gave the same error you mentioned about length of COLUMN.

Please let me know. Thanks