MultiLoad returned: 20 Error

[gagan8877]

Hi Gurus

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

.logtable #$TDWorkDB1#.NEX_LOG_INS_#TableNm1#;
.logon #$TDPID#/#$TDUserId1#,PASSWORD;
.set DBNAME to '#$TDDB1#';
.set TBNAME to '#TableNm1#';
.set JOBNAME to '#ThisJobNm#';
.begin import mload tables #$TDDB1#.#TableNm1#
WORKTABLES #$TDWorkDB1#.NEX_WT_INS_#TableNm1#
ERRORTABLES #$TDWorkDB1#.NEX_ET_INS_#TableNm1# #$TDWorkDB1#.NEX_UV_INS_#TableNm1#
ERRLIMIT #$TDMLErrLmt#
CHECKPOINT #$TDMLChkPnt#
SESSIONS #$TDMLMaxSess# #$TDMLMinSess#;
.layout internal indicators;
.field IP1_SYS_SRC_ID * smallint;
.field IP1_ID * varchar(32);
.field IP2_SYS_SRC_ID * smallint;
.field IP2_ID * varchar(32);
.field IP1_ROL_TYP * smallint;
.field IP2_ROL_TYP * smallint;
.field IP_IP_REL_TYP * smallint;
.field SNAP_DT * date;
.field CHG_DT * date;
.dml label tdmload;
insert #$TDDB1#.#TableNm1# (
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);
.import INFILE
/*UNIX*/ #$TDWorkDir#/INS_IP_Upd_Out_Mload.#$TDDB1#.#TableNm1#
/*NT*/ \\.\pipe\tdmpipe_INS_IP_Upd_Out_Mload.#$TDDB1#.#TableNm1# AXSMOD np_AXSMOD.dll
format fastload layout internal apply tdmload;
.end mload;
.if &SYSETCNT then;
.logoff 20;
.endif;
.if &SYSUVCNT then;
.logoff 20;
.endif;
.logoff;
-------------------------------------------------------------

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

[gagan8877]

Any point towards where to look will be also be appreciated. Pls give your suggestions.

[fridge]

im a bit rusty on this but believe the problem is that you are getting a Unique Constrain violation

if you look at the last lin es

0025 .if &SYSUVCNT then;
**** 00:38:04 UTY2402 Previous statement modified to:
0026 .if 1 then;
0027 .logoff 20;

This I beleive means that it has found >0 records in the Error table for the load - DDWTAW.NEX_UV_INS_INS_IP_TO_IP_RELTN you can see a reference to this a bit further up in the log - the UV stands for Unique Violation.

I believe you can stop this aborting by jigging with the MultiLoad stage , but since its a job u have inherited its probably more importent to understand why u are getting the UVs in the first place

[gagan8877]

im a bit rusty on this but believe the problem is that you are getting a Unique Constrain violation

if you look at the last lin es

0025 .if &SYSUVCNT then;
**** 00:38:04 UTY2402 Previous statement modified to:
0026 .if 1 then;
0027 .logoff 20;

This I beleive means that it has found >0 records in the Error table for the load - DDWTAW.NEX_UV_INS_INS_IP_TO_IP_RELTN you can see a reference to this a bit further up in the log - the UV stands for Unique Violation.

I believe you can stop this aborting by jigging with the MultiLoad stage , but since its a job u have inherited its probably more importent to understand why u are getting the UVs in the first place

Hi Fridge

Thanks a lot for the reply. Most people got scarred because the error was long, but u read the whole thing and pointed it out. I conveyed it to our Teradata DBA and he confirmed it as well. Thanks Again.