Teradata PT 12.0 error output schema does not match

Tools
Enthusiast

Teradata PT 12.0 error output schema does not match

Objective: Export a large amount of data to a pipe delimited file.

Error: Output Schema does not match data from SELECT statement

What I am doing:

I am using the TPT wizard to create and run the job. As I go through the steps in TPT, when I get to the part of selecting what columns I want, I click the 'select all' option. The format of the columns that I am selecting ranges from char to int to decimal. After selecting all, I then proceed to change the type to varchar. I am changing the type because I read in the manual that if you are exporting to a delimited file, all columns need to be changed to varchars. The problem is when I run the job, anything that wasn't already a varchar causes the above error if it is selected. I am assuming I am doing something wrong in the TPT script but I figured I would ask.
24 REPLIES
Enthusiast

Re: Teradata PT 12.0 error output schema does not match

I ran into a similar situation yesterday. Here are my suggestions:
1) Use TPT wizard (Windows 32 bit) to create template. After all it is done, you will see a dialog box with four major sections. Verify that field names are mentioned on the top half portion, towards end of it you will see a select statement.
2) Go to section "DEFINE SCHEMA" and change all fields to varchar, even for char fields.
3) Go to section "SELECT * FROM OPERATOR" and change SQL select statement by casting each field to varchar. Of course, if that field is already a varchar, no need to cast.
4) If you prefer, change login / password in this script.
5) Run this for a condition that gives no rows to see if there is any problem with this script.

Make sure to convert every field in the table to varchar. TPT looked very fast in exporting and quick to respond back. I received something close to a million rows in a second.

Here is a small section of SQL in TPT template or script:

UserName = 'yourUserName',
UserPassword = 'yourUserPassword',
SelectStmt = 'SELECT cast(TXN_ID as varchar(30)),
OTHER LINES
cast(CASHBACK_AMOUNT as varchar(20)),
cast(TRANSACTION_AMOUNT as varchar(20))
FROM yourdbName.yourBigFactTable where issuer >= 310;',
Teradata Employee

Re: Teradata PT 12.0 error output schema does not match

If you want to use the Export operator to extract the data from Teradata, then yes you need to CAST the columns in the SELECT statement being sent to Teradata. The Export operator utilizes the FastExport protocol and that protocol cannot extract the data properly in its native form.

Otherwise, use the Selector operator. This operator extracts the data in field mode (similar to what BTEQ would do) and there would be no need to CAST the columns in the SELECT statement.
-- SteveF
Enthusiast

Re: Teradata PT 12.0 error output schema does not match

Is there any where we can get the manual on Error messages and resolution on TPT. AS these are errors seems to little weird as compare to Teradata Fload/Mload errors. Appreciate your response..!!
Teradata Employee

Re: Teradata PT 12.0 error output schema does not match

The error message manual for TPT is a work in progress. We have started it (in 13.10) but not all errors are in there. We will be adding them in each release.

I agree with you that some errors seem "weird", although I suppose I would classify them as "different".

Please remember that even though TPT uses the same extracting and loading "protocols" when running jobs, TPT is not the same as FastLoad or MultiLoad. It is an entirely different product (different code).

The error is actually quite explanatory. If the SELECT statement did not CAST all of the columns to VARCHAR, then the data types for the columns returned by the SELECT statement would not match what was provided in the script's SCHEMA object.
-- SteveF

Re: Teradata PT 12.0 error output schema does not match

I am getting the same error however I am not trying to make any changes. Exporting from Teradata loading to Teradata, select all. Only caveate is I am using max decimal 38. Are large decimals not supported? Do I need to change them to char?
Enthusiast

Re: Teradata PT 12.0 error output schema does not match

When you are using Large Decimals in TPT, you have to specify the attribute "MaxDecimalDigits = 38" in the appropriate operator definition.
Enthusiast

Re: Teradata PT 12.0 error output schema does not match

I got the same error but it worked with cast, but it did not work with coalesce.Example as below
Worked:
cast(Base.SUB_ACCT_NO_OCI as VARCHAR(16))
,cast(Base.SERV_ID_OCI as VARCHAR(10))
Did not worked:
coalesce(cast(Base.SUB_ACCT_NO_OCI as VARCHAR(16)) ,'')
,coalesce(cast(Base.SERV_ID_OCI as VARCHAR(10)),'')
or
cast(coalesce(Base.SUB_ACCT_NO_OCI,'') as VARCHAR(16))
,cast(coalesce(Base.SERV_ID_OCI,'') as VARCHAR(10))

Advises are highly appreciated.Thanks.rameshg2521@gmail.com
Enthusiast

Re: Teradata PT 12.0 error output schema does not match

Do we have TPT error message manual available? I am trying to figure out what following error means:

TPT_INFRA: TPT01036: Error: Task (TaskID: 4, Task Name: SELECT_2Ý0001¨) terminated due to the receipt of signal number 9
Teradata Employee

Re: Teradata PT 12.0 error output schema does not match

That error message seems to indicate that the "exporting" operator (the producer operator) appears to have coredumped for some reason.
I would need more information than just that one line to better decipher what is going on.
I would need (at a minimum):
version of TPT
script
console output
entire job log
-- SteveF