TPT Error : TPT19112 Variable (VAR) field length error.

Tools & Utilities
Highlighted
Teradata Employee

TPT Error : TPT19112 Variable (VAR) field length error.

Hi,

 

I am reading a data from named pipe. TPT gives an error that :

 

DATACONNECTOR_OPERATOR[1]: TPT19112 Variable (VAR) field length error. Expected maximum length 6, received length 10 in record 1.
DATACONNECTOR_OPERATOR[1]: TPT19015 TPT Exit code set to 12.

 

TPT script is :

 

DEFINE JOB "CUSTOMER_1525782274188"
DESCRIPTION 'Data Migration Task'
(
DEFINE SCHEMA SOURCE_SCHEMA
DESCRIPTION 'SCHEMA INFORMATION'
(
"CUSTOMER_ID_0" VARCHAR (64),
"CUSTOMER_TYPE_ID_1" VARCHAR (64),
"FIRST_NAME_2" VARCHAR (404),
"LAST_NAME_3" VARCHAR (404),
"LEGAL_ENTITY_NAME_4" VARCHAR (404),
"CUSTOMER_NAME_5" VARCHAR (404),
"VAT_CODE_6" VARCHAR (64),
"PARENT_CUSTOMER_ID_7" VARCHAR (64),
"CUSTOMER_STATUS_8" VARCHAR (24),
"DATE_OF_BIRTH_9" VARCHAR (64),
"AGE_10" VARCHAR (64),
"GENDER_11" VARCHAR (6),
"ADDITION_TYPE_12" VARCHAR (104),
"NUMBER_OF_EMPLOYEES_13" VARCHAR (64),
"CONTACT_NAME_14" VARCHAR (404),
"CONTACT_ADDRESS_15" VARCHAR (1004),
"CONTACT_CITY_16" VARCHAR (204),
"CONTACT_POSTAL_CODE_17" VARCHAR (64),
"CONTACT_TEL_NUMBER_18" VARCHAR (104),
"CONTACT_FAX_NUMBER_19" VARCHAR (104),
"CONTACT_EMAIL_ADDRESS_20" VARCHAR (404),
"CUSTOMER_TYPE_21" VARCHAR (6),
"CUSTOMER_INDUSTRY_ID_22" VARCHAR (64),
"DISADVANTAGEOUS_CUSTOMER_23" VARCHAR (404),
"MANAGER_RESPONSIBLE_24" VARCHAR (204),
"DATE_CREATED_25" VARCHAR (64),
"DATE_MODIFIED_26" VARCHAR (64),
"ETL_CYLE_ID_27" VARCHAR (64),
"NOTE_28" VARCHAR (204),
"INS_TIMESTAMP_29" VARCHAR (64)
);
DEFINE OPERATOR DDL_OPERATOR()
DESCRIPTION 'TERADATA PARALLEL TRANSPORTER DDL Operator'
TYPE DDL
ATTRIBUTES
(
VARCHAR TdpId = '192.168.163.133',
VARCHAR UserName = 'wqs',
VARCHAR UserPassword = 'wqs',
VARCHAR WorkingDatabase = 'wqs',
VARCHAR AccountID,
VARCHAR DateForm,
VARCHAR ARRAY ErrorList = ['3706','3803','3807'],
VARCHAR TraceLevel = [ 'NONE' ],
VARCHAR PrivateLogName = 'Task5216-pvt'
);
DEFINE OPERATOR LOAD_OPERATOR
DESCRIPTION 'TERADATA PARALLEL TRANSPORTER LOAD OPERATOR'
TYPE LOAD
SCHEMA *
ATTRIBUTES
(
VARCHAR TdpId = '192.168.163.133',
VARCHAR UserName = 'wqs',
VARCHAR UserPassword = 'wqs',
VARCHAR WorkingDatabase = 'wqs',
VARCHAR TraceLevel = [ 'NONE' ],
VARCHAR PrivateLogName = 'Task5216-pvt',
VARCHAR TargetTable = 'CUSTOMER',
VARCHAR ErrorTable1 = 'wqs.CUSTOMER_ET5216',
VARCHAR ErrorTable2 = 'wqs.CUSTOMER_UT5216',
VARCHAR LogTable = 'wqs.CUSTOMER_LT5216',
INTEGER MaxSessions = 1,
VARCHAR CheckpointRowCount = 'Y',
INTEGER TenacityHours = 2,
VARCHAR TruncateColumnData = 'No',
VARCHAR RecordErrorVerbosity = 'Off' ,
INTEGER TenacitySleep = 10
);
DEFINE OPERATOR DATACONNECTOR_OPERATOR
DESCRIPTION 'TERADATA PARALLEL TRANSPORTER DataConnector Operator'
TYPE DATACONNECTOR PRODUCER
SCHEMA SOURCE_SCHEMA
ATTRIBUTES
(
VARCHAR PrivateLogName = 'Task5216-pvt',
VARCHAR MultipleReaders = 'No' ,
VARCHAR FileName = '\\.\pipe\tmadatapipe-5216-9940',
VARCHAR TraceLevel = 'NONE',
VARCHAR RowsPerInstance = '1000' ,
VARCHAR NullColumns = 'No' ,
VARCHAR AcceptExcessColumns = 'No' ,
VARCHAR AcceptMissingColumns = 'No' ,
VARCHAR Format = 'Binary',
VARCHAR IndicatorMode = 'Y',
VARCHAR OpenMode = 'Read',
VARCHAR AccessModuleName = 'np_axsmod.dll'
);
STEP DO_SETUP
(
APPLY
('DELETE FROM "CUSTOMER"; '),
('DROP TABLE "wqs.CUSTOMER_ET5216"; '),
('DROP TABLE "wqs.CUSTOMER_UT5216"; '),
('DROP TABLE "wqs.CUSTOMER_LT5216"; ')
TO OPERATOR ( DDL_OPERATOR [1] );
);
STEP DO_LOAD
(
APPLY
('INSERT INTO "CUSTOMER" ("CUSTOMER_ID", "CUSTOMER_TYPE_ID", "FIRST_NAME", "LAST_NAME", "LEGAL_ENTITY_NAME", "CUSTOMER_NAME", "VAT_CODE", "PARENT_CUSTOMER_ID", "CUSTOMER_STATUS", "DATE_OF_BIRTH", "AGE", "GENDER", "ADDITION_TYPE", "NUMBER_OF_EMPLOYEES", "CONTACT_NAME", "CONTACT_ADDRESS", "CONTACT_CITY", "CONTACT_POSTAL_CODE", "CONTACT_TEL_NUMBER", "CONTACT_FAX_NUMBER", "CONTACT_EMAIL_ADDRESS", "CUSTOMER_TYPE", "CUSTOMER_INDUSTRY_ID", "DISADVANTAGEOUS_CUSTOMER", "MANAGER_RESPONSIBLE", "DATE_CREATED", "DATE_MODIFIED", "ETL_CYLE_ID", "NOTE", "INS_TIMESTAMP") values (
:"CUSTOMER_ID_0", :"CUSTOMER_TYPE_ID_1", :"FIRST_NAME_2", :"LAST_NAME_3", :"LEGAL_ENTITY_NAME_4", :"CUSTOMER_NAME_5", :"VAT_CODE_6", :"PARENT_CUSTOMER_ID_7", :"CUSTOMER_STATUS_8", :"DATE_OF_BIRTH_9", :"AGE_10", :"GENDER_11", :"ADDITION_TYPE_12", :"NUMBER_OF_EMPLOYEES_13", :"CONTACT_NAME_14", :"CONTACT_ADDRESS_15", :"CONTACT_CITY_16", :"CONTACT_POSTAL_CODE_17", :"CONTACT_TEL_NUMBER_18", :"CONTACT_FAX_NUMBER_19", :"CONTACT_EMAIL_ADDRESS_20", :"CUSTOMER_TYPE_21", :"CUSTOMER_INDUSTRY_ID_22", :"DISADVANTAGEOUS_CUSTOMER_23", :"MANAGER_RESPONSIBLE_24", :"DATE_CREATED_25", :"DATE_MODIFIED_26", :"ETL_CYLE_ID_27", :"NOTE_28", :"INS_TIMESTAMP_29");')
IGNORE DUPLICATE UPDATE ROWS
IGNORE DUPLICATE INSERT ROWS
TO OPERATOR (LOAD_OPERATOR [1])
SELECT * FROM OPERATOR (DATACONNECTOR_OPERATOR [1]);
);
);

 

Data is read from oracle source systems. I don't have any CLOB/BLOB columns in my table.

 

Can anyone please help?


Accepted Solutions
Teradata Employee

Re: TPT Error : TPT19112 Variable (VAR) field length error.

Solved the issue. The issue was that in selecting the source data, the order of columns was not correct. So I was defining the TPT schema and telling TPT that it will first encounter Customer_ID, then Customer_Type_ID and so on. But on source side, I was selecting FIRST_NAME, LAST_NAME ... etc. So schema was mismatching. I did that query correct and it start working.

1 ACCEPTED SOLUTION
4 REPLIES
Teradata Employee

Re: TPT Error : TPT19112 Variable (VAR) field length error.

How are you pulling the data out of Oracle?

You specified Format='binary' and IndicatorMode='Y', which means the DC operator is expecting the data to be in binary format, with indicator bytes preceding each data row, and each column having a 2-byte field length.

Typically, data is not pulled from Oracle in that fashion.

If the data is going direct from Oracle to Teradata, have you considered using the ODBC operator for TPT?

 

-- SteveF
Teradata Employee

Re: TPT Error : TPT19112 Variable (VAR) field length error.

We have written a C++ utility which is writing a data from oracle source to named pipes. TPT reads the data from that named pipe and inserts into table.

Teradata Employee

Re: TPT Error : TPT19112 Variable (VAR) field length error.

If the data is going direct from Oracle to Teradata, have you considered using the ODBC operator for TPT?

It will save you from having to write an application to pull from Oracle, and will save you from having to use named pipes.

 

Can you post the hex dump of the first record that you are sending through the named pipe?

What version of TPT are you running? 

What is the platform?

Please re-run the job, setting the TraceLevel attribute of the DC operator to 'all', and send me the log file.

 

Your schema has 2 columns defined as VARCHAR(6), and so it looks like the data for one of those columns exceeded the max size.

 

-- SteveF
Teradata Employee

Re: TPT Error : TPT19112 Variable (VAR) field length error.

Solved the issue. The issue was that in selecting the source data, the order of columns was not correct. So I was defining the TPT schema and telling TPT that it will first encounter Customer_ID, then Customer_Type_ID and so on. But on source side, I was selecting FIRST_NAME, LAST_NAME ... etc. So schema was mismatching. I did that query correct and it start working.