All Forums Tools
dhiraj_dt121 4 posts Joined 07/16
06 Jul 2016
teradata TPT , all rows in error table

Hi Folks, I have created TPT script, however when I run it all the rows are errored out. Below is my code and the table structure.

DEFINE            JOB MOVE_DATA_WITHOUT_LANDING_TO_DISK
                  
DESCRIPTION       'MOVE DATA WITHOUT LANDING THE DATA TO DISK'
(
                   /*** Schema Definition ***/
                  
                    DEFINE SCHEMA EMPLOYEE_SCHEMA
                    DESCRIPTION 'SAMPLE EMPLOYEE SCHEMA'
                    (
VISIT_ITEM_ID     VARCHAR(20),
VISIT_MTNG_ID     VARCHAR(20),
MTNG_SBJCT        VARCHAR(100),
MTNG_MONEY        DECIMAL(22,4),
TOT               DECIMAL(22,4),
ACCMPNY           VARCHAR(200),
MTNG_ADDR         VARCHAR(100),
NOTE              VARCHAR(2000),
START_MTNG_DT     VARCHAR(50),
IS_USE_CARD       VARCHAR(10),
CARD_RSN_ID       DECIMAL(4,0),
ATTEND_PRSN_NUM   DECIMAL(22,4),
IS_SPPLY_WARTER   VARCHAR(10),
MTNG_AMT          DECIMAL(22,4),
PER_EXP_AMT       DECIMAL(22,4),
MSD_CARD_TRANS_ID DECIMAL(22,4),
BSNSS_PRPS        VARCHAR(100),
RSN_DTL           VARCHAR(150),
UPLD_DT           TIMESTAMP(0),
SRC_ID            VARCHAR(10)
                    );
                  
                  /*** Export Operator Definition ***/
                  
                    DEFINE OPERATOR EXPORT_OPERATOR
                   DESCRIPTION 'TERADATA PARALLEL TRANSPORTER EXPORT OPERATOR'
                    TYPE EXPORT
                    SCHEMA EMPLOYEE_SCHEMA
                    ATTRIBUTES
                    (
                       VARCHAR PrivateLogName = '/home/cnbietl/tgt.log',
                  
                       INTEGER MaxSessions    =  32,
                  
                       INTEGER MinSessions,
	                   
	                   MaxDecimalDigits		 =  31,
                  
                       VARCHAR TdpId          = 'TDCPRD01',
                  
                       VARCHAR UserName       = 'APCNNGBI_MEDEDETL',
                  
                       VARCHAR UserPassword   = password,
                  
                       VARCHAR SelectStmt     = 'SELECT * FROM APCNNGBI_MEDED.EVT_VISIT_MTNG_ITEM'
	                   
                    );
                    
                    /*** Load Operator Definition ***/
DEFINE            OPERATOR LOAD_OPERATOR
                  
                    DESCRIPTION 'TERADATA PARALLEL TRANSPORTER LOAD OPERATOR'
                  
                    TYPE LOAD
                  
                    SCHEMA EMPLOYEE_SCHEMA
                  
                    ATTRIBUTES
                  
                    (
                  
                       VARCHAR PrivateLogName = '/home/cnbietl/src.log',
                  
                       INTEGER MaxSessions    = 16,
                  
                       INTEGER MinSessions,
                  
                       VARCHAR TargetTable    = 'APCNNGBI_MEDED.EVT_VISIT_MTNG_ITEM',
                  
                       VARCHAR TdpId          = 'TDCDEV01',
                  
                       VARCHAR UserName       = 'APCNNGBI_MEDED_ADMIN',
                  
                       VARCHAR UserPassword   = password,
                  
                       VARCHAR ErrorTable1    = 'APCNNGBI_MEDED_WORK.LOADOPER_ERRTABLE1',
                  
                       VARCHAR ErrorTable2    = 'APCNNGBI_MEDED_WORK.LOADOPER_ERRTABLE2',
                  
                       VARCHAR LogTable       = 'APCNNGBI_MEDED_WORK.LOADOPER_LOGTABLE' 
                    );
                  
                  APPLY
                     ('INSERT INTO APCNNGBI_MEDED.EVT_VISIT_MTNG_ITEM 
	(:VISIT_ITEM_ID,
:VISIT_MTNG_ID,
:MTNG_SBJCT,
:MTNG_MONEY,
:TOT,
:ACCMPNY,
:MTNG_ADDR,
:NOTE,
:START_MTNG_DT,
:IS_USE_CARD,
:CARD_RSN_ID,
:ATTEND_PRSN_NUM,
:IS_SPPLY_WARTER,
:MTNG_AMT,
:PER_EXP_AMT,
:MSD_CARD_TRANS_ID,
:BSNSS_PRPS,
:RSN_DTL,
:UPLD_DT,
:SRC_ID
	);')
                     TO OPERATOR (LOAD_OPERATOR)
                     SELECT * FROM OPERATOR (EXPORT_OPERATOR);
                  );

Table Structure : 

CREATE SET TABLE APCNNGBI_MEDED.EVT_VISIT_MTNG_ITEM ,NO FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT,

     DEFAULT MERGEBLOCKRATIO

     (

      VISIT_ITEM_ID VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'VISIT ITEM Identifier' NOT NULL,

      VISIT_MTNG_ID VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'VISIT MEETING identifier',

      MTNG_SBJCT VARCHAR(100) CHARACTER SET UNICODE NOT CASESPECIFIC TITLE 'MEETING SUBJECT',

      MTNG_MONEY DECIMAL(22,4) TITLE 'MEETING MONEY',

      TOT DECIMAL(22,4) TITLE 'TOTAL',

      ACCMPNY VARCHAR(200) CHARACTER SET UNICODE NOT CASESPECIFIC TITLE 'ACCOMPANY',

      MTNG_ADDR VARCHAR(100) CHARACTER SET UNICODE NOT CASESPECIFIC TITLE 'MEETING ADDRESS',

      NOTE VARCHAR(2000) CHARACTER SET UNICODE NOT CASESPECIFIC TITLE 'NOTE',

      START_MTNG_DT VARCHAR(50) CHARACTER SET UNICODE NOT CASESPECIFIC TITLE 'START MEETING DATE',

      IS_USE_CARD VARCHAR(10) CHARACTER SET UNICODE NOT CASESPECIFIC TITLE 'IS USE CARD',

      CARD_RSN_ID DECIMAL(4,0) TITLE 'CARD REASON IDENTIFIER',

      ATTEND_PRSN_NUM DECIMAL(22,4) TITLE 'ATTEND PERSON NUM',

      IS_SPPLY_WARTER VARCHAR(10) CHARACTER SET UNICODE NOT CASESPECIFIC TITLE 'IS SUPPLY WARTER',

      MTNG_AMT DECIMAL(22,4) TITLE 'MEETING AMOUNT',

      PER_EXP_AMT DECIMAL(22,4) TITLE 'PER EXP AMOUNT',

      MSD_CARD_TRANS_ID DECIMAL(22,4) TITLE 'MSD CARD TRANsaction Identifier',

      BSNSS_PRPS VARCHAR(100) CHARACTER SET UNICODE NOT CASESPECIFIC TITLE 'BUSINESS PURPOSE',

      RSN_DTL VARCHAR(150) CHARACTER SET UNICODE NOT CASESPECIFIC TITLE 'REASON DETAIL',

      UPLD_DT TIMESTAMP(0) TITLE 'Upload Date',

      SRC_ID VARCHAR(10) CHARACTER SET UNICODE NOT CASESPECIFIC TITLE 'SRC ID')

UNIQUE PRIMARY INDEX XPKVISIT_MEETING_ITEM ( VISIT_ITEM_ID );

feinholz 1234 posts Joined 05/08
07 Jul 2016

What version of TPT are you using?
Do you get any errors?
Or just that all rows end up in the error table?
Did you look at the error table to see on which column the error is occurring?
Just a note: private log names are just names of virtual logs within the overall job log. They are not files on a local file system. Thus no need to provide directory paths in the names.
 

--SteveF

dhiraj_dt121 4 posts Joined 07/16
07 Jul 2016

Teradata Parallel Transporter Version 14.10.00.08
No Error in the job, only that all the rows end up in error table.
Yes, I found the error iN TABLES are 

6706 NOTE

6706 ACCMPNY

6706 MTNG_ADDR

6706 BSNSS_PRPS

 

 

 
 

Fred 1096 posts Joined 08/04
08 Jul 2016

You apparently have non-LATIN data in your UNICODE columns, but are defaulting to ASCII session character set.
Specify USING CHARACTER SET UTF16 at the start of your script. Note that in SCHEMA definitions the length for VARCHAR is specified in bytes rather than characters, so you will also want to double those.

You must sign in to leave a comment.