RecordErrorFileName

Tools
Tools covers the tools and utilities you use to work with Teradata and its supporting ecosystem. You'll find information on everything from the Teradata Eclipse plug-in to load/extract tools.

RecordErrorFileName

z/OS mainframe ....RecordErrorFileName not working getting error message TPT19229 0 error rows sent to error file DD:DATAERR 

 

JOB STEP

 

//*------------------------------------------------------------------*//
//JS0040  EXEC PGM=TBUILD,REGION=0M
//ATTRFILE DD  DSN=&&TEMP1,DISP=(OLD,DELETE,DELETE)
//SYSOUT   DD  SYSOUT=*
//SYSPRINT DD  SYSOUT=*
//SYSABEND DD  SYSOUT=G
//JOBLOG   DD  DISP=OLD,DSN=HLQ.JOBLOG
//DATAIN   DD  DISP=SHR,DSN=HLQ.NDM.ISP.MONTHLY.CCBRINPT(0)
//DATAERR  DD  DISP=OLD,DSN=HLQ.TEST
//CPD1     DD  DISP=OLD,DSN=HLQ.CPD1
//CPD2     DD  DISP=OLD,DSN=HLQ.CPD2
//LVCP     DD  DISP=OLD,DSN=HLQ.LVCP
//SYSIN    DD  *
DEFINE JOB FILE_LOAD
DESCRIPTION 'LOAD LOB INPUT FILE TO STG TABLE'
(
 DEFINE SCHEMA LOB_INPUTFILE_STG_SCHEMA
 (
    FNAME                    CHAR(20),
    MNAME                    CHAR(13),
    LNAME                    CHAR(25),
    NSUFFIX                  CHAR(5) ,
    ADDRESS1                 CHAR(40),
    ADDRESS2                 CHAR(40),
    CITY                     CHAR(25),
    STATE                    CHAR(2) ,
    ZIP                      CHAR(5) ,
    SSN                      CHAR(9) ,
    ANUM                     CHAR(18),
    LFLAG                    CHAR(1) ,
    DYYYYMM                  CHAR(6) ,
    SNUM                     CHAR(8) ,
    LPASS1                   CHAR(18),
    LPASS2                   CHAR(18),
    LPASS3                   CHAR(18),
    LPASS4                   CHAR(18),
    LPASS5                   CHAR(18),
    FRCIND                   CHAR(1),
    PID                      CHAR(11),
    APPID                    CHAR(3),
    CIND                     CHAR(1),
    DYYYYMMDD1               CHAR(8),
    FILLER                   CHAR(9),
    DYYYYMMDD2               CHAR(8),
    BAL11                    CHAR(11),
    BYPAS                    CHAR(1)
 );
DEFINE OPERATOR DDL_OPERATOR
TYPE DDL
ATTRIBUTES
(
VARCHAR TDPID          = @TDPID,
VARCHAR USERNAME       = @USERNAME,
VARCHAR PRIVATELOGNAME = 'DDLLOG',
VARCHAR ERRORLIST      = ['3807','2580','3803']
);
 
DEFINE OPERATOR READ_OPERATOR
TYPE DATACONNECTOR PRODUCER
SCHEMA LOB_INPUTFILE_STG_SCHEMA
ATTRIBUTES
(
VARCHAR PRIVATELOGNAME = 'READLOG',
VARCHAR FILENAME       = 'DD:DATAIN',
VARCHAR RECORDERRORFILENAME = 'DD:DATAERR',
VARCHAR INDICATORMODE  = 'N',
VARCHAR OPENMODE       = 'READ',
VARCHAR FORMAT         = 'FORMATTED'
);
 
DEFINE OPERATOR UPDATE_OPERATOR
TYPE UPDATE
SCHEMA LOB_INPUTFILE_STG_SCHEMA
ATTRIBUTES
(
VARCHAR TDPID          = @TDPID,
VARCHAR USERNAME       = @USERNAME,
VARCHAR PRIVATELOGNAME = 'UP_LOG',
INTEGER ERRORLIMIT     =  10,
INTEGER MAXSESSIONS    =  10,
INTEGER MINSESSIONS    =  1,
VARCHAR TARGETTABLE    =
                'USER_WORK.LOB_INPUT_FILE_STG_ISP      ',
VARCHAR ERRORTABLE1    =
             'USER_WORK.E1_LOB_INPUT_FILE_STG_ISP      ',
VARCHAR ERRORTABLE2    =
             'USER_WORK.E2_LOB_INPUT_FILE_STG_ISP      ',
VARCHAR WORKTABLE      =
             'USER_WORK.WT_LOB_INPUT_FILE_STG_ISP      ',
VARCHAR LOGTABLE       =
             'USER_WORK.LT_LOB_INPUT_FILE_STG_ISP      '
);
STEP SETUP
(
APPLY
('DROP TABLE  USER_WORK.E1_LOB_INPUT_FILE_STG_ISP      ;'),
('DROP TABLE  USER_WORK.E2_LOB_INPUT_FILE_STG_ISP      ;'),
('DROP TABLE  USER_WORK.WT_LOB_INPUT_FILE_STG_ISP      ;'),
('DROP TABLE  USER_WORK.LT_LOB_INPUT_FILE_STG_ISP      ;'),
('DROP TABLE     USER_WORK.LOB_INPUT_FILE_STG_ISP      ;'),
('RELEASE MLOAD  USER_WORK.LOB_INPUT_FILE_STG_ISP      ;'),
('DELETE FROM    USER_WORK.LOB_INPUT_FILE_STG_ISP      ;'),
('CREATE MULTISET TABLE
                 USER_WORK.LOB_INPUT_FILE_STG_ISP      ,
    NO FALLBACK ,
    NO BEFORE JOURNAL,
    NO AFTER JOURNAL,
    CHECKSUM = DEFAULT
    (
   PCD CHAR(12) CHARACTER SET LATIN NOT CASESPECIFIC,
   RCD CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC,
   RCDD CHAR(60) CHARACTER SET LATIN NOT CASESPECIFIC,
   FNAME CHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,
   MNAME CHAR(13) CHARACTER SET LATIN NOT CASESPECIFIC,
   LNAME CHAR(25) CHARACTER SET LATIN NOT CASESPECIFIC,
   NSUFFIX CHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC,
   ADDRESS1 CHAR(40) CHARACTER SET LATIN NOT CASESPECIFIC,
   ADDRESS2 CHAR(40) CHARACTER SET LATIN NOT CASESPECIFIC,
   CITY CHAR(25) CHARACTER SET LATIN NOT CASESPECIFIC,
   STATE CHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC,
   ZIP CHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC,
   SSN CHAR(9) CHARACTER SET LATIN NOT CASESPECIFIC,
   ANUM CHAR(18) CHARACTER SET LATIN NOT CASESPECIFIC,
   LFLAG CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC,
   DYYYYMM DATE FORMAT ''YYYYMM'',
   SNUM INTEGER,
   LPASS1 CHAR(18) CHARACTER SET LATIN NOT CASESPECIFIC,
   LPASS2 CHAR(18) CHARACTER SET LATIN NOT CASESPECIFIC,
   LPASS3 CHAR(18) CHARACTER SET LATIN NOT CASESPECIFIC,
   LPASS4 CHAR(18) CHARACTER SET LATIN NOT CASESPECIFIC,
   LPASS5 CHAR(18) CHARACTER SET LATIN NOT CASESPECIFIC,
   FRCIND CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC,
   PID DECIMAL(11,0) COMPRESS ,
   APPID         CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC,
   CIND CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC,
   DYYYYMMDD1 DATE FORMAT ''YYYYMMDD'',
   FILLER CHAR(19) CHARACTER SET LATIN NOT CASESPECIFIC,
   START_DATE DATE FORMAT ''YYYY-MM-DD'',
   END_DATE DATE FORMAT ''YYYY-MM-DD'',
   RFLAGALL CHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC,
   RDESC CHAR(150) CHARACTER SET LATIN NOT CASESPECIFIC,
   DYYYYMMDD2   DATE FORMAT ''YYYYMMDD'',
   BAL11        DECIMAL(11,0) COMPRESS,
   BYPAS CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC
   )
 PRIMARY INDEX (DYYYYMM,LFLAG,SNUM); ')
TO OPERATOR (DDL_OPERATOR () );
);
STEP LOAD_DATA
(
 APPLY
 ('INSERT INTO USER_WORK.LOB_INPUT_FILE_STG_ISP
   (
  FNAME
 ,MNAME
 ,LNAME
 ,NSUFFIX
 ,ADDRESS1
 ,ADDRESS2
 ,CITY
 ,STATE
 ,ZIP
 ,SSN
 ,ANUM
 ,LFLAG
 ,DYYYYMM
 ,SNUM
 ,LPASS1
 ,LPASS2
 ,LPASS3
 ,LPASS4
 ,LPASS5
 ,FRCIND
 ,PID
 ,APPID
 ,CIND
 ,DYYYYMMDD1
 ,FILLER
 ,DYYYYMMDD2
 ,BAL11
 ,BYPAS
 )
 VALUES
 (
  CASE WHEN :FNAME <> '' '' THEN
       CAST(:FNAME        AS CHAR(20))
           ELSE NULL
           END
 ,CASE WHEN :MNAME <> '' '' THEN
       CAST(:MNAME        AS CHAR(13))
           ELSE NULL
           END
 ,CASE WHEN :LNAME <> '' '' THEN
       CAST(:LNAME AS CHAR(25))
           ELSE NULL
           END
 ,CASE WHEN :NSUFFIX <> '' '' THEN
       CAST(:NSUFFIX AS CHAR(5))
           ELSE NULL
           END
 ,CASE WHEN :ADDRESS1 <> '' '' THEN
       CAST(:ADDRESS1 AS CHAR(40))
           ELSE NULL
           END
 ,CASE WHEN :ADDRESS2 <> '' '' THEN
       CAST(:ADDRESS2 AS CHAR(40))
           ELSE NULL
           END
 ,CASE WHEN :CITY <> '' '' THEN
       CAST(:CITY AS CHAR(25))
           ELSE NULL
           END
 ,CASE WHEN :STATE <> '' '' THEN
       CAST(:STATE AS CHAR(2))
           ELSE NULL
           END
 ,CASE WHEN :ZIP <> '' '' THEN
       CAST(:ZIP AS CHAR(5))
           ELSE NULL
           END
 ,CASE WHEN :SSN <> '' '' THEN
       CAST(:SSN AS CHAR(9))
           ELSE NULL
           END
 ,CASE WHEN :ANUM <> '' '' THEN
       CAST(:ANUM AS CHAR(18))
           ELSE NULL
           END
 ,CASE WHEN :LFLAG <> '' '' THEN
       CAST(:LFLAG AS CHAR(1))
           ELSE NULL
           END
 ,CASE WHEN :DYYYYMM <> '' '' THEN
       CAST(:DYYYYMM AS DATE FORMAT ''YYYYMM'')
           ELSE NULL
           END
 ,CASE WHEN :SNUM <> '' '' THEN
       CAST(:SNUM AS DECIMAL(8))
           ELSE NULL
           END
 ,CASE WHEN :LPASS1 <> '' '' THEN
       CAST(:LPASS1 AS CHAR(18))
           ELSE NULL
           END
 ,CASE WHEN :LPASS2 <> '' '' THEN
       CAST(:LPASS2 AS CHAR(18))
           ELSE NULL
           END
 ,CASE WHEN :LPASS3 <> '' '' THEN
       CAST(:LPASS3 AS CHAR(18))
           ELSE NULL
           END
 ,CASE WHEN :LPASS4 <> '' '' THEN
       CAST(:LPASS4 AS CHAR(18))
           ELSE NULL
           END
 ,CASE WHEN :LPASS5 <> '' '' THEN
       CAST(:LPASS5 AS CHAR(18))
           ELSE NULL
           END
 ,CASE WHEN :FRCIND <> '' '' THEN
       CAST(:FRCIND AS CHAR(1))
           ELSE ''N''
           END
 ,CASE WHEN :PID <> '' '' THEN
       CAST(:PID AS CHAR(11))
           ELSE NULL
           END
 ,CASE WHEN :APPID <> '' '' THEN
       CAST(:APPID AS CHAR(3))
           ELSE NULL
           END
 ,CASE WHEN :CIND <> '' '' THEN
       CAST(:CIND AS CHAR(1))
           ELSE NULL
           END
 ,CASE WHEN :DYYYYMMDD1 <> '' '' THEN
       CAST(:DYYYYMMDD1 AS DATE FORMAT ''YYYYMMDD'')
           ELSE NULL
           END
 ,CASE WHEN :FILLER <> '' '' THEN
       CAST(:FILLER AS CHAR(9))
           ELSE NULL
           END
 ,CASE WHEN :DYYYYMMDD2 <> '' '' THEN
       CAST(:DYYYYMMDD2 AS DATE FORMAT ''YYYYMMDD'')
           ELSE NULL
           END
 ,CASE WHEN :BAL11 <> '' '' THEN
       CAST(:BAL11 AS DECIMAL(11,0))
           ELSE NULL
           END
 ,CASE WHEN :BYPAS <> '' '' THEN
       CAST(:BYPAS AS CHAR(1))
           ELSE NULL
           END
  );'
 )
  TO OPERATOR (UPDATE_OPERATOR() )
  SELECT * FROM OPERATOR(READ_OPERATOR() );
  );
  );
 
.LOGOFF;
.QUIT;
//*------------------------------------------------------------------*//
//JS0020  EXEC PGM=TLOGVIEW,REGION=0M,
// PARM='-f ''*'' -g'
//JOBLOG   DD  DISP=SHR,DSN=HLQ.JOBLOG
//SYSPRINT DD  SYSOUT=*
//SYSOUT   DD  SYSOUT=*
//SYSABEND DD  SYSOUT=G
//SYSTERM  DD  SYSOUT=*

 

Log

 Teradata Parallel Transporter Version 15.10.01.01 32-Bit
 Teradata Parallel Transporter SQL DDL Operator Version 15.10.01.01
 DDL_OPERATOR: private log specified: DDLLOG
 DDL_OPERATOR: connecting sessions
 DDL_OPERATOR: sending SQL requests
 DDL_OPERATOR: TPT10508: RDBMS error 3807: Object 'USER_WORK.LOB_INPUT_FILE_STG_ISP' does not exist.
 DDL_OPERATOR: TPT18046: Error is ignored as requested in ErrorList
 DDL_OPERATOR: TPT10508: RDBMS error 3807: Object 'USER_WORK.LOB_INPUT_FILE_STG_ISP' does not exist.
 DDL_OPERATOR: TPT18046: Error is ignored as requested in ErrorList
 DDL_OPERATOR: disconnecting sessions
 DDL_OPERATOR: Total processor time used = '0.00592 Second(s)'
 DDL_OPERATOR: Start : Thu May 11 12:40:51 2017
 DDL_OPERATOR: End   : Thu May 11 12:40:55 2017
 Job step SETUP completed successfully
 Teradata Parallel Transporter DataConnector Operator Version 15.10.01.01
 Teradata Parallel Transporter Update Operator Version 15.10.01.01
 UPDATE_OPERATOR: private log specified: UP_LOG
 READ_OPERATOR[1]: Instance 1 directing private log report to 'READLOG-1'.
 READ_OPERATOR[1]: DataConnector Producer operator Instances: 1
 UPDATE_OPERATOR: connecting sessions
 READ_OPERATOR[1]: ECI operator ID: 'READ_OPERATOR-197999'
 READ_OPERATOR[1]: Operator instance 1 processing file 'DD:DATAIN'.
 UPDATE_OPERATOR: preparing target table(s)
 UPDATE_OPERATOR: entering DML Phase
 UPDATE_OPERATOR: entering Acquisition Phase
 UPDATE_OPERATOR: entering Application Phase
 UPDATE_OPERATOR: Statistics for Target Table:  'USER_WORK.LOB_INPUT_FILE_STG_ISP      '
 UPDATE_OPERATOR: Rows Inserted: 15
 UPDATE_OPERATOR: Rows Updated:  0
 UPDATE_OPERATOR: Rows Deleted:  0
 UPDATE_OPERATOR: entering Cleanup Phase
 UPDATE_OPERATOR: Error Table Statistics for Target Table :   'USER_WORK.LOB_INPUT_FILE_STG_ISP      '
 UPDATE_OPERATOR: Total Rows in Error Table 1:   3
 UPDATE_OPERATOR: Total Rows in Error Table 2:   1
 UPDATE_OPERATOR: disconnecting sessions
 READ_OPERATOR[1]: Total files processed: 1.
 UPDATE_OPERATOR: Total processor time used = '0.017401 Second(s)'
 UPDATE_OPERATOR: Start : Thu May 11 12:40:55 2017
 READ_OPERATOR[1]: TPT19229 0 error rows sent to error file DD:DATAERR
 UPDATE_OPERATOR: End   : Thu May 11 12:41:12 2017
 Job step LOAD_DATA completed successfully
 Job NBKTXQAQ completed successfully
 Job start: Thu May 11 12:40:50 2017
 Job end:   Thu May 11 12:41:12 2017

 

 

2 REPLIES
Teradata Employee

Re: RecordErrorFileName

RecordErrorFileName attribute will only capture  error records  that include those with either incorrect column counts or individual
columns with invalid lengths. (Like schema mismatch errors). Data Conversion errors that get captured in Error table table 1 (what you have above) will not be captured on client side i.e in Error record file. 

Re: RecordErrorFileName

With this application the column invalid length, or schema mismatch will never happen, the file is a mainframe FB file with fixed columns. All records are same with no segemented data or VB data. If FNAME is in position 1-20 in first record it will be in same position until EOF never changing.    

 

If the schema doesnt match the input file TPT usually puts out message TPT19438 pmOpen failed. LRECL does not match length defined by data schema (38) and job ends with a CC 12

 

When Error table table 1 is updated why doesnt ERRORFIELD  get updated ?