Schema reconciliation detected a size mismatch for column

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
naren9135
Participant
Posts: 7
Joined: Thu Apr 07, 2011 11:41 pm

Schema reconciliation detected a size mismatch for column

Post by naren9135 »

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
Naren
ETL Developer - Datastage
sriven786
Participant
Posts: 37
Joined: Wed Nov 08, 2017 1:36 pm

Re: Schema reconciliation detected a size mismatch for colum

Post by sriven786 »

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.
Venkata Srini
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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.
-craig

"You can never have too many knives" -- Logan Nine Fingers
naren9135
Participant
Posts: 7
Joined: Thu Apr 07, 2011 11:41 pm

Re: Schema reconciliation detected a size mismatch for colum

Post by naren9135 »

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.
Naren
ETL Developer - Datastage
naren9135
Participant
Posts: 7
Joined: Thu Apr 07, 2011 11:41 pm

Post by naren9135 »

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.
Naren
ETL Developer - Datastage
sriven786
Participant
Posts: 37
Joined: Wed Nov 08, 2017 1:36 pm

Re: Schema reconciliation detected a size mismatch for colum

Post by sriven786 »

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.
Venkata Srini
UCDI
Premium Member
Premium Member
Posts: 383
Joined: Mon Mar 21, 2016 2:00 pm

Post by UCDI »

similar but saying it explicitly:
4) you can cast it in the extract sql to match the target format
naren9135
Participant
Posts: 7
Joined: Thu Apr 07, 2011 11:41 pm

Re: Schema reconciliation detected a size mismatch for colum

Post by naren9135 »

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 ?
Naren
ETL Developer - Datastage
naren9135
Participant
Posts: 7
Joined: Thu Apr 07, 2011 11:41 pm

Re: Schema reconciliation detected a size mismatch for colum

Post by naren9135 »

The source value in DB is 0.45 the value coming out is 0.00
Naren
ETL Developer - Datastage
sriven786
Participant
Posts: 37
Joined: Wed Nov 08, 2017 1:36 pm

Re: Schema reconciliation detected a size mismatch for colum

Post by sriven786 »

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
Venkata Srini
naren9135
Participant
Posts: 7
Joined: Thu Apr 07, 2011 11:41 pm

Re: Schema reconciliation detected a size mismatch for colum

Post by naren9135 »

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 (...)
Naren
ETL Developer - Datastage
sriven786
Participant
Posts: 37
Joined: Wed Nov 08, 2017 1:36 pm

Re: Schema reconciliation detected a size mismatch for colum

Post by sriven786 »

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?.
Venkata Srini
naren9135
Participant
Posts: 7
Joined: Thu Apr 07, 2011 11:41 pm

Re: Schema reconciliation detected a size mismatch for colum

Post by naren9135 »

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.
Naren
ETL Developer - Datastage
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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.
-craig

"You can never have too many knives" -- Logan Nine Fingers
sriven786
Participant
Posts: 37
Joined: Wed Nov 08, 2017 1:36 pm

Post by sriven786 »

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?
Venkata Srini
Post Reply