Archives of the TeradataForum

Message Posted: Thu, 31 May 2001 @ 16:27:31 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Hola a mis amigos Teradatyls,

Well, I've got a weird one. Using the following script, a friend of mine ended up with 8,160,110 rows in the ET table, even though the script has an ERRLIMIT set to 50, and the job output shows it was set correctly. It's a date error (which we corrected), but when I looked at the ET table, I got the following:

SELECT ErrorCode, COUNT(*)
  FROM ET_ProgramStatus
 GROUP BY 1;

*** Query completed.  1 row(s) found.  2 field(s) returned.
*** Time was  14 secs.       05/31/2001    10:13:03 AM
     ErrorCode      Count(*)
   -------------------------
          2665       8160110

What's really weird is that the HostData and the field name are empty! The correction was to start the date in column 25, not column 24. Anyway, this stupid thing ran 37 hours and never STOPPED at the errlimit!

Anyway, here's the script:

/************************************************************************/
/*                                                                      */
/*   Multiload script MLIPStat for ProgramStatus table                  */
/*             (Pacmis Program/Participation)                           */
/*                                                                      */
/*   THIS SCRIPT DROPS AUXILLARY MLOAD TABLES TO EXECUTE A RE-BUILD.    */
/*   USE CAREFULLY WHEN NEEDED OTHERWISE DELETE NOW.                    */
/*                                                                      */
/*   03/14/01 (pjb)  Convert CaseID and ClientID from decimal           */
/*            input format to char format for table                     */
/*   03/21/01 (pjb)  Special NULLIF check for zeroes in dates           */
/*   03/21/01 (pjb)  Default dates in format yyyymm to day of 1         */
/*            EndDate                                                   */
/*                                                                      */
/************************************************************************/
 /* DROP TABLE HCFTest.Logtable_ProgramStatus; */
.LOGTABLE HCFTest.LOGTABLE_ProgramStatus;
.SET DBName TO 'HCFTest';
.SET TBName TO 'PacmisPP/ProgramStatus';

DROP TABLE &DBName..ET_ProgramStatus;
DROP TABLE &DBName..WT_ProgramStatus;
DROP TABLE &DBName..UV_ProgramStatus;
DROP TABLE &DBName..ProgramStatus;

 /* .RUN FILE CRETTAB; */
CREATE TABLE &DBName..ProgramStatus (
       CaseID               CHAR(9) NOT NULL,
       ClientID             CHAR(10) NOT NULL,
       ProgramType          CHAR(2),
       ProgramSubtype       CHAR(2),
       BenefitEndDate       CHAR(6),
       ProgramStatusDate    CHAR(8),
       ProgramStatus        CHAR(2)
)
       PRIMARY INDEX XPKProgramStatus (
              CaseID,
              ClientID
       )
;

/**********************************************************/
/*                                                        */
/*   THIS SCRIPT INSERTS WITH MULTILOAD UTILITY.          */
/*                                                        */
/**********************************************************/
DATABASE &DBName;

.BEGIN IMPORT MLOAD
  TABLES ProgramStatus
      WORKTABLES &DBName..WT_ProgramStatus
      ERRORTABLES &DBName..ET_ProgramStatus
                  &DBName..UV_ProgramStatus
      ERRLIMIT 50
      AMPCHECK ALL;

.LAYOUT INPUTLAYOUT;
      .FIELD IN_BenefitEndDate                          1 CHAR(6)
                NULLIF IN_BenefitEndDate = '000000' ;
      .FIELD IN_CaseID                                  7 DECIMAL(9,0);
      .FIELD IN_ClientID                               12 DECIMAL(10,0);
      .FIELD IN_ProgramType                            18 CHAR( 2);
      .FIELD IN_ProgramSubtype                         20 CHAR( 2);
      .FIELD IN_ProgramStatus                          22 CHAR( 2);
      .FIELD IN_ProgramStatusDate                      24 CHAR( 8)
                NULLIF IN_ProgramStatusDate = '00000000' ;

.DML LABEL INSERTS;
INSERT INTO ProgramStatus
      (
       CaseID
      ,ClientID
      ,ProgramType
      ,ProgramSubtype
      ,BenefitEndDate
      ,ProgramStatusDate
      ,ProgramStatus
 ) VALUES (
       :IN_CaseID   (format '9(9)')
      ,:IN_ClientID (format '9(10)')
      ,:IN_ProgramType
      ,:IN_ProgramSubtype
      ,:IN_BenefitEndDate(date, format 'yyyymm')
      ,:IN_ProgramStatusDate (date, format 'yyyymmdd')
      ,:IN_ProgramStatus
);

.IMPORT INFILE INFILE
      LAYOUT INPUTLAYOUT
      APPLY INSERTS;

.END MLOAD;

INSERT INTO &DBName..MultiLoadStats
      (DataBaseName
      ,TableName
      ,CreatorName
      ,BeginDate
      ,BeginTime
      ,EndDate
      ,EndTime
      ,ReturnCode
      ,ETCount
      ,UVCount
      ,InsertCount
      ,UpdateCount
      )
VALUES
      ('&DBName'
      ,'&TBName'
      ,'&SysUser'
      ,'&SysDate4'
      ,'&SysTime'
      ,Date (format 'yyyy/mm/dd')
      ,Time
      ,'&SysRC'
      ,'&SysETCnt'
      ,'&SysUVCnt'
      ,'&SysInsCnt'
      ,'&SysUpdCnt'
      )
;

.LOGOFF;

And here's a snippet of the output showing the Multiload settings:

**** 12:25:12 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:    50 rejected record(s).
     .       AmpCheck:    In effect for all phases.
**** 12:25:12 UTY0817 MultiLoad submitting the following request:
     Select NULL from HCFTEST.LOGTABLE_PROGRAMSTATUS where (LogType = =
125) and (Seq = 1) and (MloadSeq = 0);
**** 12:25:12 UTY0817 MultiLoad submitting the following request:
     Select NULL from HCFTEST.LOGTABLE_PROGRAMSTATUS where (LogType = =
120) and (Seq = 1);
**** 12:25:13 UTY0815 MLOAD session(s) connected: 6.
**** 12:25:13 UTY0817 MultiLoad submitting the following request:
     BEGIN MLOAD PROGRAMSTATUS;

iv

Frank C. Martinez IV

	<Prev	Next>		<<First	<Prev	Next>	Last>>