ERRLIMIT ignored in MLOAD script

Tools & Utilities
Fan

ERRLIMIT ignored in MLOAD script

Hi All,

I am encountering a strange problem with my Mload script. I have specified an ERRLIMIT of 1 record in mload script to limit the number of records being rejected into ET table and thus making the script fail.

ERRLIMIT 1 /* Should make the job fail if more than 1 record is rejected */

When i run the script all the records go into ET table but still MLOAD runs with a RC=0. ET table has error 2666 for PKG_ESTB_DT field . Now I know 2666 error and can resolve the issue but i am worried as to why it is returning RC=0 even after setting ERRLIMIT to 1 record?

Appreciate any help.

========================================================================
= =
= MultiLoad Utility Release MLOD.12.00.00.000 =
= Platform MVS =
= =
========================================================================
= =
= Copyright 1990-2007, NCR Corporation. ALL RIGHTS RESERVED. =
= =
========================================================================
**** 14:33:54 UTY2411 Processing start date: TUE JUL 27, 2010
========================================================================
= =
= Logon/Connection =
= =
========================================================================
0001 /*-----------------------------------------------------------------*/
/* SCRIPT ID : AN180 */
/* SCRIPT NAME : */
/* PACKAGE_LETTER_DATA */
/* */
/* LOG TABLE : LOG_AN180L01 */
/* UPDATED TABLE : PACKAGE_LETTER_DATA */
/* WORK TABLES : ET_AN180L01_PKLT */
/* : UV_AN180L01_PKLT */
/* : WT_AN180L01_PKLT */
/* AIM : */
/* THIS SCRIPT INSERTS INTO PACKAGE_LETTER_DATA */
/*-----------------------------------------------------------------*/
/* DATE: AUTHOR: DESCRIPTION: */
/* -------- ------- ------------ */
/*-----------------------------------------------------------------*/
.LOGTABLE LOG_AN180L01;
0002 .RUN FILE LOGON;
0003 .LOGON T1AFSTST,;
**** 14:33:54 UTY8400 Teradata Database Release: V2R.06.02.02.94
**** 14:33:54 UTY8400 Teradata Database Version: 06.02.02.106
**** 14:33:54 UTY8400 Default character set: EBCDIC
**** 14:33:54 UTY8400 Current RDBMS has interval support
**** 14:33:54 UTY8400 Current RDBMS has UDT support
**** 14:33:54 UTY8400 Maximum supported buffer size: 1M
**** 14:33:54 UTY6211 A successful connect was made to the RDBMS.
**** 14:33:54 UTY6217 Logtable 'T1AFSTEMP.LOG_AN180L01' has been created.
========================================================================
= =
= Processing Control Statements =
= =
========================================================================
0004 .SET ENVVIEW TO 'T1AFSVIEW';
0005 .SET ENVTEMP TO 'T1AFSTEMP';
0006 .RUN FILE GETDATE;
0007 .ACCEPT PROCESS_DATE,PROCESS_YEAR,PROCESS_MONTH,PROCESS_DAY,
PROCESS_YEAR_MONTH,DAY_OF_WEEK,DAY_OF_WEEK_NUMBER,PREV_PROCESS_YEAR,
PREV_PROCESS_MONTH,NEXT_PROCESS_YEAR,NEXT_PROCESS_MONTH,
NEXT_BUSINESS_DAY,PM_1_YEAR,PM_1_MONTH,PM_2_YEAR,PM_2_MONTH,
PM_3_YEAR,PM_3_MONTH,PM_4_YEAR,PM_4_MONTH,PM_5_YEAR,PM_5_MONTH
FROM FILE PROCDATE;

0008 .BEGIN IMPORT MLOAD TABLES &ENVVIEW..PACKAGE_LETTER_DATA
WORKTABLES &ENVTEMP..WT_AN180L01_PKLT
ERRORTABLES &ENVTEMP..ET_AN180L01_PKLT
&ENVTEMP..UV_AN180L01_PKLT
ERRLIMIT 1
TABLEWAIT 1;
**** 14:33:54 UTY2402 Previous statement modified to:
0009 .BEGIN IMPORT MLOAD TABLES T1AFSVIEW.PACKAGE_LETTER_DATA
WORKTABLES T1AFSTEMP.WT_AN180L01_PKLT
ERRORTABLES T1AFSTEMP.ET_AN180L01_PKLT
T1AFSTEMP.UV_AN180L01_PKLT
ERRLIMIT 1
TABLEWAIT 1;
========================================================================
= =
= Processing MultiLoad Statements =
= =
========================================================================

0010 .LAYOUT PKG_LETTER_LAYOUT_DEFN;
0011 .FIELD PKG_ID 1 CHAR(9);
0012 .FIELD DOMICILE_BSB_NO 10 CHAR(6)
NULLIF DOMICILE_BSB_NO = '';
0013 .FIELD BUID 16 CHAR(9)
NULLIF BUID = '';
0014 .FIELD PKG_TYPE_CD 25 CHAR(4)
NULLIF PKG_TYPE_CD = '';
0015 .FIELD PKG_SHORT_NM 29 CHAR(60)
NULLIF PKG_SHORT_NM = '';
0016 .FIELD PREP_RULE_CD 89 CHAR(10)
NULLIF PREP_RULE_CD = '';
0017 .FIELD PREP_RULE_DESC 99 CHAR(60)
NULLIF PREP_RULE_DESC = '';
0018 .FIELD PKG_ESTB_DT 159 CHAR(8)
NULLIF PKG_ESTB_DT = '';
0019 .FIELD PKG_LAST_AMEND_DT 167 CHAR(8)
NULLIF PKG_LAST_AMEND_DT = '';
0020 .FIELD PKG_CLOSE_DT 175 CHAR(8)
NULLIF PKG_CLOSE_DT = '';
0021 .FIELD PKG_CLOSURE_IND 183 CHAR(1)
NULLIF PKG_CLOSURE_IND = '';
0022 .FIELD PKG_FEE_NXT_DUE_DT 184 CHAR(8)
NULLIF PKG_FEE_NXT_DUE_DT = '';
0023 .FIELD PKG_FEE_AMT 192 CHAR(14)
NULLIF PKG_FEE_AMT = '';
0024 .FIELD PKG_FEE_TYPE_CD 206 CHAR(6)
NULLIF PKG_FEE_TYPE_CD = '';
0025 .FIELD PKG_FEE_EXEMPT_IND 212 CHAR(1)
NULLIF PKG_FEE_EXEMPT_IND = '';

0026 .DML LABEL AN_INSERT
IGNORE DUPLICATE INSERT ROWS;

0027 INSERT INTO &ENVVIEW..PACKAGE_LETTER_DATA
(
PKG_ID
,DOMICILE_BSB_NO
,BUID
,PKG_TYPE_CD
,PKG_SHORT_NM
,PREP_RULE_CD
,PREP_RULE_DESC
,PKG_ESTB_DT
,PKG_LAST_AMEND_DT
,PKG_CLOSE_DT
,PKG_CLOSURE_IND
,PKG_FEE_NEXT_DUE_DT
,PKG_FEE_AMT
,PKG_FEE_TYPE_CD
,PKG_FEE_EXMPT_IND
,PROCESS_DT
,INSERT_DT
)
VALUES
(
CAST(:PKG_ID AS INTEGER)
,CAST(:DOMICILE_BSB_NO AS INTEGER)
,CAST(:BUID AS INTEGER)
,:PKG_TYPE_CD
,:PKG_SHORT_NM
,:PREP_RULE_CD
,:PREP_RULE_DESC
,CAST(:PKG_ESTB_DT AS DATE FORMAT 'YYYYMMDD')
,CAST(:PKG_LAST_AMEND_DT AS DATE FORMAT 'YYYYMMDD')
,CAST(:PKG_CLOSE_DT AS DATE FORMAT 'YYYYMMDD')
,:PKG_CLOSURE_IND
,CAST(:PKG_FEE_NXT_DUE_DT AS DATE FORMAT 'YYYYMMDD')
,CAST(:PKG_FEE_AMT AS DECIMAL(13,2))
,:PKG_FEE_TYPE_CD
,:PKG_FEE_EXEMPT_IND
,'&PROCESS_DATE'
,CURRENT_DATE
);
**** 14:33:54 UTY2402 Previous statement modified to:
0028 INSERT INTO T1AFSVIEW.PACKAGE_LETTER_DATA
(
PKG_ID
,DOMICILE_BSB_NO
,BUID
,PKG_TYPE_CD
,PKG_SHORT_NM
,PREP_RULE_CD
,PREP_RULE_DESC
,PKG_ESTB_DT
,PKG_LAST_AMEND_DT
,PKG_CLOSE_DT
,PKG_CLOSURE_IND
,PKG_FEE_NEXT_DUE_DT
,PKG_FEE_AMT
,PKG_FEE_TYPE_CD
,PKG_FEE_EXMPT_IND
,PROCESS_DT
,INSERT_DT
)
VALUES
(
CAST(:PKG_ID AS INTEGER)
,CAST(:DOMICILE_BSB_NO AS INTEGER)
,CAST(:BUID AS INTEGER)
,:PKG_TYPE_CD
,:PKG_SHORT_NM
,:PREP_RULE_CD
,:PREP_RULE_DESC
,CAST(:PKG_ESTB_DT AS DATE FORMAT 'YYYYMMDD')
,CAST(:PKG_LAST_AMEND_DT AS DATE FORMAT 'YYYYMMDD')
,CAST(:PKG_CLOSE_DT AS DATE FORMAT 'YYYYMMDD')
,:PKG_CLOSURE_IND
,CAST(:PKG_FEE_NXT_DUE_DT AS DATE FORMAT 'YYYYMMDD')
,CAST(:PKG_FEE_AMT AS DECIMAL(13,2))
,:PKG_FEE_TYPE_CD
,:PKG_FEE_EXEMPT_IND
,'20100702'
,CURRENT_DATE
);

0029 .IMPORT INFILE INFILE
FROM 2
LAYOUT PKG_LETTER_LAYOUT_DEFN
APPLY AN_INSERT
WHERE PKG_ID <>''
;

0030 .END MLOAD;
========================================================================
= =
= MultiLoad Initial Phase =
= =
========================================================================
**** 14:33:54 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.
. Tablewait: 1 hour(s).
. Errlimit: 1 rejected record(s).
. AmpCheck: In effect for apply phase transitions.
**** 14:33:54 UTY0817 MultiLoad submitting the following request:
Select NULL from T1AFSTEMP.LOG_AN180L01 where (LogType = 125) and (Seq = 1)
and (MloadSeq = 0);
**** 14:33:54 UTY0817 MultiLoad submitting the following request:
Select NULL from T1AFSTEMP.LOG_AN180L01 where (LogType = 120) and (Seq =
1);
**** 14:33:55 UTY0815 MLOAD session(s) connected: 20.
**** 14:33:55 UTY0817 MultiLoad submitting the following request:
BEGIN MLOAD T1AFSVIEW.PACKAGE_LETTER_DATA WITH INTERVAL;
**** 14:33:55 UTY0817 MultiLoad submitting the following request:
INS T1AFSTEMP.LOG_AN180L01 (LogType, Seq, MLoadSeq)VALUES(130, 1, 10);
**** 14:33:55 UTY0817 MultiLoad submitting the following request:
MLOAD T1AFSVIEW.PACKAGE_LETTER_DATA with T1AFSTEMP.WT_AN180L01_PKLT
errortables T1AFSTEMP.ET_AN180L01_PKLT, T1AFSTEMP.UV_AN180L01_PKLT;
========================================================================
= =
= MultiLoad DML Transaction Phase =
= =
========================================================================
**** 14:33:55 UTY0817 MultiLoad submitting the following request:
BT;
**** 14:33:55 UTY0817 MultiLoad submitting the following request:
USING PKG_ID(CHAR(9)), DOMICILE_BSB_NO(CHAR(6)), BUID(CHAR(9)),
PKG_TYPE_CD(CHAR(4)), PKG_SHORT_NM(CHAR(60)), PREP_RULE_CD(CHAR(10)),
PREP_RULE_DESC(CHAR(60)), PKG_ESTB_DT(CHAR(8)), PKG_LAST_AMEND_DT(CHAR(8)),
PKG_CLOSE_DT(CHAR(8)), PKG_CLOSURE_IND(CHAR(1)),
PKG_FEE_NXT_DUE_DT(CHAR(8)), PKG_FEE_AMT(CHAR(14)),
PKG_FEE_TYPE_CD(CHAR(6)), PKG_FEE_EXEMPT_IND(CHAR(1)) INSERT INTO
T1AFSVIEW.PACKAGE_LETTER_DATA
(
PKG_ID
,DOMICILE_BSB_NO
,BUID
,PKG_TYPE_CD
,PKG_SHORT_NM
,PREP_RULE_CD
,PREP_RULE_DESC
,PKG_ESTB_DT
,PKG_LAST_AMEND_DT
,PKG_CLOSE_DT
,PKG_CLOSURE_IND
,PKG_FEE_NEXT_DUE_DT
,PKG_FEE_AMT
,PKG_FEE_TYPE_CD
,PKG_FEE_EXMPT_IND
,PROCESS_DT
,INSERT_DT
)
VALUES
(
CAST(:PKG_ID AS INTEGER)
,CAST(:DOMICILE_BSB_NO AS INTEGER)
,CAST(:BUID AS INTEGER)
,:PKG_TYPE_CD
,:PKG_SHORT_NM
,:PREP_RULE_CD
,:PREP_RULE_DESC
,CAST(:PKG_ESTB_DT AS DATE FORMAT 'YYYYMMDD')
,CAST(:PKG_LAST_AMEND_DT AS DATE FORMAT 'YYYYMMDD')
,CAST(:PKG_CLOSE_DT AS DATE FORMAT 'YYYYMMDD')
,:PKG_CLOSURE_IND
,CAST(:PKG_FEE_NXT_DUE_DT AS DATE FORMAT 'YYYYMMDD')
,CAST(:PKG_FEE_AMT AS DECIMAL(13,2))
,:PKG_FEE_TYPE_CD
,:PKG_FEE_EXEMPT_IND
,'20100702'
,CURRENT_DATE
);
**** 14:33:55 UTY0817 MultiLoad submitting the following request:
INS T1AFSTEMP.LOG_AN180L01 (LogType, Seq, MLoadSeq)VALUES(130, 1, 20);
**** 14:33:56 UTY0817 MultiLoad submitting the following request:
ET;
========================================================================
= =
= MultiLoad Acquisition Phase =
= =
========================================================================
**** 14:33:56 UTY0817 MultiLoad submitting the following request:
BEGIN TRANSACTION;
**** 14:33:56 UTY0817 MultiLoad submitting the following request:
CHECKPOINT LOADING INTERVAL 0;
**** 14:33:56 UTY0817 MultiLoad submitting the following request:
CHECKPOINT LOADING INTERVAL 0;
**** 14:33:56 UTY0817 MultiLoad submitting the following request:
CHECKPOINT LOADING INTERVAL 0;
**** 14:33:57 UTY0817 MultiLoad submitting the following request:
USING Ckpt(VARBYTE(1024)) INSERT T1AFSTEMP.LOG_AN180L01 (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);
**** 14:33:57 UTY0817 MultiLoad submitting the following request:
CHECKPOINT LOADING INTERVAL 0;
**** 14:33:58 UTY0817 MultiLoad submitting the following request:
USING Ckpt(VARBYTE(1024)) INSERT T1AFSTEMP.LOG_AN180L01 (Logtype, Seq,
MLoadSeq, MLoadImpSeq, MLoadSrcSeq, MiscInt1,MiscInt2,MiscInt3,MiscInt4,
MiscInt5,MiscInt6,MiscInt7,MiscInt8,CkptInterval,MLoadCkpt) VALUES (110, 1,
1, 1, 0, 6377, 6377, 6377, 6377, 0, 0, 0, 0, 0, :Ckpt);
**** 14:33:58 UTY0826 A checkpoint has been taken, recording that end of file has been reached
for IMPORT 1 of this MultiLoad Import task.
**** 14:33:58 UTY1803 Import processing statistics
. IMPORT 1 Total thus far
. ========= ==============
Candidate records considered:........ 6377....... 6377
Apply conditions satisfied:.......... 6377....... 6377
Candidate records not applied:....... 0....... 0
Candidate records rejected:.......... 0....... 0
**** 14:33:58 UTY0817 MultiLoad submitting the following request:
CHECKPOINT LOADING INTERVAL 0;
**** 14:33:59 UTY0817 MultiLoad submitting the following request:
CHECKPOINT LOADING INTERVAL 0 END;
**** 14:34:00 UTY0817 MultiLoad submitting the following request:
INS T1AFSTEMP.LOG_AN180L01 (LogType, Seq, MLoadSeq)VALUES(130, 1, 30);
**** 14:34:00 UTY0817 MultiLoad submitting the following request:
ET;
========================================================================
= =
= MultiLoad Application Phase =
= =
========================================================================
**** 14:34:00 UTY0817 MultiLoad submitting the following request:
EXEC MLOAD T1AFSVIEW.PACKAGE_LETTER_DATA;
**** 14:34:08 UTY0818 Statistics for table T1AFSVIEW.PACKAGE_LETTER_DATA:
Inserts: 0
Updates: 0
Deletes: 0
**** 14:34:08 UTY0817 MultiLoad submitting the following request:
INS T1AFSTEMP.LOG_AN180L01 (LogType, Seq) VALUES (115, 1)
**** 14:34:08 UTY0817 MultiLoad submitting the following request:
BEGIN TRANSACTION;
**** 14:34:08 UTY0817 MultiLoad submitting the following request:
END MLOAD;
**** 14:34:08 UTY0817 MultiLoad submitting the following request:
INS T1AFSTEMP.LOG_AN180L01 (LogType, Seq) VALUES (120, 1)
**** 14:34:08 UTY0817 MultiLoad submitting the following request:
USING Ckpt(VARBYTE(1024)) INS T1AFSTEMP.LOG_AN180L01 (LogType, Seq,
MLoadCkpt)VALUES(135, 1, :Ckpt);
**** 14:34:08 UTY0817 MultiLoad submitting the following request:
END TRANSACTION;
========================================================================
= =
= MultiLoad Task Cleanup =
= =
========================================================================
**** 14:34:08 UTY0817 MultiLoad submitting the following request:
SELECT COUNT(*) FROM T1AFSTEMP.ET_AN180L01_PKLT;
**** 14:34:08 UTY0820 Error table T1AFSTEMP.ET_AN180L01_PKLT contains 6377 rows.
**** 14:34:08 UTY0817 MultiLoad submitting the following request:
SELECT COUNT(*) FROM T1AFSTEMP.UV_AN180L01_PKLT;
**** 14:34:08 UTY0821 Error table T1AFSTEMP.UV_AN180L01_PKLT is EMPTY, dropping table.
**** 14:34:08 UTY0817 MultiLoad submitting the following request:
BEGIN TRANSACTION;
**** 14:34:08 UTY0817 MultiLoad submitting the following request:
INS T1AFSTEMP.LOG_AN180L01 (LogType, Seq, MLoadSeq)VALUES(125, 1, 2)
**** 14:34:08 UTY0817 MultiLoad submitting the following request:
DROP TABLE T1AFSTEMP.UV_AN180L01_PKLT;
**** 14:34:08 UTY0817 MultiLoad submitting the following request:
END TRANSACTION;
**** 14:34:08 UTY0817 MultiLoad submitting the following request:
BEGIN TRANSACTION;
**** 14:34:08 UTY0817 MultiLoad submitting the following request:
INS T1AFSTEMP.LOG_AN180L01 (LogType, Seq, MLoadSeq)VALUES(125, 1, 3)
**** 14:34:08 UTY0817 MultiLoad submitting the following request:
DROP TABLE T1AFSTEMP.WT_AN180L01_PKLT;
**** 14:34:09 UTY0817 MultiLoad submitting the following request:
END TRANSACTION;
**** 14:34:09 UTY0825 Error table statistics for:

Target table 1: T1AFSVIEW.PACKAGE_LETTER_DATA
Number of Rows Error Table Name
============== ========================================================
6377 T1AFSTEMP.ET_AN180L01_PKLT
0 T1AFSTEMP.UV_AN180L01_PKLT

**** 14:34:09 UTY0817 MultiLoad submitting the following request:
BEGIN TRANSACTION;
**** 14:34:09 UTY0817 MultiLoad submitting the following request:
USING Ckpt(VARBYTE(1024)) INS T1AFSTEMP.LOG_AN180L01 (LogType, Seq,
MLoadCkpt)VALUES(140, 1, :Ckpt);
**** 14:34:09 UTY0817 MultiLoad submitting the following request:
INS T1AFSTEMP.LOG_AN180L01 (LogType, Seq) VALUES (125, 1)
**** 14:34:09 UTY0817 MultiLoad submitting the following request:
END TRANSACTION;
**** 14:34:09 UTY0822 MultiLoad processing complete for this MultiLoad import task.
========================================================================
= =
= MultiLoad Task Complete =
= =
========================================================================
========================================================================
= =
= Processing Control Statements =
= =
========================================================================
0031 .LOGOFF;
========================================================================
= =
= Logoff/Disconnect =
= =
========================================================================
**** 14:34:09 UTY6216 The restart log table has been dropped.
**** 14:34:09 UTY6212 A successful disconnect was made from the RDBMS.
**** 14:34:09 UTY2410 Total processor time used = '0.12901 Seconds'
. Start : 14:33:54 - TUE JUL 27, 2010
. End : 14:34:09 - TUE JUL 27, 2010
. Highest return code encountered = '0'.
Tags (1)
3 REPLIES
Junior Contributor

Re: ERRLIMIT ignored in MLOAD script

The number of errors might be higher than the specified errorlimit, because that limit is checked *after* a block of rows has been sent to the receiving AMP.
You didn't specify the number of sessions, so it defaulted to one session per AMP, which is 20 on your system, which resulted in a maximum of one block per session, so even if the errorlimit was exceeded, it already loaded all rows.

If you set the sessions to 1 the load will fail (and the number of errors is the number of records per datablock)

Dieter
Fan

Re: ERRLIMIT ignored in MLOAD script

Thanks Dieter,

But if I set sessions to 1 wouldn't that unnecssary limit the throughput.On a large production box the Job will take a lot more time.

Can't i acheive the output without setting the sessions?
Junior Contributor

Re: ERRLIMIT ignored in MLOAD script

The throughput for loading 6377 rows of approx. 200 bytes will not change if you use 1 or 20 sessions.

But if you load those 6377 rows on a large system without specifying the number of sessions, it will take more time to log on all those sessions than loading the data.
And on a large system you will always control the session number, because if you don't you will waste sessions. A load is not getting faster anymore if the optimal number if sessions is reached.

Dieter