teradata TPT , all rows in error table

Tools & Utilities

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 );

Tags (1)
3 REPLIES
Teradata Employee

Re: teradata TPT , all rows in error table

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

Re: teradata TPT , all rows in error table

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

Teradata Employee

Re: teradata TPT , all rows in error table

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.