All Forums Tools
chill3che 99 posts Joined 10/12
25 Feb 2014
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,MLoad Ckpt) 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,MLoad Ckpt) 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.

Thanks, Cheeli
dnoeth 4628 posts Joined 11/04
25 Feb 2014

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

Dieter

chill3che 99 posts Joined 10/12
25 Feb 2014

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

 

Thanks,
Cheeli

chill3che 99 posts Joined 10/12
25 Feb 2014

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.

Thanks,
Cheeli

feinholz 1234 posts Joined 05/08
25 Feb 2014

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

--SteveF

dnoeth 4628 posts Joined 11/04
25 Feb 2014

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;

Dieter

chill3che 99 posts Joined 10/12
25 Feb 2014

Perfect, thank you Dieter and Steven. 

Thanks,
Cheeli

feinholz 1234 posts Joined 05/08
25 Feb 2014

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

--SteveF

You must sign in to leave a comment.