DSXchange: DataStage and IBM Websphere Data Integration Forum
View next topic
View previous topic
Add To Favorites
Author Message
naren9135
Participant



Joined: 07 Apr 2011
Posts: 7

Points: 56

Post Posted: Tue Nov 14, 2017 1:20 am Reply with quote    Back to top    

DataStage® Release: 11x
Job Type: Parallel
OS: Unix
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



Joined: 08 Nov 2017
Posts: 35

Points: 260

Post Posted: Tue Nov 14, 2017 8:47 am Reply with quote    Back to top    

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
Rate this response:  
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 42574
Location: Denver, CO
Points: 219079

Post Posted: Tue Nov 14, 2017 10:08 am Reply with quote    Back to top    

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

I know I don't say this enough, but I like when you talk to me. It's much better than when nobody talks to me. Or when people that I don't like will not stop talking to me.
Rate this response:  
naren9135
Participant



Joined: 07 Apr 2011
Posts: 7

Points: 56

Post Posted: Tue Nov 14, 2017 9:24 pm Reply with quote    Back to top    

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
Rate this response:  
naren9135
Participant



Joined: 07 Apr 2011
Posts: 7

Points: 56

Post Posted: Tue Nov 14, 2017 9:26 pm Reply with quote    Back to top    

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
Rate this response:  
sriven786
Participant



Joined: 08 Nov 2017
Posts: 35

Points: 260

Post Posted: Wed Nov 15, 2017 8:00 am Reply with quote    Back to top    

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

https://www.ibm.com/support/knowledgecenter/en/SSZJPZ_9.1.0/com.ibm.swg.im.iis.conn.drs.usage.doc/topics/DRS061.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
Rate this response:  
UCDI



Group memberships:
Premium Members

Joined: 21 Mar 2016
Posts: 327

Points: 3307

Post Posted: Wed Nov 15, 2017 12:15 pm Reply with quote    Back to top    

similar but saying it explicitly:
4) you can cast it in the extract sql to match the target format
Rate this response:  
naren9135
Participant



Joined: 07 Apr 2011
Posts: 7

Points: 56

Post Posted: Thu Nov 16, 2017 2:23 am Reply with quote    Back to top    

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
Rate this response:  
naren9135
Participant



Joined: 07 Apr 2011
Posts: 7

Points: 56

Post Posted: Thu Nov 16, 2017 3:15 am Reply with quote    Back to top    

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

_________________
Naren
ETL Developer - Datastage
Rate this response:  
sriven786
Participant



Joined: 08 Nov 2017
Posts: 35

Points: 260

Post Posted: Thu Nov 16, 2017 8:46 am Reply with quote    Back to top    

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
Rate this response:  
naren9135
Participant



Joined: 07 Apr 2011
Posts: 7

Points: 56

Post Posted: Wed Nov 22, 2017 4:53 am Reply with quote    Back to top    

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
Rate this response:  
sriven786
Participant



Joined: 08 Nov 2017
Posts: 35

Points: 260

Post Posted: Wed Nov 22, 2017 8:04 am Reply with quote    Back to top    

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
Rate this response:  
naren9135
Participant



Joined: 07 Apr 2011
Posts: 7

Points: 56

Post Posted: Tue Nov 28, 2017 11:13 pm Reply with quote    Back to top    

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
Rate this response:  
Not yet rated
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 42574
Location: Denver, CO
Points: 219079

Post Posted: Wed Nov 29, 2017 7:33 am Reply with quote    Back to top    

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

I know I don't say this enough, but I like when you talk to me. It's much better than when nobody talks to me. Or when people that I don't like will not stop talking to me.
Rate this response:  
Not yet rated
sriven786
Participant



Joined: 08 Nov 2017
Posts: 35

Points: 260

Post Posted: Wed Nov 29, 2017 8:17 am Reply with quote    Back to top    

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
Rate this response:  
Not yet rated
Display posts from previous:       

Add To Favorites
View next topic
View previous topic
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum



Powered by phpBB © 2001, 2002 phpBB Group
Theme & Graphics by Daz :: Portal by Smartor
All times are GMT - 6 Hours