Can't load data using TPUMP. Error: Field overflow

Tools & Utilities
Enthusiast

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

2 REPLIES
Enthusiast

Re: Can't load data using TPUMP. Error: Field overflow

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?

Enthusiast

Re: Can't load data using TPUMP. Error: Field overflow

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