Fast export and mload in vartext mode - Data item too large for field issue

Tools
Enthusiast

Fast export and mload in vartext mode - Data item too large for field issue

Greetings experts,

I am facing the following issue in TD Demo version 13.0 , windows 7

Scenario:  Fast exporting from a table samples.itemppi_bkp in "mode record format text" (vartext separated by ,) and then using this file as source for mloading into target table samples.itemppi_wodate.  

After completion of fast export, and then while running MLOAD, the job is failing with the following error 

**** 02:16:06 UTY4016 Data item too large for field L_LINESTATUS in vartext record number 1.

**** 02:16:06 UTY1803 Import processing statistics

When I viewed the fast exported file through HXD editor, could see 2 entirely blank lines.  Can you please explain how to overcome this.  Gone through other posts, and could find that TPT could easily export data in vartext form, but I am trying them through fastexport and multiload.

Here are the details:

Source / Target table structure:

CREATE SET TABLE samples.itemppi_wodate 

     (

      L_ORDERKEY INTEGER,

      L_PARTKEY INTEGER,

      L_QUANTITY DECIMAL(15,2),

      L_LINESTATUS CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC)

PRIMARY INDEX ( L_ORDERKEY );

Fast export script:

.logtable LT_itemppi_wodate;

.logon localtd/tduser,tduser;

database samples;

.begin export sessions 12;

.export outfile "G:\Users\cheeli\Desktop\fexp_out\fexp_itemppi_vartext.txt" mode record format text;

select CAST (trim(CAST (L_ORDERKEY AS CHAR(12))) || ',' || trim(CAST(L_PARTKEY AS CHAR(12))) || ',' || trim(CAST(L_QUANTITY AS CHAR(20))) || ',' ||
trim(CAST(L_LINESTATUS AS CHAR(1))) AS CHAR(48)) from
samples.itemppi_bkp where L_ORDERKEY=225;

.end export;

Exported file data:

225,1352,34.00,O                                

Following is the output of the file when opened in HXD editor.

32 32 35 2C 31 33 35 32 2C 33 34 2E 30 30 2C 4F 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 0D 0A

Mload script:

.LOGTABLE SAMPLES.ML_ITEMPPI_WODATE;
.logon localtd/tduser,tduser;

.begin import mload tables samples.itemppi_wodate;
/*errortables samples.ET_ITEMPPI SAMPLES.UV_ITEMPPI SAMPLES.WT_ITEMPPI*/;

.LAYOUT DATA_LAYOUT;

.field L_ORDERKEY * varchar(12);
.field L_PARTKEY * varchar(12);
.field L_QUANTITY * varchar(20);
.field L_LINESTATUS * varCHAR(1);

.dml label insert_itemppi;
insert into samples.itemppi_wodate values (:L_ORDERKEY, :L_PARTKEY, :L_QUANTITY, :L_LINESTATUS);

.import infile "G:\Users\cheeli\Desktop\fexp_out\fexp_itemppi_vartext.txt"
format vartext ','
layout data_layout
apply insert_itemppi;
.end mload;

.logoff;

Mload log:

     ========================================================================
= =
= MultiLoad Utility Release MLOD.13.00.00.005 =
= Platform WIN32 =
= =
========================================================================
= =
= Copyright 1990-2009 Teradata Corporation. ALL RIGHTS RESERVED. =
= =
========================================================================
**** 02:16:03 UTY2411 Processing start date: THU FEB 27, 2014
========================================================================
= =
= Logon/Connection =
= =
========================================================================
0001 .LOGTABLE SAMPLES.ML_ITEMPPI_WODATE;
0002 .logon localtd/tduser,;
**** 02:16:03 UTY8400 Teradata Database Release: 13.00.00.12
**** 02:16:03 UTY8400 Teradata Database Version: 13.00.00.12
**** 02:16:03 UTY8400 Default character set: ASCII
**** 02:16:03 UTY8400 Current RDBMS has interval support
**** 02:16:03 UTY8400 Current RDBMS has UDT support
**** 02:16:03 UTY8400 Maximum supported buffer size: 1M
**** 02:16:03 UTY8400 Data Encryption supported by RDBMS server
**** 02:16:03 UTY6211 A successful connect was made to the RDBMS.
**** 02:16:03 UTY6217 Logtable 'SAMPLES.ML_ITEMPPI_WODATE' has been created.
========================================================================
= =
= Processing Control Statements =
= =
========================================================================

0003 .begin import mload tables samples.itemppi_wodate;
========================================================================
= =
= Processing MultiLoad Statements =
= =
========================================================================
0004 /*errortables samples.ET_ITEMPPI SAMPLES.UV_ITEMPPI SAMPLES.WT_ITEMPPI*/;

0005 .LAYOUT DATA_LAYOUT;

0006 .field L_ORDERKEY * varchar(12);
0007 .field L_PARTKEY * varchar(12);
0008 .field L_QUANTITY * varchar(20);
0009 .field L_LINESTATUS * varCHAR(1);

0010 .dml label insert_itemppi;
0011 insert into samples.itemppi_wodate values (:L_ORDERKEY, :L_PARTKEY, :L_QUANTITY,
:L_LINESTATUS);

0012 .import infile "G:\Users\cheeli\Desktop\fexp_out\fexp_itemppi_vartext.txt"
format vartext ','
layout data_layout
apply insert_itemppi;
0013 .end mload;
========================================================================
= =
= MultiLoad Initial Phase =
= =
========================================================================
**** 02:16:03 UTY0829 Options in effect for this MultiLoad import task:
. Sessions: One session per available amp.
. Checkpoint: 15 minute(s).
. Tenacity: 4 hour limit to successfully connect load sessions.
. Errlimit: No limit in effect.
. AmpCheck: In effect for apply phase transitions.
**** 02:16:03 UTY0817 MultiLoad submitting the following request:
Select NULL from SAMPLES.ML_ITEMPPI_WODATE where (LogType = 125) and (Seq =
1) and (MloadSeq = 0);
**** 02:16:03 UTY0817 MultiLoad submitting the following request:
Select NULL from SAMPLES.ML_ITEMPPI_WODATE where (LogType = 120) and (Seq =
1);
**** 02:16:04 UTY0815 MLOAD session(s) connected: 2.
**** 02:16:04 UTY0817 MultiLoad submitting the following request:
BEGIN MLOAD samples.itemppi_wodate WITH INTERVAL;
**** 02:16:04 UTY0817 MultiLoad submitting the following request:
INS SAMPLES.ML_ITEMPPI_WODATE (LogType, Seq, MLoadSeq)VALUES(130, 1, 10);
**** 02:16:04 UTY0817 MultiLoad submitting the following request:
MLOAD samples.itemppi_wodate with samples.WT_itemppi_wodate errortables
samples.ET_itemppi_wodate, samples.UV_itemppi_wodate;
========================================================================
= =
= MultiLoad DML Transaction Phase =
= =
========================================================================
**** 02:16:04 UTY0817 MultiLoad submitting the following request:
BT;
**** 02:16:04 UTY0817 MultiLoad submitting the following request:
USING L_ORDERKEY(VARCHAR(12)), L_PARTKEY(VARCHAR(12)),
L_QUANTITY(VARCHAR(20)), L_LINESTATUS(VARCHAR(1)) insert into
samples.itemppi_wodate values (:L_ORDERKEY, :L_PARTKEY, :L_QUANTITY,
:L_LINESTATUS);
**** 02:16:04 UTY0817 MultiLoad submitting the following request:
INS SAMPLES.ML_ITEMPPI_WODATE (LogType, Seq, MLoadSeq)VALUES(130, 1, 20);
**** 02:16:04 UTY0817 MultiLoad submitting the following request:
ET;
========================================================================
= =
= MultiLoad Acquisition Phase =
= =
========================================================================
**** 02:16:04 UTY0817 MultiLoad submitting the following request:
BEGIN TRANSACTION;
**** 02:16:04 UTY0817 MultiLoad submitting the following request:
CHECKPOINT LOADING INTERVAL 0;
**** 02:16:04 UTY0817 MultiLoad submitting the following request:
CHECKPOINT LOADING INTERVAL 0;
**** 02:16:05 UTY0817 MultiLoad submitting the following request:
CHECKPOINT LOADING INTERVAL 0;
**** 02:16:06 UTY0817 MultiLoad submitting the following request:
USING Ckpt(VARBYTE(1024)) INSERT SAMPLES.ML_ITEMPPI_WODATE (Logtype, Seq,
MLoadSeq, MLoadImpSeq, MLoadSrcSeq, MiscInt1,MiscInt2,MiscInt3,MiscInt4,
MiscInt5,MiscInt6,MiscInt7,MiscInt8,CkptInterval,MLoadCkpt) VALUES (110, 1,
0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, :Ckpt);
**** 02:16:06 UTY4016 Data item too large for field L_LINESTATUS in vartext record number 1.
**** 02:16:06 UTY1803 Import processing statistics
. IMPORT 1 Total thus far
. ========= ==============
Candidate records considered:........ 0....... 0
Apply conditions satisfied:.......... 0....... 0
Candidate records not applied:....... 0....... 0
Candidate records rejected:.......... 0....... 0
========================================================================
= =
= Logoff/Disconnect =
= =
========================================================================
**** 02:16:08 UTY6212 A successful disconnect was made from the RDBMS.
**** 02:16:08 UTY2410 Total processor time used = '0.483603 Seconds'
. Start : 02:16:03 - THU FEB 27, 2014
. End : 02:16:08 - THU FEB 27, 2014
. Highest return code encountered = '12'.


Awaiting your valuable responses.

5 REPLIES
Teradata Employee

Re: Fast export and mload in vartext mode - Data item too large for field issue

Hi Cheeli,

Your fastexported data file is not in VARTEXT format, so your mload job failed.

Here is a matching FastExport script and corresponding MultiLoad script.

FastExport:

.LOGTABLE FELOG1124;

.LOGON <tdpid>/<userid>, <password>;

DROP TABLE TBL01124;

CREATE SET TABLE TBL01124

(

L_ORDERKEY INTEGER,

L_PARTKEY INTEGER,

L_QUANTITY DECIMAL(15,2),

L_LINESTATUS CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC)

PRIMARY INDEX ( L_ORDERKEY );

ins TBL01124(1,1,1234.12, 'a');

ins TBL01124(2,2,1234.12, 'b');

.BEGIN EXPORT;

.EXPORT OUTFILE cheeli.dat;

select * from TBL01124;

.END EXPORT;

.logoff;

.QUIT;

MultiLoad:

.LOGON <tdpid>/<userid>, <password>;

 DROP TABLE MLTBL01124;

 DROP TABLE WT_MLTBL01124;

 DROP TABLE ET_MLTBL01124;

CREATE SET TABLE MLTBL01124

(

L_ORDERKEY INTEGER,

L_PARTKEY INTEGER,

L_QUANTITY DECIMAL(15,2),

L_LINESTATUS CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC)

PRIMARY INDEX ( L_ORDERKEY );

.BEGIN MLOAD TABLES MLTBL01124;

.LAYOUT LAY1124 indicators;

.field L_ORDERKEY * integer;

.field L_PARTKEY * integer;

.field L_QUANTITY * decimal(15,2);

.field L_LINESTATUS * CHAR(1);

.DML LABEL dmlA;

insert into MLTBL01124 values

(:L_ORDERKEY, :L_PARTKEY, :L_QUANTITY, :L_LINESTATUS);

 .IMPORT INFILE cheeli.dat  FORMAT FASTLOAD LAYOUT LAY1124 APPLY dmlA;

.END MLOAD;

.LOGOFF;

.QUIT;

Thanks!

--Ivy.

Enthusiast

Re: Fast export and mload in vartext mode - Data item too large for field issue

Hi Ivy/All,

Thank you for your input. This one worked with fastload format.

I am looking for some script that does export the data in delimited fashion through fast export utility and load into TD using Mload with format of vartext. 

Can you please share the script for it.

Also, for a fastexport script with TEXT format, when I cast the integer values in the select statement of the fast export script to CHAR(10), there is truncation of values.  When I casted to CHAR(12) the values are not truncated. As the max range for integers is 2,147,483,647   I expected CHAR(10) to accommodate integer data type values. 

source table integer data type (col1) value: 235

fast export select statement with cast on it:

cast (col1 as char(10))  results in 23  -- Are the integer values right aligned and does sign of the values also take a place, so that I should cast for integer values to cast(11) to avoid truncation.

Can you please shed some light on this.

Thank you for your time on this.

Enthusiast

Re: Fast export and mload in vartext mode - Data item too large for field issue

Hi All,

Modified the input select statement (of fast export) to include TRIM there by eliminating the blanks as below

1

2

3

select trim(CAST (trim(CAST (L_ORDERKEY AS CHAR(12))) || ',' || trim(CAST(L_PARTKEY AS CHAR(12))) || ',' || trim(CAST(L_QUANTITY AS CHAR(20))) || ',' ||

trim(CAST(L_LINESTATUS AS CHAR(1))) AS CHAR(48))) from

samples.itemppi_bkp where L_ORDERKEY=225;




Now could see that the trailing blanks are deleted.  But there are some other characters in starting line of the file as below (may be due to the trim I guess, checked through type() resulted in varchar(48), which may represent the 2 byte length of the record)

Actual output from fast exported output file:

 225,1352,34.00,O             --  there are some junk characters which could not be pasted

Following is the data of the file when viewed in hxd editor.

..225,1352,34.00
,O..

Following is the equivalent values of the above data in hxd editor

10 00 32 32 35 2C 31 33 35 32 2C 33 34 2E 30 30 2C 4F 0D 0A

Now ran the Mload job by modifying the layout as below (ran both with and without filler command)

.LAYOUT DATA_LAYOUT;

.filler abc * varchar(2);

.field L_ORDERKEY * varchar(12);

.field L_PARTKEY * varchar(12); 

.field L_QUANTITY * varchar(20); 

.field L_LINESTATUS * varCHAR(4); 

Now the job is failing with the other error as below in acquisition phase.

**** 20:36:22 UTY0817 MultiLoad submitting the following request:
USING Ckpt(VARBYTE(1024)) INSERT SAMPLES.ML_ITEMPPI_WODATE (Logtype, Seq,
MLoadSeq, MLoadImpSeq, MLoadSrcSeq, MiscInt1,MiscInt2,MiscInt3,MiscInt4,
MiscInt5,MiscInt6,MiscInt7,MiscInt8,CkptInterval,MLoadCkpt) VALUES (110, 1,
0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, :Ckpt);
**** 20:36:22 UTY4017 Not enough data in vartext record number 1.
**** 20:36:22 UTY1803 Import processing statistics

. IMPORT 1 Total thus far
. ========= ==============
Candidate records considered:........ 0....... 0
Apply conditions satisfied:.......... 0....... 0
Candidate records not applied:....... 0....... 0
Candidate records rejected:.......... 0....... 0

Can you please help me how to get rid of this error.

Junior Contributor

Re: Fast export and mload in vartext mode - Data item too large for field issue

Hi Cheeli,

yes, the first two bytes indicate the length of the VarChar, you need an OutMod or a Unix shell command to strip them off.

The max range of an INT is 10 digits, but there's a sign, too. So you need 11 characters.

Teradata Employee

Re: Fast export and mload in vartext mode - Data item too large for field issue

You should consider switching to TPT.

In 14.10 TPT supports the ability to write out data (retrieved from Teradata or any other ODBC compliant data source) to delimited fashion without the need to specially code the SELECT statement.

-- SteveF