All Forums Tools
ss186044 3 posts Joined 03/12
02 May 2012
Can't load data using TPUMP. Error: Field overflow

Hi. I want to export data from one place and import it to other. 

Here is the source data declaration:

--SHOW VIEW  MY_VIEW_WITH_DATA
REPLACE VIEW  MY_VIEW_WITH_DATA AS LOCKING ROW ACCESS( 
SELECT    
	tast.type_id 		--INTEGER NOT NULL
  , tis.type_cd	--CHAR(3) CHARACTER SET UNICODE NOT CASESPECIFIC NOT NULL,
  , tis.inst_cd	--CHAR(6) CHARACTER SET UNICODE NOT CASESPECIFIC,
  , type_desc		--VARCHAR(512) CHARACTER SET UNICODE NOT CASESPECIFIC
  FROM some_other_thing tast
      LEFT JOIN  one_more_thing tis ON tast.system_id = tis.system_id
);

It is a view and I did mention the type of the fields. 

 

I want to export data from that view and import it to this table:

CREATE TABLE MY_TABLE_WITH_DATA,
	NO BEFORE JOURNAL,
	NO AFTER JOURNAL,
	CHECKSUM = DEFAULT,
	DEFAULT MERGEBLOCKRATIO
	(
	 type_id INTEGER NOT NULL,
	type_cd CHAR(3) CHARACTER SET UNICODE NOT CASESPECIFIC, 
	inst_cd CHAR(6) CHARACTER SET UNICODE NOT CASESPECIFIC, 
	type_desc VARCHAR(512) CHARACTER SET UNICODE NOT CASESPECIFIC,
CONSTRAINT ACCT_STTS_ID PRIMARY KEY (type_id ));

Please see the fastexport script which I use to export data:

.LOGTABLE MY_SOURCE_DB.LOG_EXPORT;
.DATEFORM ANSIDATE;
.LOGON teradb/MY_SOURCE_DB_USR,pwd;

.BEGIN EXPORT SESSIONS 4;
	
.EXPORT OUTFILE ..\data\MY_VIEW_WITH_DATA.txt MODE INDICATOR FORMAT FASTLOAD;

SELECT 
type_id, 
type_cd, 
inst_cd, 
type_desc 
FROM MY_SOURCE_DB.MY_VIEW_WITH_DATA; 

.END EXPORT;

.LOGOFF;

I do run it without any warings and I get a file MY_VIEW_WITH_DATA.txt which contains a data. It's in binary format of course, but I can see that integer data does match with a data from view. Seems like it's ok!

 

Now, the second step: I'm trying to import in to other DB into table. Please see the tpump script and log of the operation.

 

.LOGTABLE TARGET_DB.MY_TABLE_WITH_DATA_L;
.DATEFORM ANSIDATE;
.LOGON otherteradb/TARGET_DB_USR,pwd;

.BEGIN LOAD
	ERRLIMIT 5
	CHECKPOINT 60
	SESSIONS 2
	TENACITY 2
	PACK 300	
	ERRORTABLE MY_TABLE_WITH_DATA_E;


.LAYOUT FILELAYOUT INDICATORS;
	.FIELD  type_id * INTEGER;
.FIELD  type_cd * CHAR(3);
.FIELD inst_cd * CHAR(6);
.FIELD type_desc * VARCHAR(512)  ;


.DML LABEL INSERTS;
	 INSERT INTO TARGET_DB.MY_TABLE_WITH_DATA
 (
 type_id, 
type_cd, 
inst_cd, 
type_desc
)
VALUES (
 :type_id, 
 :type_cd, 
 :inst_cd, 
 :type_desc
);

.IMPORT INFILE ..\data\MY_VIEW_WITH_DATA.txt
	FORMAT FASTLOAD
	LAYOUT FILELAYOUT
	APPLY INSERTS;

.END LOAD;
.LOGOFF;
								
**** 08:29:14 UTY6609 Starting to log on sessions...
**** 08:29:15 UTY6610 Logged on 2 sessions.
     ====================================================== ==================
     =                                                                      =
     =          TPump Import(s) Beginning                                   =
     =                                                                      =
     ====================================================== ==================
**** 08:29:15 UTY6630 Options in effect for following TPump Import(s):
     .       Tenacity:    2 hour limit to successfully connect load sessions.
     .   Max Sessions:    2 session(s).
     .   Min Sessions:    1 session(s).
     .     Checkpoint:    60 minute(s).
     .       Errlimit:    5 rejected record(s).
     .   Restart Mode:    ROBUST.
     .  Serialization:    OFF.
     .        Packing:    300 Statements per Request.
     .   StartUp Rate:    UNLIMITED Statements per Minute.
**** 08:29:15 UTY6625 WARNING: Packing has changed to 121 statement(s) per request.
**** 08:29:15 UTY6664 PACK factor was determined by the DATA parcel size limit. Apply: 1,
     Stmt: 1.
**** 08:29:15 UTY8802 WARNING: Rate Monitoring turned off - database TPumpMacro does not
     exist.
**** 08:29:15 UTY6608 Import 1 begins.
**** 08:29:15 UTY4208 Field overflow, 'TYPE_DESC' size = 12336, maximum
     'TYPE_DESC' size = 512, file '..\data\MY_VIEW_WITH_DATA.txt',
     record number '1'.
     ====================================================== ==================
     =                                                                      =
     =          Logoff/Disconnect                                           =
     =                                                                      =
     ====================================================== ==================
**** 08:29:15 UTY6215 The restart log table has NOT been dropped.
**** 08:29:16 UTY6212 A successful disconnect was made from the RDBMS.
**** 08:29:16 UTY2410 Total processor time used = '0.21875 Seconds'
     .       Start : 08:28:56 - THU MAY 03, 2012
     .       End   : 08:29:16 - THU MAY 03, 2012
     .       Highest return code encountered = '12'.

 

What does it mean??? Seems like tpump can't parse exported file... I suppose it's because of UNICODE or stuff like that..

What do i do wrong... ((( Please suggest.

 

**** 08:29:15 UTY4208 Field overflow, 'TYPE_DESC' size = 12336, maximum
     'TYPE_DESC' size = 512, file '..\data\MY_VIEW_WITH_DATA.txt',
     record number '1'.

 

P.S.

All these scripts are autogenerated. I've applied them to export-import data from table to table, they work perfectly. I can't understand whats wrong when I apply them to export view...

ss186044 3 posts Joined 03/12
03 May 2012

Seems like the problem is solved.

I don't have to use key -c UTF8 for fexp and tpump script. This key is a reason of an error. Put it away, run fexp, run tpump and enjoy.

BUT

I do use fexp and tpump with key -c UTF8 to export other tables from other DB. It works fine with the key and doesn't work without it.

 

What's the problem?

 

ss186044 3 posts Joined 03/12
03 May 2012

Strange, It worked only for one view, it doesn't work for others....

You must sign in to leave a comment.