TPT error tables

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.

TPT error tables

TPT not updating DBCErrorfield or ErrorField columns in error table 1 and 2. Is there a VARCHAR parameter to make this happen ? i have included the TPT step and log     

 

Uniqueness|  DBCErrorCode|DBCErrorField
---------- -------------- --------------
         0|          2620|        


     SourceSeq|     ErrorCode|ErrorField 
-------------- -------------- ------------
             1|          2665|           
             3|          2665|           
             2|          2665|                 

 

//*------------------------------------------------------------------*//
//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)
//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 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     =  20,
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;

1 REPLY

Re: TPT error tables

I tried to post the log but it excedded the limit