Fatal Error: While loading data using Teradata Mltiload

rajeev_prabhuat · Posted: Fri Mar 26, 2010 1:35 am

Hi,

We are trying to load data from a Sequential file into a Teradata table by making use of the teradata mload stage and my job Aborts with the following Fatal error

Quote:

Fatal Error: Fatal: Multiload returned: 223 *Please refer to statement in ....rpt file to resolve.

I am getting this error in all the load jobs, though the data is getting loaded into the teradata target tables successfully . Our sequential file is located at Linux server. Since the job is aborting we don't want to move this code to production withoug fixing this or rather end user will not be happy to take this forward.

Any support on this is well appreciated.

The .rpt (report) file details are provided below for your information.

Quote:

Report file
Server – cpc3780

========================================================================
= =
= MultiLoad Utility Release MLOD.12.00.00.000 =
= Platform LINUX =
= =
========================================================================
= =
= Copyright 1990-2007, NCR Corporation. ALL RIGHTS RESERVED. =
= =
========================================================================
**** 03:56:16 UTY2411 Processing start date: THU MAR 25, 2010
========================================================================
= =
= Logon/Connection =
= =
========================================================================
0001 .logtable EDW_STAGE.STATUS_S_WE411_MLOG;
0002 .logon TDP5/edwuser,;
**** 03:56:16 UTY8400 Teradata Database Release: 12.00.02.21
**** 03:56:16 UTY8400 Teradata Database Version: 12.00.02.41
**** 03:56:16 UTY8400 Default character set: ASCII
**** 03:56:16 UTY8400 Current RDBMS has interval support
**** 03:56:16 UTY8400 Current RDBMS has UDT support
**** 03:56:16 UTY8400 Maximum supported buffer size: 1M
**** 03:56:16 UTY8400 Data Encryption supported by RDBMS server
**** 03:56:16 UTY6211 A successful connect was made to the RDBMS.
**** 03:56:16 UTY6217 Logtable 'EDW_STAGE.STATUS_S_WE411_MLOG' has been created.
========================================================================
= =
= Processing Control Statements =
= =
========================================================================
0003 DELETE FROM EDW_STAGE.STATUS_S_WE411;
**** 03:56:17 UTY1016 'DELETE' request successful.

0004 .if &SYSRC then;
**** 03:56:17 UTY2402 Previous statement modified to:
0005 .if 0 then;
0006 .logoff &SYSRC;
0007 .endif;
0008 drop table EDW_STAGE.STATUS_S_WE411_UV;
**** 03:56:17 UTY1008 RDBMS failure: 3807, Object 'EDW_STAGE.STATUS_S_WE411_UV' does not
exist.
0009 drop table EDW_STAGE.STATUS_S_WE411_ET;
**** 03:56:17 UTY1008 RDBMS failure: 3807, Object 'EDW_STAGE.STATUS_S_WE411_ET' does not
exist.
0010 .begin import mload tables EDW_STAGE.STATUS_S_WE411 WORKTABLES
EDW_STAGE.STATUS_S_WE411_WT ERRORTABLES EDW_STAGE.STATUS_S_WE411_UV
EDW_STAGE.STATUS_S_WE411_ET CHECKPOINT 0;
========================================================================
= =
= Processing MultiLoad Statements =
= =
========================================================================
0011 .layout internal indicators;
0012 .field STATUS_CD * char(2);
0013 .field STATUS_DESCR * varchar(20);
0014 .dml label tdmload;
0015 insert EDW_STAGE.STATUS_S_WE411 (STATUS_CD, STATUS_DESCR)
values (:STATUS_CD, :STATUS_DESCR);
0016 .import INFILE /opt/lnapps/erp/EDW_STAGE_STATUS_S_WE411.dat format fastload
layout internal apply tdmload;
0017 .end mload;
========================================================================
= =
= MultiLoad Initial Phase =
= =
========================================================================
**** 03:56:17 UTY0829 Options in effect for this MultiLoad import task:
. Sessions: One session per available amp.
. Checkpoint: No rate in effect.
. Tenacity: 4 hour limit to successfully connect load sessions.
. Errlimit: No limit in effect.
. AmpCheck: In effect for apply phase transitions.
**** 03:56:17 UTY0817 MultiLoad submitting the following request:
Select NULL from EDW_STAGE.STATUS_S_WE411_MLOG where (LogType = 125) and
(Seq = 1) and (MloadSeq = 0);
**** 03:56:17 UTY0817 MultiLoad submitting the following request:
Select NULL from EDW_STAGE.STATUS_S_WE411_MLOG where (LogType = 120) and
(Seq = 1);
**** 03:56:26 UTY0815 MLOAD session(s) connected: 32.
**** 03:56:26 UTY0817 MultiLoad submitting the following request:
BEGIN MLOAD EDW_STAGE.STATUS_S_WE411 WITH INTERVAL;
**** 03:56:26 UTY0817 MultiLoad submitting the following request:
INS EDW_STAGE.STATUS_S_WE411_MLOG (LogType, Seq, MLoadSeq)VALUES(130, 1,
10);
**** 03:56:26 UTY0817 MultiLoad submitting the following request:
MLOAD EDW_STAGE.STATUS_S_WE411 with EDW_STAGE.STATUS_S_WE411_WT errortables
EDW_STAGE.STATUS_S_WE411_UV, EDW_STAGE.STATUS_S_WE411_ET;
========================================================================
= =
= MultiLoad DML Transaction Phase =
= =
========================================================================
**** 03:56:26 UTY0817 MultiLoad submitting the following request:
BT;
**** 03:56:26 UTY0817 MultiLoad submitting the following request:
USING STATUS_CD(CHAR(2)), STATUS_DESCR(VARCHAR(20)) insert
EDW_STAGE.STATUS_S_WE411 (STATUS_CD, STATUS_DESCR)
values (:STATUS_CD, :STATUS_DESCR);
**** 03:56:26 UTY0817 MultiLoad submitting the following request:
INS EDW_STAGE.STATUS_S_WE411_MLOG (LogType, Seq, MLoadSeq)VALUES(130, 1,
20);
**** 03:56:26 UTY0817 MultiLoad submitting the following request:
ET;
========================================================================
= =
= MultiLoad Acquisition Phase =
= =
========================================================================
**** 03:56:26 UTY1818 As requested, no record checkpoints will be performed for this task.
**** 03:56:26 UTY0817 MultiLoad submitting the following request:
BEGIN TRANSACTION;
**** 03:56:26 UTY0817 MultiLoad submitting the following request:
CHECKPOINT LOADING INTERVAL 0;
**** 03:56:26 UTY0817 MultiLoad submitting the following request:
CHECKPOINT LOADING INTERVAL 0;
**** 03:56:26 UTY0817 MultiLoad submitting the following request:
CHECKPOINT LOADING INTERVAL 0;
**** 03:56:27 UTY0817 MultiLoad submitting the following request:
USING Ckpt(VARBYTE(1024)) INSERT EDW_STAGE.STATUS_S_WE411_MLOG (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);
**** 03:56:28 UTY0817 MultiLoad submitting the following request:
CHECKPOINT LOADING INTERVAL 0;
**** 03:56:28 UTY0817 MultiLoad submitting the following request:
USING Ckpt(VARBYTE(1024)) INSERT EDW_STAGE.STATUS_S_WE411_MLOG (Logtype,
Seq, MLoadSeq, MLoadImpSeq, MLoadSrcSeq,
MiscInt1,MiscInt2,MiscInt3,MiscInt4,
MiscInt5,MiscInt6,MiscInt7,MiscInt8,CkptInterval,MLoadCkpt) VALUES (110, 1,
1, 1, 0, 1, 1, 1, 1, 0, 0, 0, 0, 0, :Ckpt);
**** 03:56:28 UTY0826 A checkpoint has been taken, recording that end of file has been reached
for IMPORT 1 of this MultiLoad Import task.
**** 03:56:28 UTY1803 Import processing statistics
. IMPORT 1 Total thus far
. ========= ==============
Candidate records considered:........ 1....... 1
Apply conditions satisfied:.......... 1....... 1
Candidate records not applied:....... 0....... 0
Candidate records rejected:.......... 0....... 0
**** 03:56:28 UTY0817 MultiLoad submitting the following request:
CHECKPOINT LOADING INTERVAL 0;
**** 03:56:29 UTY0817 MultiLoad submitting the following request:
CHECKPOINT LOADING INTERVAL 0 END;
**** 03:56:30 UTY0817 MultiLoad submitting the following request:
INS EDW_STAGE.STATUS_S_WE411_MLOG (LogType, Seq, MLoadSeq)VALUES(130, 1,
30);
**** 03:56:30 UTY0817 MultiLoad submitting the following request:
ET;
========================================================================
= =
= MultiLoad Application Phase =
= =
========================================================================
**** 03:56:31 UTY0817 MultiLoad submitting the following request:
EXEC MLOAD EDW_STAGE.STATUS_S_WE411;
**** 03:56:31 UTY0818 Statistics for table EDW_STAGE.STATUS_S_WE411:
Inserts: 1
Updates: 0
Deletes: 0
**** 03:56:31 UTY0817 MultiLoad submitting the following request:
INS EDW_STAGE.STATUS_S_WE411_MLOG (LogType, Seq) VALUES (115, 1)
**** 03:56:31 UTY0817 MultiLoad submitting the following request:
BEGIN TRANSACTION;
**** 03:56:31 UTY0817 MultiLoad submitting the following request:
END MLOAD;
**** 03:56:31 UTY0817 MultiLoad submitting the following request:
INS EDW_STAGE.STATUS_S_WE411_MLOG (LogType, Seq) VALUES (120, 1)
**** 03:56:31 UTY0817 MultiLoad submitting the following request:
USING Ckpt(VARBYTE(1024)) INS EDW_STAGE.STATUS_S_WE411_MLOG (LogType, Seq,
MLoadCkpt)VALUES(135, 1, :Ckpt);
**** 03:56:31 UTY0817 MultiLoad submitting the following request:
END TRANSACTION;
========================================================================
= =
= MultiLoad Task Cleanup =
= =
========================================================================
**** 03:56:31 UTY0817 MultiLoad submitting the following request:
SELECT COUNT(*) FROM EDW_STAGE.STATUS_S_WE411_UV;
**** 03:56:31 UTY0821 Error table EDW_STAGE.STATUS_S_WE411_UV is EMPTY, dropping table.
**** 03:56:31 UTY0817 MultiLoad submitting the following request:
BEGIN TRANSACTION;
**** 03:56:31 UTY0817 MultiLoad submitting the following request:
INS EDW_STAGE.STATUS_S_WE411_MLOG (LogType, Seq, MLoadSeq)VALUES(125, 1,
1)
**** 03:56:31 UTY0817 MultiLoad submitting the following request:
DROP TABLE EDW_STAGE.STATUS_S_WE411_UV;
**** 03:56:31 UTY0817 MultiLoad submitting the following request:
END TRANSACTION;
**** 03:56:31 UTY0817 MultiLoad submitting the following request:
SELECT COUNT(*) FROM EDW_STAGE.STATUS_S_WE411_ET;
**** 03:56:32 UTY0821 Error table EDW_STAGE.STATUS_S_WE411_ET is EMPTY, dropping table.
**** 03:56:32 UTY0817 MultiLoad submitting the following request:
BEGIN TRANSACTION;
**** 03:56:32 UTY0817 MultiLoad submitting the following request:
INS EDW_STAGE.STATUS_S_WE411_MLOG (LogType, Seq, MLoadSeq)VALUES(125, 1,
2)
**** 03:56:32 UTY0817 MultiLoad submitting the following request:
DROP TABLE EDW_STAGE.STATUS_S_WE411_ET;
**** 03:56:32 UTY0817 MultiLoad submitting the following request:
END TRANSACTION;
**** 03:56:32 UTY0817 MultiLoad submitting the following request:
BEGIN TRANSACTION;
**** 03:56:32 UTY0817 MultiLoad submitting the following request:
INS EDW_STAGE.STATUS_S_WE411_MLOG (LogType, Seq, MLoadSeq)VALUES(125, 1,
3)
**** 03:56:32 UTY0817 MultiLoad submitting the following request:
DROP TABLE EDW_STAGE.STATUS_S_WE411_WT;
**** 03:56:32 UTY0817 MultiLoad submitting the following request:
END TRANSACTION;
**** 03:56:32 UTY0825 Error table statistics for:

Target table 1: EDW_STAGE.STATUS_S_WE411
Number of Rows Error Table Name
============== ========================================================
0 EDW_STAGE.STATUS_S_WE411_UV
0 EDW_STAGE.STATUS_S_WE411_ET

**** 03:56:32 UTY0817 MultiLoad submitting the following request:
BEGIN TRANSACTION;
**** 03:56:32 UTY0817 MultiLoad submitting the following request:
USING Ckpt(VARBYTE(1024)) INS EDW_STAGE.STATUS_S_WE411_MLOG (LogType, Seq,
MLoadCkpt)VALUES(140, 1, :Ckpt);
**** 03:56:32 UTY0817 MultiLoad submitting the following request:
INS EDW_STAGE.STATUS_S_WE411_MLOG (LogType, Seq) VALUES (125, 1)
**** 03:56:32 UTY0817 MultiLoad submitting the following request:
END TRANSACTION;
**** 03:56:34 UTY0822 MultiLoad processing complete for this MultiLoad import task.
========================================================================
= =
= MultiLoad Task Complete =
= =
========================================================================
========================================================================
= =
= Processing Control Statements =
= =
========================================================================
0018 .if &SYSRC then;
**** 03:56:34 UTY2402 Previous statement modified to:
0019 .if 3807 then;
0020 .logoff &SYSRC;
**** 03:56:34 UTY2402 Previous statement modified to:
0021 .logoff 3807;
========================================================================
= =
= Logoff/Disconnect =
= =
========================================================================
**** 03:56:35 UTY6216 The restart log table has been dropped.
**** 03:56:35 UTY6212 A successful disconnect was made from the RDBMS.
**** 03:56:35 UTY2410 Total processor time used = '0.83 Seconds'
. Start : 03:56:16 - THU MAR 25, 2010
. End : 03:56:35 - THU MAR 25, 2010
. Highest return code encountered = '3807'.

Regards
Rajeev Prabhu

fridge · Posted: Fri Mar 26, 2010 11:20 am

Hullo,

I suspect the final error (3807) is down to the previous error concerning the error tables

03:56:17 UTY1008 RDBMS failure: 3807, Object 'EDW_STAGE.STATUS_S_WE411_ET' does not
exist.

Basically when the multiload starts it attempts to drop any error tables first, though these will only get left if you either
a) Had some bad data
or / and
b) You attempted to insert duplicates

on a previous run

as the tables dont exist - it cant drop them , however will continue to do the load proper

However not sure why it is picking up the error at the end - as dropping of non-existant tables shouldlt cause the job to fail

How is the script produced - is it custom load type in the Multiload stage - I work on AIX so cant seem to replicate the script

hamzaqk · Posted: Thu Apr 22, 2010 1:43 am

it gives 3807 error when the target table does not exist.... the one you are trying to insert into...

ManojRawat · Participant Joined: 21 Oct 2009 Posts: 2 Points: 14

========================================================================
= =
= Processing Control Statements =
= =
========================================================================
0018 .if &SYSRC then;
**** 03:56:34 UTY2402 Previous statement modified to:
0019 .if 3807 then;
0020 .logoff &SYSRC; **** 03:56:34 UTY2402 Previous statement modified to:
0021 .logoff 3807;

Load script outputting the return code "3807" due to the "highlited code" mentioned above.Multiloade will not prompt for failure if this portion removed from the code.

aaron102 · Participant Joined: 09 Nov 2009 Posts: 17 Points: 245

In after section of the script in the multiload script did you miss to tick mark "Treat SQL Errors as non fatal error"