DSXchange

Hi ,

I am getting below error and data is getting truncated in 11.3 where as in 9.1 the job is running fine.

ID_ACT_Excl_Fact,0: Schema reconciliation detected a size mismatch for column columnname. When reading database column DECIMAL(31,5) into column DECIMAL(31,0), trunca
tion, loss of pre (...)

please suggest a solution. Thanks

Check the data type in the database table.
Check the data type in your job's metadata.

If you're using a Connector stage, change the "Fail on type mismatch" property from Yes to No.

If you are saying that the data was not being truncated in your 9.1 job, that would mean you were taking advantage of a bug... which has since been fixed. If you don't want the truncation, then you'll need to correct the target metadata.

I am using teradata connector stage. Fail on type mismatch is already set to NO. Job is not getting aborted but data is getting truncated.

Craig

I am casting the source field to decimal(31,5) and i have mentioned the same in connector metadata. It was running as expected in 9.1 . Do we need to set some parameters in 11.3 or something.

Please check below link and try one of the 3 suggested options

https://www.ibm.com/support/knowledgece ... RS061.html


There are generally three approaches for eliminating the schema reconciliation messages:
1.Modify column definitions on the link to match (as close as possible) the corresponding column definitions in the database table.
2.Modify column definitions in the database table to match (as close as possible) the corresponding column definitions on the link.
3.Define a message handler that demotes or suppresses the schema reconciliation messages. The message handler definition can be applied to all the jobs in the project or to an individual job.

similar but saying it explicitly:
4) you can cast it in the extract sql to match the target format

sriven786,
Thanks For your suggestion.
Actually i have decimal(18,7) in database table and i am casting to decimal(31,5) and the same decimal(31,5) is defined in medatadata of the conenctor. The job is running fine 9.1 it is only getting aborted in 11.3. Do we need to cahnge anything ?

The source value in DB is 0.45 the value coming out is 0.00

Are you Casting while Extracting from Table As suggested below ?.

you can cast it in the extract sql to match the target format

Please clarify

Yes I am casting externally in SQL query.
select cast(col as decimal(31,5)) ;
and i defined decimal(31,5) in teradata connector.
but i am getting warning
Schema reconciliation detected a size mismatch for column col1. When reading database column DECIMAL(31,5) into column DECIMAL(31,0), truncation, loss of pr (...)

try running the same query in Toad (Outside Datastage) and see if you are getting the same value or not for below example you mentioned:
The source value in DB is 0.45 the value coming out is 0.00

Also, try defining the column as decimal(31,7) and see if the warning still appears in Datastage Job?.

Thanks for your suggestion.
Outside datastage it is giving expected value.(0.45)
What would be the difference in decimal(31,5) to decimal(31,7) if the value is 0.45 only.

There isn't one.

Your issue still goes back to this statement in the error message:

"When reading database column DECIMAL(31,5) into column DECIMAL(31,0)"

And you are seeing exactly what it warns you of, truncation due to the scale mismatch. We're obviously missing a key piece of information to be able to understand where it got the zero scale decimal from so I'm going to suggest this:

Don't do a CAST in the source SQL, bring it into the job intact at its native data precision and scale, then use transformer and the DecimalToDecimal function to convert it before sending it on to the target.

Any Idea why Datastage is treating as DECIMAL(31,0) when casing as DECIMAL(31,5) and defined as Decimal (31,5)?

Also, Can you make sure that the order of the columns are correct and Just View Data for the 1 Row (Sample you mentioned) in both DS Versions and make sure that you are getting 0.45 in older and 0.00 in New Version?