Schema reconciliation detected a size mismatch for column
Moderators: chulett, rschirm, roy
Schema reconciliation detected a size mismatch for column
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
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
ETL Developer - Datastage
Re: Schema reconciliation detected a size mismatch for colum
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.
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
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
"You can never have too many knives" -- Logan Nine Fingers
Re: Schema reconciliation detected a size mismatch for colum
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
ETL Developer - Datastage
Re: Schema reconciliation detected a size mismatch for colum
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.
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
Re: Schema reconciliation detected a size mismatch for colum
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 ?
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
ETL Developer - Datastage
Re: Schema reconciliation detected a size mismatch for colum
The source value in DB is 0.45 the value coming out is 0.00
Naren
ETL Developer - Datastage
ETL Developer - Datastage
Re: Schema reconciliation detected a size mismatch for colum
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
you can cast it in the extract sql to match the target format
Please clarify
Venkata Srini
Re: Schema reconciliation detected a size mismatch for colum
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 (...)
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
ETL Developer - Datastage
Re: Schema reconciliation detected a size mismatch for colum
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?.
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
Re: Schema reconciliation detected a size mismatch for colum
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.
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
ETL Developer - Datastage
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.
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
"You can never have too many knives" -- Logan Nine Fingers
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?
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