EXPORT_OPERATOR: TPT12108: Output Schema does not match data from SELECT statement

Tools

EXPORT_OPERATOR: TPT12108: Output Schema does not match data from SELECT statement

Hi ,

The subject might be old, but I was unable to figure out my solution, so please take a look once.

Trying to TPT using the below Script :

USING CHAR SET UTF8
DEFINE JOB MOVE_DATA_WITHOUT_LANDING_TO_DISK

DESCRIPTION 'MOVE DATA WITHOUT LANDING THE DATA TO DISK'

(

/*** Schema Definition ***/

DEFINE SCHEMA ASSIGN_RESPONSE_INFO_GBMYS

DESCRIPTION 'ASSIGN_RESPONSE_INFO_GBMYS.TPT Data Dump for EMEIA'
(
Asgnmt_Ref_Id VARCHAR(100),
Snc_Ref_Id VARCHAR(100),
Project_Cd VARCHAR(100),
Project_Name VARCHAR(1000),
Project_Start_Ts VARCHAR(100),
Project_Start_Dt VARCHAR(100),
Project_End_Ts VARCHAR(100),
Project_End_Dt VARCHAR(100),
Question_Ref_Id VARCHAR(100),
Response_Raw_Txt VARCHAR(24000),
Response_Raw_Nr VARCHAR(100),
Response_Potential_Score_Nr VARCHAR(100),
Response_Actual_Score_Nr VARCHAR(100),
Question_Comment_Txt VARCHAR(24000),
Questnr_Ref_Id VARCHAR(100)
);

DEFINE OPERATOR WRITE_TO_FILE

DESCRIPTION 'DATACONNECTOR OPERATOR FOR TERADATA PARALLEL TRANSPORTER'

TYPE DATACONNECTOR CONSUMER

SCHEMA ASSIGN_RESPONSE_INFO_GBMYS

ATTRIBUTES
(
VARCHAR AccessModuleName,
VARCHAR AccessModuleInitStr,
INTEGER BlockSize,
VARCHAR FileName,
VARCHAR Format='Delimited',
VARCHAR OpenMode='Write',
VARCHAR TextDelimiter =',',
VARCHAR PrivateLogName
);

/*** Export Operator Definition ***/

DEFINE OPERATOR EXPORT_OPERATOR

DESCRIPTION 'TERADATA PARALLEL TRANSPORTER EXPORT OPERATOR'

TYPE EXPORT

SCHEMA ASSIGN_RESPONSE_INFO_GBMYS

ATTRIBUTES

(

VARCHAR PrivateLogName = 'exportoper_privatelog',
INTEGER MaxSessions = 8,
INTEGER MinSessions = 4,
VARCHAR TdpId = 'EDWIN',
VARCHAR DateForm = 'ANSIDATE',
VARCHAR UserName = 'c1509587',
VARCHAR UserPassword = 'BikiEdwin1#',

VARCHAR SelectStmt='SELECT
''''''''||TRIM(CAST(Asgnmt_Ref_Id AS VARCHAR(100)))||'''''''',
''''''''||TRIM(CAST(Snc_Ref_Id AS VARCHAR(100)))||'''''''',
''''''''||TRIM(CAST(Project_Cd AS VARCHAR(100)))||'''''''',
''''''''||TRIM(CAST(Project_Name AS VARCHAR(1000)))||'''''''',
''''''''||TRIM(CAST(Project_Start_Ts AS VARCHAR(100)))||'''''''',
''''''''||TRIM(CAST(Project_Start_Dt AS VARCHAR(100)))||'''''''',
''''''''||TRIM(CAST(Project_End_Ts AS VARCHAR(100)))||'''''''',
''''''''||TRIM(CAST(Project_End_Dt AS VARCHAR(100)))||'''''''',
''''''''||TRIM(CAST(Question_Ref_Id AS VARCHAR(100)))||'''''''',
''''''''||TRIM(CAST(Response_Raw_Txt AS VARCHAR(24000)))||'''''''',
''''''''||TRIM(CAST(Response_Raw_Nr AS VARCHAR(100)))||'''''''',
''''''''||TRIM(CAST(Response_Potential_Score_Nr AS VARCHAR(100)))||'''''''',
''''''''||TRIM(CAST(Response_Actual_Score_Nr AS VARCHAR(100)))||'''''''',
''''''''||TRIM(CAST(Question_Comment_Txt AS VARCHAR(24000)))||'''''''',
''''''''||TRIM(CAST(Questnr_Ref_Id AS VARCHAR(100)))||''''''''
from CHNLSLS_MYS.ASSIGN_RESPONSE_INFO_GBMYS
'
);

LOAD INTO OPERATOR
(
WRITE_TO_FILE
ATTRIBUTES
(
FileName = 'ASSIGN_RESPONSE_INFO_GBMYS.csv',
Format = 'Delimited'
)
)
SELECT * FROM OPERATOR (EXPORT_OPERATOR);

);

Below is the message generated by the utility :

EXPORT_OPERATOR: connecting sessions

EXPORT_OPERATOR: TPT12108: Output Schema does not match data from SELECT statement

EXPORT_OPERATOR: disconnecting sessions

EXPORT_OPERATOR: Total processor time used = '0.043247 Second(s)'

My best guess is the usage of '' in Select Statement is the source of bug, as without using that I ran it successfully.

Is there any other way I can wrap each value inside single quotes.

Any insight will be appreciated.

Best Wishes,

Minat

15 REPLIES
Teradata Employee

Re: EXPORT_OPERATOR: TPT12108: Output Schema does not match data from SELECT statement

First of all, when you use a character set of UTF8, the sizes of the CHAR/VARCHAR columns must be 3x the definition of the columns in Teradata. That is because the size of the column in Teradata (and SQL) is in terms of characters, and TPT is in terms of bytes. And a single character can be anywhere from 1-3 bytes when the character set is UTF8.

(And when you use UTF16, the size must be 2x.)

So, your VARCHAR(100) in the schema definition must be VARCHAR(300).

However, I see an issue you will have with those VARCHAR(24000) columns, as tripling them will make the schema exceed 64000 bytes. Is that the true size of the column in the table?

Next, what version of TPT are you using?

In 14.10 the file writer (DC operator) will convert the data for you and you do not need to use a schema of all CHAR/VARCHAR columns and we can still write the data out in delimited manner.

-- SteveF
N/A

Re: EXPORT_OPERATOR: TPT12108: Output Schema does not match data from SELECT statement

After reading these posts and did numerous trial, I still can't figure out a solution for this same old error.

I use BIDS 2008 R2 to load a Teradata view to a SQL destination table. My TPT API is 13.10 R1(power user).  Used MSFT Connector for Teradata by Attunity v1.2.  I used SQL command to retrieve source data.

I can run the task through in BIDS when I cast all non varchar columns to varchar in the SQL command. But when adding a column which is varchar type  at source (I don't have access to the Teradata table, and can see via Teradata SQL Assistant the column is a varchar (40)). The task always fail. I tried to cast this column to varchar(40),varchar(80),varchar(120), none works.

Any help?

YJ

Teradata Employee

Re: EXPORT_OPERATOR: TPT12108: Output Schema does not match data from SELECT statement

The only check we make is a comparison between the the result of the SELECT statement and the schema definition (the target table definition is immaterial; the DBS will do any necessary conversions).

Apparently, the result of the SELECT statement (with the CASTs) and the schema do not match.

The only solution is for you to turn on trace (add VARCHAR TraceLevel = 'all') to the Export operator and send me the script and the entire binary log (the .out file), to steven.feinholz@teradata.com.

-- SteveF

Re: EXPORT_OPERATOR: TPT12108: Output Schema does not match data from SELECT statement

Hi SteveF

I am having a similar issue to Yajing. Can I receive some feedback on where to troubleshoot next? 

Informatica Version : PowerExchange for Teradata PT Reader Plugin Version[9.5.1]

TPT Utilities: 14.0

ERROR: Message Code: TPTRD_12108

Message: [ERROR] Type:(Teradata PT API Error), Error: (EXPORT_OPERATOR: TPT12108: Output Schema does not match data from SELECT statement)

I checked on the Informatica and Teradata forums and have tried the following changes:

1)      Increase precision on the source object and reloaded the object in the mapping. (18,2 became 19,2 on all decimal fields)

2)      Validate that the TPT Utilities are utilizing a version that will function with TPT effectively.

Here are some additional troubleshooting steps that I tried.

1)      I have a total of 3 TPT reader connections all targeting the same server but different databases.

­   - The views sourced in each connection has a variety of char, varchar, integer, and decimal fields.

­   - Every Informatica ETL code using the TPT reader connections are source to target mapping with no complex transformation.

­   - 2 out of 3 connections are working flawlessly.

­   - The connection that is not working seems to have issues on the decimal fields. When the decimal fields are removed from source table, the reader connection works successfully.

Additional Details:

1)      I am fairly new to TPT (fast export) reader, so please explain to me where I can put a script override for the TPT script.

2)      I think my issue is an issue with the encoding on the table. 

3)      Is there a way of manually set the encoding on the table to Latin through Informatica?

Thank you for responses.

Sample Source/Target Table structure

CREATE MULTISET TABLE DatabaseName.TableName ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT,
DEFAULT MERGEBLOCKRATIO
(
Field VARCHAR(35) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
Field CHAR(8) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL COMPRESS (' ','AUTO ','AUTOLIAB','AUTOPD ','CMPAL ','CMPGL ','CMPPD ','CMPPKG ','CMPPROP ','EB ','FARM ','GLUMB ','GLXS ','GLXSUMB ','IMR ','OTHER ','PAL ','PROP ','UNKNWN ','WC ','WCXS '),
Field INTEGER NOT NULL,
Field CHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL COMPRESS (' ','01','02','03','04','05','06','07','08','09','1 ','10','2 ','3 ','4 ','5 ','A ','B ','C ','D ','E ','F '),
Field DECIMAL(18,9) COMPRESS ,
Field DECIMAL(18,9) COMPRESS ,
Field DECIMAL(18,9) COMPRESS ,
Field CHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL COMPRESS (' ','01','02','03','04','05','06','07','08','09','1 ','10','2 ','3 ','4 ','5 ','A ','B ','C ','D ','E ','F '),
Field CHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL COMPRESS (' ','01','02','03','04','05','06','07','08','09','1 ','10','2 ','3 ','4 ','5 ','A ','B ','C ','D ','E ','F '),
Field CHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL COMPRESS (' ','01','02','03','04','05','06','07','08','09','1 ','10','2 ','3 ','4 ','5 ','A ','B ','C ','D ','E ','F '),
Field CHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL COMPRESS (' ','01','02','03','04','05','06','07','08','09','1 ','10','2 ','3 ','4 ','5 ','A ','B ','C ','D ','E ','F '),
Field CHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS (' ','LD','LG','LS','LT'),
Field DECIMAL(18,9) COMPRESS ,
Field DECIMAL(18,9) COMPRESS ,
Field DECIMAL(18,9) COMPRESS ,
Field CHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL COMPRESS (' ','01','02','03','04','05','06','07','08','09','1 ','10','2 ','3 ','4 ','5 ','A ','B ','C ','D ','E ','F '),
Field CHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL COMPRESS (' ','01','02','03','04','05','06','07','08','09','1 ','10','2 ','3 ','4 ','5 ','A ','B ','C ','D ','E ','F '),
Field CHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL COMPRESS (' ','01','02','03','04','05','06','07','08','09','1 ','10','2 ','3 ','4 ','5 ','A ','B ','C ','D ','E ','F '),
Field CHAR(6) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL COMPRESS (' ','0015 ','0020 ','0021 ','0022 ','0023 ','0024 ','0026 '),
Field DECIMAL(8,2) COMPRESS (0.00 ,1.00 ,-1.00 ,2.00 ),
Field DECIMAL(8,2) COMPRESS (0.00 ,1.00 ,-1.00 ,2.00 ),
Field DECIMAL(8,2) COMPRESS (0.00 ,1.00 ,-1.00 ,2.00 ),
Field DECIMAL(8,2) COMPRESS (0.00 ,1.00 ,-1.00 ,2.00 ),
Field VARCHAR(15) CHARACTER SET LATIN NOT CASESPECIFIC,
Field VARCHAR(15) CHARACTER SET LATIN NOT CASESPECIFIC,
Field DATE FORMAT 'yyyy-mm-dd',
Field DATE FORMAT 'yyyy-mm-dd')
PRIMARY INDEX TableNamePI ( Field );
Teradata Employee

Re: EXPORT_OPERATOR: TPT12108: Output Schema does not match data from SELECT statement

Unfortunately, I am not familiar with Informatica and cannot provide any information for setting options.

And I believe Informatica uses TPTAPI, which means there is no script to modify.

Thus, you will need to contact Informatica to find out how to set any type of tracing and logging.

BTW, I notice that all of your columns have character set Latin.

Am I to assume that the client session character set being used for the job is ASCII?

-- SteveF

Re: EXPORT_OPERATOR: TPT12108: Output Schema does not match data from SELECT statement

Hi Steve,

The table is set to Latin as that is our default setting for our Teradata. I was able to solve this issue by enabling high percisioning on Informatica's properties settings. What's interestng is our decimal types are (18,9) max on this source table...

Can you explain what TPTAPI is?

Thank you,

-WL

Teradata Employee

Re: EXPORT_OPERATOR: TPT12108: Output Schema does not match data from SELECT statement

TPTAPI is the method by which ETL vendors (and other applications) use TPT to load data into Teradata.

We publish the API and provide the operators so that applications can call our libraries/operators directly instead of having to write scripts.

Gives them better control over the flow of their load jobs.

-- SteveF
Enthusiast

Re: EXPORT_OPERATOR: TPT12108: Output Schema does not match data from SELECT statement

Hello Steve,

We are converting our data loads to use the TPT interface.  We're having a similar problem and wanted to find a fix.  Here's the setup:

Moving data from Teradata system A to Teradata system B.  Both are running the same DB version 13.10 (and patch).

Informatica 9.5 using TPT 14.10 

Extract: From table on System A using Informatica TPT API FastExport

Load:  To table on System B using Informatica TPT API Load

Error:  TPTRD_307 [ERROR] Type:(Teradata PT API Error), Error: (TPTAPI_INFRA: API307: Error: Conflicting data type for column(37) - item_charge. Source column's data type is (DECIMAL) Target column's data type is (FLOAT).

Research:  Confirmed that the table definitions are identical using decminal (18,2) in Teradata as well as the Informatica mapping.

Workaround:  Converted the Extract to use ODBC and the load completes.  But is significantly slower than FastExport.

Is there a way to modify the FastExport global settings not to convert to a float? 

It's not reasonable for us to convert via SQL due to the large number of tables.

Thanks for your guidance.

Teradata Employee

Re: EXPORT_OPERATOR: TPT12108: Output Schema does not match data from SELECT statement

You will have to contact Informatica. If both source and target tables are the same, I do not see how (or why) TPTAPI would report that they are not.

BTW, TPTAPI does not do any conversions.

-- SteveF