Multiload - Highest return code encountered = '20'

Tools & Utilities

Multiload - Highest return code encountered = '20'

Hi Gurus

I have inheritied a few jobs that load data into the TeraData database using Multiload.

This one intermittently fails with :

FINEOS_IDM_01IP_621_Load_PartyReltn_New..INS: MultiLoad returned: 20 ** Please refer to statements in H:\QA_DW00IDM\TeraWork\NEX_ETL_REF_INS_IP_TO_IP_RELTN_REPORT_INS.txt to resolve the issue **

If I look into this file, it shows:

========================================================================
= =
= MultiLoad Utility Release MLOD.07.08.00.003 =
= Platform WIN32 =
= =
========================================================================
= =
= Copyright 1990-2006, NCR Corporation. ALL RIGHTS RESERVED. =
= =
========================================================================
**** 00:37:45 UTY2411 Processing start date: SAT OCT 27, 2007
========================================================================
= =
= Logon/Connection =
= =
========================================================================
0001 .logtable DDWTAW.NEX_LOG_INS_INS_IP_TO_IP_RELTN;
0002 .logon dbc1/PDWTIU,;
**** 00:37:48 UTY8400 Default character set: ASCII
**** 00:37:48 UTY8400 Current RDBMS has interval support
**** 00:37:48 UTY8400 Maximum supported buffer size: 1M
**** 00:37:48 UTY8400 Data Encryption supported by RDBMS server
**** 00:37:48 UTY6211 A successful connect was made to the RDBMS.
**** 00:37:48 UTY6217 Logtable 'DDWTAW.NEX_LOG_INS_INS_IP_TO_IP_RELTN' has been created.
========================================================================
= =
= Processing Control Statements =
= =
========================================================================
0003 .set DBNAME to 'DDWTI';
0004 .set TBNAME to 'INS_IP_TO_IP_RELTN';
0005 .set JOBNAME to 'FINEOS_IDM_01IP_621_Load_PartyReltn_New';
0006 .begin import mload tables DDWTI.INS_IP_TO_IP_RELTN WORKTABLES
DDWTAW.NEX_WT_INS_INS_IP_TO_IP_RELTN ERRORTABLES
DDWTAW.NEX_ET_INS_INS_IP_TO_IP_RELTN DDWTAW.NEX_UV_INS_INS_IP_TO_IP_RELTN
ERRLIMIT 50 CHECKPOINT 0 SESSIONS 18 4;
========================================================================
= =
= Processing MultiLoad Statements =
= =
========================================================================
0007 .layout internal indicators;
0008 .field IP1_SYS_SRC_ID * smallint;
0009 .field IP1_ID * varchar(32);
0010 .field IP2_SYS_SRC_ID * smallint;
0011 .field IP2_ID * varchar(32);
0012 .field IP1_ROL_TYP * smallint;
0013 .field IP2_ROL_TYP * smallint;
0014 .field IP_IP_REL_TYP * smallint;
0015 .field SNAP_DT * date;
0016 .field CHG_DT * date;
0017 .dml label tdmload;
0018 insert DDWTI.INS_IP_TO_IP_RELTN (IP1_SYS_SRC_ID, IP1_ID, IP2_SYS_SRC_ID, IP2_ID,
IP1_ROL_TYP, IP2_ROL_TYP, IP_IP_REL_TYP, SNAP_DT, CHG_DT)
values (:IP1_SYS_SRC_ID, :IP1_ID, :IP2_SYS_SRC_ID, :IP2_ID, :IP1_ROL_TYP,
:IP2_ROL_TYP, :IP_IP_REL_TYP, :SNAP_DT, :CHG_DT);
0019 .import INFILE \.\pipe\tdmpipe_INS_IP_Upd_Out_Mload.DDWTI.INS_IP_TO_IP_RELTN
AXSMOD np_AXSMOD.dll format fastload layout internal apply tdmload;
0020 .end mload;
========================================================================
= =
= MultiLoad Initial Phase =
= =
========================================================================
**** 00:37:48 UTY0829 Options in effect for this MultiLoad import task:
. Sessions: 18 session(s).
. Minimum of 4 load session(s) requested.
. Checkpoint: No rate in effect.
. Tenacity: 4 hour limit to successfully connect load sessions.
. Errlimit: 50 rejected record(s).
. AmpCheck: In effect for apply phase transitions.
**** 00:37:48 UTY0817 MultiLoad submitting the following request:
Select NULL from DDWTAW.NEX_LOG_INS_INS_IP_TO_IP_RELTN where (LogType =
125) and (Seq = 1) and (MloadSeq = 0);
**** 00:37:48 UTY0817 MultiLoad submitting the following request:
Select NULL from DDWTAW.NEX_LOG_INS_INS_IP_TO_IP_RELTN where (LogType =
120) and (Seq = 1);
**** 00:37:51 UTY0812 MLOAD session(s) requested: 18.
**** 00:37:51 UTY0815 MLOAD session(s) connected: 18.
**** 00:37:51 UTY0817 MultiLoad submitting the following request:
BEGIN MLOAD DDWTI.INS_IP_TO_IP_RELTN WITH INTERVAL;
**** 00:37:51 UTY0817 MultiLoad submitting the following request:
INS DDWTAW.NEX_LOG_INS_INS_IP_TO_IP_RELTN (LogType, Seq,
MLoadSeq)VALUES(130, 1, 10);
**** 00:37:51 UTY0817 MultiLoad submitting the following request:
MLOAD DDWTI.INS_IP_TO_IP_RELTN with DDWTAW.NEX_WT_INS_INS_IP_TO_IP_RELTN
errortables DDWTAW.NEX_ET_INS_INS_IP_TO_IP_RELTN,
DDWTAW.NEX_UV_INS_INS_IP_TO_IP_RELTN;
========================================================================
= =
= MultiLoad DML Transaction Phase =
= =
========================================================================
**** 00:37:52 UTY0817 MultiLoad submitting the following request:
BT;
**** 00:37:52 UTY0817 MultiLoad submitting the following request:
USING IP1_SYS_SRC_ID(SMALLINT), IP1_ID(VARCHAR(32)),
IP2_SYS_SRC_ID(SMALLINT), IP2_ID(VARCHAR(32)), IP1_ROL_TYP(SMALLINT),
IP2_ROL_TYP(SMALLINT), IP_IP_REL_TYP(SMALLINT), SNAP_DT(DATE), CHG_DT(DATE)
insert DDWTI.INS_IP_TO_IP_RELTN (IP1_SYS_SRC_ID, IP1_ID, IP2_SYS_SRC_ID,
IP2_ID, IP1_ROL_TYP, IP2_ROL_TYP, IP_IP_REL_TYP, SNAP_DT, CHG_DT)
values (:IP1_SYS_SRC_ID, :IP1_ID, :IP2_SYS_SRC_ID, :IP2_ID, :IP1_ROL_TYP,
:IP2_ROL_TYP, :IP_IP_REL_TYP, :SNAP_DT, :CHG_DT);
**** 00:37:52 UTY0817 MultiLoad submitting the following request:
INS DDWTAW.NEX_LOG_INS_INS_IP_TO_IP_RELTN (LogType, Seq,
MLoadSeq)VALUES(130, 1, 20);
**** 00:37:52 UTY0817 MultiLoad submitting the following request:
ET;
========================================================================
= =
= MultiLoad Acquisition Phase =
= =
========================================================================
**** 00:37:52 UTY1818 As requested, no record checkpoints will be performed for this task.
**** 00:37:52 UTY0817 MultiLoad submitting the following request:
BEGIN TRANSACTION;
**** 00:37:52 UTY0817 MultiLoad submitting the following request:
CHECKPOINT LOADING INTERVAL 0;
**** 00:37:52 UTY0817 MultiLoad submitting the following request:
CHECKPOINT LOADING INTERVAL 0;
**** 00:37:59 UTY0817 MultiLoad submitting the following request:
CHECKPOINT LOADING INTERVAL 0;
**** 00:37:59 UTY0817 MultiLoad submitting the following request:
USING Ckpt(VARBYTE(1024)) INSERT DDWTAW.NEX_LOG_INS_INS_IP_TO_IP_RELTN
(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);
**** 00:37:59 UTY0817 MultiLoad submitting the following request:
CHECKPOINT LOADING INTERVAL 0;
**** 00:38:00 UTY0817 MultiLoad submitting the following request:
USING Ckpt(VARBYTE(1024)) INSERT DDWTAW.NEX_LOG_INS_INS_IP_TO_IP_RELTN
(Logtype, Seq, MLoadSeq, MLoadImpSeq, MLoadSrcSeq,
MiscInt1,MiscInt2,MiscInt3,MiscInt4,
MiscInt5,MiscInt6,MiscInt7,MiscInt8,CkptInterval,MLoadCkpt) VALUES (110, 1,
1, 1, 0, 9, 9, 9, 9, 0, 0, 0, 0, 0, :Ckpt);
**** 00:38:00 UTY0826 A checkpoint has been taken, recording that end of file has been reached
for IMPORT 1 of this MultiLoad Import task.
**** 00:38:01 UTY1803 Import processing statistics
. IMPORT 1 Total thus far
. ========= ==============
Candidate records considered:........ 9....... 9
Apply conditions satisfied:.......... 9....... 9
Candidate records not applied:....... 0....... 0
Candidate records rejected:.......... 0....... 0
**** 00:38:01 UTY0817 MultiLoad submitting the following request:
CHECKPOINT LOADING INTERVAL 0;
**** 00:38:01 UTY0817 MultiLoad submitting the following request:
CHECKPOINT LOADING INTERVAL 0 END;
**** 00:38:02 UTY0817 MultiLoad submitting the following request:
INS DDWTAW.NEX_LOG_INS_INS_IP_TO_IP_RELTN (LogType, Seq,
MLoadSeq)VALUES(130, 1, 30);
**** 00:38:02 UTY0817 MultiLoad submitting the following request:
ET;
========================================================================
= =
= MultiLoad Application Phase =
= =
========================================================================
**** 00:38:03 UTY0817 MultiLoad submitting the following request:
EXEC MLOAD DDWTI.INS_IP_TO_IP_RELTN;
**** 00:38:03 UTY0818 Statistics for table DDWTI.INS_IP_TO_IP_RELTN:
Inserts: 8
Updates: 0
Deletes: 0
**** 00:38:03 UTY0817 MultiLoad submitting the following request:
INS DDWTAW.NEX_LOG_INS_INS_IP_TO_IP_RELTN (LogType, Seq) VALUES (115, 1)
**** 00:38:03 UTY0817 MultiLoad submitting the following request:
BEGIN TRANSACTION;
**** 00:38:03 UTY0817 MultiLoad submitting the following request:
END MLOAD;
**** 00:38:03 UTY0817 MultiLoad submitting the following request:
INS DDWTAW.NEX_LOG_INS_INS_IP_TO_IP_RELTN (LogType, Seq) VALUES (120, 1)
**** 00:38:03 UTY0817 MultiLoad submitting the following request:
USING Ckpt(VARBYTE(1024)) INS DDWTAW.NEX_LOG_INS_INS_IP_TO_IP_RELTN
(LogType, Seq, MLoadCkpt)VALUES(135, 1, :Ckpt);
**** 00:38:03 UTY0817 MultiLoad submitting the following request:
END TRANSACTION;
========================================================================
= =
= MultiLoad Task Cleanup =
= =
========================================================================
**** 00:38:03 UTY0817 MultiLoad submitting the following request:
SELECT COUNT(*) FROM DDWTAW.NEX_ET_INS_INS_IP_TO_IP_RELTN;
**** 00:38:03 UTY0821 Error table DDWTAW.NEX_ET_INS_INS_IP_TO_IP_RELTN is EMPTY, dropping
table.
**** 00:38:03 UTY0817 MultiLoad submitting the following request:
BEGIN TRANSACTION;
**** 00:38:03 UTY0817 MultiLoad submitting the following request:
INS DDWTAW.NEX_LOG_INS_INS_IP_TO_IP_RELTN (LogType, Seq,
MLoadSeq)VALUES(125, 1, 1)
**** 00:38:03 UTY0817 MultiLoad submitting the following request:
DROP TABLE DDWTAW.NEX_ET_INS_INS_IP_TO_IP_RELTN;
**** 00:38:03 UTY0817 MultiLoad submitting the following request:
END TRANSACTION;
**** 00:38:03 UTY0817 MultiLoad submitting the following request:
SELECT COUNT(*) FROM DDWTAW.NEX_UV_INS_INS_IP_TO_IP_RELTN;
**** 00:38:03 UTY0820 Error table DDWTAW.NEX_UV_INS_INS_IP_TO_IP_RELTN contains 1 rows.
**** 00:38:03 UTY0817 MultiLoad submitting the following request:
BEGIN TRANSACTION;
**** 00:38:03 UTY0817 MultiLoad submitting the following request:
INS DDWTAW.NEX_LOG_INS_INS_IP_TO_IP_RELTN (LogType, Seq,
MLoadSeq)VALUES(125, 1, 3)
**** 00:38:04 UTY0817 MultiLoad submitting the following request:
DROP TABLE DDWTAW.NEX_WT_INS_INS_IP_TO_IP_RELTN;
**** 00:38:04 UTY0817 MultiLoad submitting the following request:
END TRANSACTION;
**** 00:38:04 UTY0825 Error table statistics for:

Target table 1: DDWTI.INS_IP_TO_IP_RELTN
Number of Rows Error Table Name
============== ========================================================
0 DDWTAW.NEX_ET_INS_INS_IP_TO_IP_RELTN
1 DDWTAW.NEX_UV_INS_INS_IP_TO_IP_RELTN

**** 00:38:04 UTY0817 MultiLoad submitting the following request:
BEGIN TRANSACTION;
**** 00:38:04 UTY0817 MultiLoad submitting the following request:
USING Ckpt(VARBYTE(1024)) INS DDWTAW.NEX_LOG_INS_INS_IP_TO_IP_RELTN
(LogType, Seq, MLoadCkpt)VALUES(140, 1, :Ckpt);
**** 00:38:04 UTY0817 MultiLoad submitting the following request:
INS DDWTAW.NEX_LOG_INS_INS_IP_TO_IP_RELTN (LogType, Seq) VALUES (125, 1)
**** 00:38:04 UTY0817 MultiLoad submitting the following request:
END TRANSACTION;
**** 00:38:04 UTY0822 MultiLoad processing complete for this MultiLoad import task.
========================================================================
= =
= MultiLoad Task Complete =
= =
========================================================================
========================================================================
= =
= Processing Control Statements =
= =
========================================================================
0021 .if &SYSETCNT then;
**** 00:38:04 UTY2402 Previous statement modified to:
0022 .if 0 then;
0023 .logoff 20;
0024 .endif;
0025 .if &SYSUVCNT then;
**** 00:38:04 UTY2402 Previous statement modified to:
0026 .if 1 then;
0027 .logoff 20;
========================================================================
= =
= Logoff/Disconnect =
= =
========================================================================
**** 00:38:05 UTY6216 The restart log table has been dropped.
**** 00:38:05 UTY6212 A successful disconnect was made from the RDBMS.
**** 00:38:05 UTY2410 Total processor time used = '6.8125 Seconds'
. Start : 00:37:45 - SAT OCT 27, 2007
. End : 00:38:05 - SAT OCT 27, 2007
. Highest return code encountered = '20'.

-----------------------------------------------------------------------------

Please help. Even if u can point me where to look, that would be great.

Thanks

Gary

1 REPLY
Enthusiast

Re: Multiload - Highest return code encountered = '20'

Your target table has an UPI and you probably have a record in your file, which has a duplicate PI (ie PI value already there in the target table), you can check it up in the error table NEX_UV_INS_INS_IP_TO_IP_RELTN that is defined for uniqueness violation.