fast export and mload in fastload format issue - error code 2793

Tools
Enthusiast

fast export and mload in fastload format issue - error code 2793

Greetings experts,

I am facing the below issue and need your expertise on the following.

I am fast exporting from the table samples.itemppi_bkp to a file in fastload format.  Using this file as source, inserting data to empty target table samples.itemppi_wodate through Mload utility in fastload format.  All the records are moved into UV table with error code 2,793 (Could see that the values are different in samples.itemppi_bkp and samples.itemppi_wodate.)

Here is the fast export script:

.logtable LT_itemppi_bkp;

.logon localtd/tduser,tduser;

database samples;

.begin export sessions 12;

.export outfile "G:\Users\cheeli\Desktop\fexp_out\fexp_itemppi_fastload.txt" format fastload ;

select L_ORDERKEY , L_PARTKEY , L_QUANTITY , L_LINESTATUS from samples.itemppi_bkp;

.end export;

Here is the 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 * integer;
.field L_PARTKEY * integer;
.field L_QUANTITY * decimal(15,2);
.field L_LINESTATUS * CHAR(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_fastload.txt"
format fastload
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. =
= =
========================================================================
**** 23:46:30 UTY2411 Processing start date: TUE FEB 25, 2014
========================================================================
= =
= Logon/Connection =
= =
========================================================================
0001 .LOGTABLE SAMPLES.ML_ITEMPPI_wodate;
0002 .logon localtd/tduser,;
**** 23:46:30 UTY8400 Teradata Database Release: 13.00.00.12
**** 23:46:30 UTY8400 Teradata Database Version: 13.00.00.12
**** 23:46:30 UTY8400 Default character set: ASCII
**** 23:46:30 UTY8400 Current RDBMS has interval support
**** 23:46:30 UTY8400 Current RDBMS has UDT support
**** 23:46:30 UTY8400 Maximum supported buffer size: 1M
**** 23:46:30 UTY8400 Data Encryption supported by RDBMS server
**** 23:46:30 UTY6211 A successful connect was made to the RDBMS.
**** 23:46:30 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 * integer;
0007 .field L_PARTKEY * integer;
0008 .field L_QUANTITY * decimal(15,2);
0009 .field L_LINESTATUS * CHAR(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_fastload.txt"
format fastload
layout data_layout
apply insert_itemppi;
0013 .end mload;
========================================================================
= =
= MultiLoad Initial Phase =
= =
========================================================================
**** 23:46:30 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.
**** 23:46:30 UTY0817 MultiLoad submitting the following request:
Select NULL from SAMPLES.ML_ITEMPPI_wodate where (LogType = 125) and (Seq =
1) and (MloadSeq = 0);
**** 23:46:30 UTY0817 MultiLoad submitting the following request:
Select NULL from SAMPLES.ML_ITEMPPI_wodate where (LogType = 120) and (Seq =
1);
**** 23:46:32 UTY0815 MLOAD session(s) connected: 2.
**** 23:46:32 UTY0817 MultiLoad submitting the following request:
BEGIN MLOAD samples.itemppi_wodate WITH INTERVAL;
**** 23:46:32 UTY0817 MultiLoad submitting the following request:
INS SAMPLES.ML_ITEMPPI_wodate (LogType, Seq, MLoadSeq)VALUES(130, 1, 10);
**** 23:46:32 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 =
= =
========================================================================
**** 23:46:32 UTY0817 MultiLoad submitting the following request:
BT;
**** 23:46:32 UTY0817 MultiLoad submitting the following request:
USING L_ORDERKEY(INTEGER), L_PARTKEY(INTEGER), L_QUANTITY(DECIMAL(15,2)),
L_LINESTATUS(CHAR(1)) insert into samples.itemppi_wodate values
(:L_ORDERKEY, :L_PARTKEY, :L_QUANTITY, :L_LINESTATUS);
**** 23:46:32 UTY0817 MultiLoad submitting the following request:
INS SAMPLES.ML_ITEMPPI_wodate (LogType, Seq, MLoadSeq)VALUES(130, 1, 20);
**** 23:46:32 UTY0817 MultiLoad submitting the following request:
ET;
========================================================================
= =
= MultiLoad Acquisition Phase =
= =
========================================================================
**** 23:46:32 UTY0817 MultiLoad submitting the following request:
BEGIN TRANSACTION;
**** 23:46:32 UTY0817 MultiLoad submitting the following request:
CHECKPOINT LOADING INTERVAL 0;
**** 23:46:33 UTY0817 MultiLoad submitting the following request:
CHECKPOINT LOADING INTERVAL 0;
**** 23:46:34 UTY0817 MultiLoad submitting the following request:
CHECKPOINT LOADING INTERVAL 0;
**** 23:46:35 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);
**** 23:46:35 UTY0817 MultiLoad submitting the following request:
CHECKPOINT LOADING INTERVAL 0;
**** 23:46:36 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,
1, 1, 0, 200, 200, 200, 200, 0, 0, 0, 0, 0, :Ckpt);
**** 23:46:36 UTY0826 A checkpoint has been taken, recording that end of file has been reached
for IMPORT 1 of this MultiLoad Import task.
**** 23:46:36 UTY1803 Import processing statistics
. IMPORT 1 Total thus far
. ========= ==============
Candidate records considered:........ 200....... 200
Apply conditions satisfied:.......... 200....... 200
Candidate records not applied:....... 0....... 0
Candidate records rejected:.......... 0....... 0
**** 23:46:36 UTY0817 MultiLoad submitting the following request:
CHECKPOINT LOADING INTERVAL 0;
**** 23:46:37 UTY0817 MultiLoad submitting the following request:
CHECKPOINT LOADING INTERVAL 0 END;
**** 23:46:38 UTY0817 MultiLoad submitting the following request:
INS SAMPLES.ML_ITEMPPI_wodate (LogType, Seq, MLoadSeq)VALUES(130, 1, 30);
**** 23:46:38 UTY0817 MultiLoad submitting the following request:
ET;
========================================================================
= =
= MultiLoad Application Phase =
= =
========================================================================
**** 23:46:38 UTY0817 MultiLoad submitting the following request:
EXEC MLOAD samples.itemppi_wodate;
**** 23:46:39 UTY0818 Statistics for table samples.itemppi_wodate:
Inserts: 0
Updates: 0
Deletes: 0
**** 23:46:39 UTY0817 MultiLoad submitting the following request:
INS SAMPLES.ML_ITEMPPI_wodate (LogType, Seq) VALUES (115, 1)
**** 23:46:39 UTY0817 MultiLoad submitting the following request:
BEGIN TRANSACTION;
**** 23:46:39 UTY0817 MultiLoad submitting the following request:
END MLOAD;
**** 23:46:39 UTY0817 MultiLoad submitting the following request:
INS SAMPLES.ML_ITEMPPI_wodate (LogType, Seq) VALUES (120, 1)
**** 23:46:39 UTY0817 MultiLoad submitting the following request:
USING Ckpt(VARBYTE(1024)) INS SAMPLES.ML_ITEMPPI_wodate (LogType, Seq,
MLoadCkpt)VALUES(135, 1, :Ckpt);
**** 23:46:39 UTY0817 MultiLoad submitting the following request:
END TRANSACTION;
========================================================================
= =
= MultiLoad Task Cleanup =
= =
========================================================================
**** 23:46:39 UTY0817 MultiLoad submitting the following request:
SELECT COUNT(*) FROM samples.ET_itemppi_wodate;
**** 23:46:39 UTY0821 Error table samples.ET_itemppi_wodate is EMPTY, dropping table.
**** 23:46:39 UTY0817 MultiLoad submitting the following request:
BEGIN TRANSACTION;
**** 23:46:39 UTY0817 MultiLoad submitting the following request:
INS SAMPLES.ML_ITEMPPI_wodate (LogType, Seq, MLoadSeq)VALUES(125, 1, 1)
**** 23:46:39 UTY0817 MultiLoad submitting the following request:
DROP TABLE samples.ET_itemppi_wodate;
**** 23:46:39 UTY0817 MultiLoad submitting the following request:
END TRANSACTION;
**** 23:46:39 UTY0817 MultiLoad submitting the following request:
SELECT COUNT(*) FROM samples.UV_itemppi_wodate;
**** 23:46:39 UTY0820 Error table samples.UV_itemppi_wodate contains 200 rows.
**** 23:46:39 UTY0817 MultiLoad submitting the following request:
BEGIN TRANSACTION;
**** 23:46:39 UTY0817 MultiLoad submitting the following request:
INS SAMPLES.ML_ITEMPPI_wodate (LogType, Seq, MLoadSeq)VALUES(125, 1, 3)
**** 23:46:39 UTY0817 MultiLoad submitting the following request:
DROP TABLE samples.WT_itemppi_wodate;
**** 23:46:39 UTY0817 MultiLoad submitting the following request:
END TRANSACTION;
**** 23:46:39 UTY0825 Error table statistics for:

Target table 1: samples.itemppi_wodate
Number of Rows Error Table Name
============== ========================================================
0 samples.ET_itemppi_wodate
200 samples.UV_itemppi_wodate

**** 23:46:39 UTY0817 MultiLoad submitting the following request:
BEGIN TRANSACTION;
**** 23:46:39 UTY0817 MultiLoad submitting the following request:
USING Ckpt(VARBYTE(1024)) INS SAMPLES.ML_ITEMPPI_wodate (LogType, Seq,
MLoadCkpt)VALUES(140, 1, :Ckpt);
**** 23:46:39 UTY0817 MultiLoad submitting the following request:
INS SAMPLES.ML_ITEMPPI_wodate (LogType, Seq) VALUES (125, 1)
**** 23:46:40 UTY0817 MultiLoad submitting the following request:
END TRANSACTION;
**** 23:46:41 UTY0822 MultiLoad processing complete for this MultiLoad import task.
========================================================================
= =
= MultiLoad Task Complete =
= =
========================================================================
========================================================================
= =
= Processing Control Statements =
= =
========================================================================

0014 .logoff;
========================================================================
= =
= Logoff/Disconnect =
= =
========================================================================
**** 23:46:41 UTY6216 The restart log table has been dropped.
**** 23:46:41 UTY6212 A successful disconnect was made from the RDBMS.
**** 23:46:41 UTY2410 Total processor time used = '1.21681 Seconds'
. Start : 23:46:29 - TUE FEB 25, 2014
. End : 23:46:41 - TUE FEB 25, 2014
. Highest return code encountered = '0'.

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 );

The target and source table structure are same except that the source table has one extra date column, indeed which have't been fast exported.

Sample source data:








L_ORDERKEY L_PARTKEY L_QUANTITY L_LINESTATUS L_SHIPDATE
225 1,352 34 O 8/4/1995
257 352 12 O 5/30/1998
258 1,984 21 F 4/21/1994
291 332 3 F 5/10/1994

Sample data in UV table:















L_ORDERKEY L_PARTKEY L_QUANTITY L_LINESTATUS ImportSeq DMLSeq SMTSeq ApplySeq SourceSeq Uniqueness DBCErrorCode DBCErrorField
57,600 346,112 8,704.00   1 1 1 1 197 0 2,793  
65,792 90,112 3,072.00   1 1 1 1 25 0 2,793  
66,048 507,904 5,376.00   1 1 1 1 68 0 2,793  
74,496 84,992 768   1 1 1 1 37 0 2,793  
91,904 239,616 9,216.00   1 1 1 1 73 0 2,793  

Awaiting your valuable responses.

7 REPLIES
Senior Apprentice

Re: fast export and mload in fastload format issue - error code 2793

Hi Cheeli,

2793 is the result of the MARK DUPLICATE INSERT option, i.e. all rows already exist in the target table.

Enthusiast

Re: fast export and mload in fastload format issue - error code 2793

I am using TD express edition 13.0 on windows 7.  Rewriting the sample records for input and output.

Input: (order by 1,2,3,4)

L_ORDERKEY    L_PARTKEY   L_QUANTITY   L_LINESTATUS   L_SHIPDATE

225                   1,352           34                  O                         8/4/1995

257                      352           12                  O                         5/30/1998

Output records selective columns in UV table: (order by 1,2,3,4)

L_ORDERKEY    L_PARTKEY   L_QUANTITY   L_LINESTATUS 

57,600             346,112        8,704.00

65,792             90,112           3,072.00

Enthusiast

Re: fast export and mload in fastload format issue - error code 2793

Hi Dieter,

Yes, the rows are already present in the target table.  I have deleted them and ran the entire process again. Now data loaded into target table, but I could see mismatch in the data from source to target.

sel L_ORDERKEY, L_PARTKEY, L_QUANTITY, L_LINESTATUS from itemppi_wodate  -- target table

intersect

sel L_ORDERKEY, L_PARTKEY, L_QUANTITY, L_LINESTATUS from itemppi_bkp   -- source table

order by 1,2,3,4

gives 0 rows.

Can you please elucidate what went wrong.

Teradata Employee

Re: fast export and mload in fastload format issue - error code 2793

Try it with just one row of data and post the hex output of the data file from the FastExport job.

-- SteveF
Senior Apprentice

Re: fast export and mload in fastload format issue - error code 2793

Hi Cheeli,

the default for FastExport is INDICATOR mode, you need to modify your LAYOUT:

.LAYOUT DATA_LAYOUT INDICATORS;

 Or let FastExport create the MLoad:

.EXPORT .... MLSCRIPT xxx.ml;

Enthusiast

Re: fast export and mload in fastload format issue - error code 2793

Perfect, thank you Dieter and Steven. 

Teradata Employee

Re: fast export and mload in fastload format issue - error code 2793

Thanks Dieter! That is where I was going next, right after looking at a hex dump of the data.

-- SteveF