Fastload-ing the FastExported Data.???

Tools & Utilities
Enthusiast

Fastload-ing the FastExported Data.???

Hi Everyone,

I ve been working with the load utilities, Fastexport and Fastload.
I could easily FastExport the table data to flat files using MODE INDICATOR and FORMAT FASTLOAD. But, when I try to load the same data file(exported using FastExport with INDICATOR MORE and FASTLOAD FORMAT), the fastload script fails, telling 'invalid data type'.
Below are the scripts and the execution output also.
Could u pls go thru the scripts and clear my doubt, what exactly the way to load the data using fastload.

NOTE: my requirement is to unload the data from the DB using FastExport, keep it somewhere safely, and reload the same data(using Fastload), whenever required at later point of time.

----------------------
Request Text
CREATE SET TABLE sarathy_db.emp ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL
(
emp_no INTEGER NOT NULL,
emp_Name VARCHAR(25) CHARACTER SET LATIN NOT CASESPECIFIC,
emp_street VARCHAR(25) CHARACTER SET LATIN NOT CASESPECIFIC,
emp_city VARCHAR(25) CHARACTER SET LATIN NOT CASESPECIFIC)
UNIQUE PRIMARY INDEX ( emp_no );

----------------------

---------------------------------------------------------
fast export script:
--------------------------------------------------------
.LOGTABLE sarathy_db.fexp_log_tb;

.LOGON demotdat/sarathy_user,******;

.BEGIN EXPORT SESSIONS 3;

.EXPORT OUTFILE c:\td\out\fexp_emp.dat
MODE INDICATOR
FORMAT FASTLOAD;

SELECT * FROM sarathy_db.emp;

.END EXPORT;

.LOGOFF;

--------------------------------------------------------------
fast load script:
--------------------------------------------------------------
SESSIONS 3;

TENACITY 2;
SLEEP 5;

LOGON demotdat/sarathy_user,********;

SHOW VERSIONS;

/*SET RECORD FORMATTED;*/
/*SET RECORD VARTEXT ",";*/

DEFINE
emp_no (integer),
emp_name (varchar(25)),
emp_street (varchar(25)),
emp_city (varchar(25))
FILE=c:\td\out\fexp_emp.dat;
/*FILE=c:\td\data\emp_data.csv; */

SHOW;

BEGIN LOADING sarathy_db.emp
ERRORFILES sarathy_db.fld_err_tb1,sarathy_db.fld_err_tb2
CHECKPOINT 50;

INSERT INTO sarathy_db.emp VALUES
(
:emp_no,
:emp_name,
:emp_street,
:emp_city
);

END LOADING;

LOGOFF;

--------------------------------------------------------------
output generated with ERROR details
--------------------------------------------------------------

C:\TD\out>fastload < c:\td\bin\emp.fld
===================================================================
= =
= FASTLOAD UTILITY VERSION 07.05.01.000 =
= =
===================================================================

===================================================================
= =
= PLATFORM WIN32 =
= =
===================================================================

**** 16:37:12 Processing starting at: Fri Sep 01 16:37:12 2006

0001 SESSIONS 3;

**** 16:37:12 FDL4866 SESSIONS command accepted

0002 TENACITY 2;

**** 16:37:12 Tenacity Enabled: 2 hour(s)

0003 SLEEP 5;

**** 16:37:12 Sleep Minutes Set: 5 minute(s)

===================================================================
= =
= Logon/Connection =
= =
===================================================================

0004 LOGON demotdat/sarathy_user,

**** 16:37:12 Current CLI or RDBMS allows maximum row size: 64K
**** 16:37:12 Character set for this job: ASCII
**** 16:37:12 Number of FastLoad sessions requested = 3
**** 16:37:12 Number of FastLoad sessions connected = 2
**** 16:37:12 FDL4808 LOGON successful

0005 SHOW VERSIONS;

FastLoad Version 07.05.01.000 for Win 32 running Windows Sockets
FastLoad : 07.05.01.03
FastCmds : 07.04.00.06
FastIO : 07.04.00.01
FastMBCS : 07.05.01.01
FastNtfy : 07.02.00.01
FastPars : 07.04.00.01
FastSQL : 07.05.01.03
FastUtil : 07.05.00.02
PMPROCS : 02.00.00.04
PMRWFMT : 02.00.00.02
PMTRCE : 02.00.00.02
PMMM : 02.00.00.01
PMHEXDMP : 02.00.00.01
PMUNXDSK : 02.00.00.03
CLIV2 : 04.07.00.26
MTDP : 04.07.00.11
MOSIos : 04.07.00.09
MOSIDEP : 04.07.00.05
OSENCRYPT : N/A
OSERR : 04.07.00.03

FastLoad linking date: Sep 13 2002

/*SET RECORD FORMATTED;*/
/*SET RECORD VARTEXT ",";*/

0006 DEFINE
emp_no (integer),
emp_name (varchar(25)),
emp_street (varchar(25)),
emp_city (varchar(25))
FILE=c:\td\out\fexp_emp.dat;

**** 16:37:12 FDL4803 DEFINE statement processed

/*FILE=c:\td\data\emp_data.csv; */

0007 SHOW;

FILE = c:\td\out\fexp_emp.dat
EMP_NO OFFSET = 0 LEN = 4 INTEGER
EMP_NAME OFFSET = 4 LEN = 25 VARCHAR
EMP_STREET OFFSET = 31 LEN = 25 VARCHAR
EMP_CITY OFFSET = 58 LEN = 25 VARCHAR
TOTAL RECORD LENGTH = 85

0008 BEGIN LOADING sarathy_db.emp
ERRORFILES sarathy_db.fld_err_tb1,sarathy_db.fld_err_tb2
CHECKPOINT 50;

**** 16:37:13 Number of AMPs available: 2
**** 16:37:13 BEGIN LOADING COMPLETE

===================================================================
= =
= Insert Phase =
= =
===================================================================

0009 INSERT INTO sarathy_db.emp VALUES
(
:emp_no,
:emp_name,
:emp_street,
:emp_city
);

**** 16:37:13 Number of recs/msg: 765
**** 16:37:13 Starting to send to RDBMS with record 1
**** 16:37:13 Bad file or data definition.
**** 16:37:13 The length of: EMP_NAME in row: 1 was greater than defined.
Defined: 25, Received: 2304
===================================================================
= =
= Logoff/Disconnect =
= =
===================================================================

**** 16:37:13 Logging off all sessions
**** 16:37:13 Total processor time used = '0.0100144 Seconds'
. Start : Fri Sep 01 16:37:12 2006
. End : Fri Sep 01 16:37:13 2006
. Highest return code encountered = '12'.
**** 16:37:13 FastLoad Paused

3 REPLIES
Junior Contributor

Re: Fastload-ing the FastExported Data.???

You export indicator bits, but forgot INDICATORS within the FastLoad:

BEGIN LOADING sarathy_db.emp
ERRORFILES sarathy_db.fld_err_tb1,sarathy_db.fld_err_tb2
INDICATORS
CHECKPOINT 50;

Dieter
Enthusiast

Re: Fastload-ing the FastExported Data.???

you may fexp the result as fixed-length files ,
so you may fastload quickly
or you may fexp the result file as fixed-seperator.

for example
you may fexp as fixed-length
select cast( a as char(10))|| cast( b as char (10))
...
from
tables
where clause

fixed-seperator such as '^'
select a ||'^'|| b||..
from tables
where clause

and then make sure what type of fastload ,
it's will ok
Enthusiast

Re: Fastload-ing the FastExported Data.???

Hi Dieter,

Thanks a lot for your help. I was struggling to solve that problem for days, but no more. I must thank you again, and it was a great learning experience for me.
Looking fwd to meet in more discussions.
--------------------------------------------------------------------------------
Hi Nightmare,

Thanks a lot for your help too. Well, your solution would work perfectly, but the prob is , i ve been dealing with tables of cols in 100s.:-) once again, my thanks for yo help.