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



Joined: 13 May 2015
Posts: 5

Points: 205

Post Posted: Fri Oct 06, 2017 8:23 am Reply with quote    Back to top    

DataStage® Release: 9x
Job Type: Parallel
OS: Windows
Hi,
One of my datastage jobs is failling with below error in SCD stage.
Fatal Error: APT_Decimal::asIntegerU64: the decimal value is out of range for the integer result.

Job design: Source(oracle) ->Transformer -> SCD -> Target(oracle)

The issue is with dummy record (Surrogate Key = -100) in the dimension table. There are 2 columns in source (key value pair). If the source key matches with the dummy record in target, ETL job is failing.

Dummy record (target dimension table):
SurrogateID Key Value
-100 N/A N/A


I was able to resolve the issue with either of 3 approaches, but still want to know the actual reason of failure.
a.) Filtered out 'N/A' key from source.
or
b.) Changed data type of surrogate key from Decimal(38,0) to Integer (10) and job runs fine without filtering the 'N/A' record in source.
or
c.) Using abs(surrogate key) while fetching data from target to SCD stage for comparison.

I searched forums and the suggestion given by ArndW for this issue was to contact IBM support.
http://www.dsxchange.com/viewtopic.php?t=147220

Please help on this.

_________________
Best Regards,
Balakrishna V
chulett

Premium Poster


since January 2006

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

Joined: 12 Nov 2002
Posts: 42163
Location: Denver, CO
Points: 216469

Post Posted: Fri Oct 06, 2017 9:25 am Reply with quote    Back to top    

Arnd's suggestion to contact support was for the situation where the values were allegedly appropriate for the data type. However, that "IntegerU64" is unsigned so -100 would indeed be "out of range" for it.

_________________
-craig

Dr. Frankenstein entered a bodybuilding competition and discovered he had seriously misunderstood the objective.
Rate this response:  
Not yet rated
balu99999
Participant



Joined: 13 May 2015
Posts: 5

Points: 205

Post Posted: Fri Oct 06, 2017 10:32 am Reply with quote    Back to top    

I defined data type as Decimal(38,0).
Is SCD converting Decimal(38,0) to unsigned integer?

If yes, how to avoid it?

_________________
Best Regards,
Balakrishna V
Rate this response:  
Not yet rated
ray.wurlod

Premium Poster
Participant

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

Joined: 23 Oct 2002
Posts: 54007
Location: Sydney, Australia
Points: 293017

Post Posted: Mon Oct 09, 2017 3:07 am Reply with quote    Back to top    

The surrogate key value is ALWAYS generated as UInt64 (unsigned BigInt). You can manipulate this in a Transformer stage if you need negative surrogate key values ( but why would you need them? ).

_________________
RXP Services Ltd
Melbourne | Canberra | Sydney | Hong Kong | Hobart | Brisbane
currently hiring: Canberra, Sydney and Melbourne
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: 42163
Location: Denver, CO
Points: 216469

Post Posted: Mon Oct 09, 2017 7:46 am Reply with quote    Back to top    

We use them (-1 specifically) for the single "dummy" or N/A record in dimensions, something pretty common. However, they are never generated by anything but are rather inserted as part of the tabl ...

_________________
-craig

Dr. Frankenstein entered a bodybuilding competition and discovered he had seriously misunderstood the objective.
Rate this response:  
Not yet rated
UCDI



Group memberships:
Premium Members

Joined: 21 Mar 2016
Posts: 261

Points: 2643

Post Posted: Mon Oct 09, 2017 12:08 pm Reply with quote    Back to top    

just bits ...
-1, if dumped without any logic into unsigned int, will give the maximum value of that int.

That is if you put -1 into an unsigned byte without any error checks you get 255, and if you do it for a 64 bit int you get 2^64-1 whatever that is.

That might work just as well as -1 for a sentinel value. That is, you are unlikely to fill a 64 bit int's worth of keys between now and the time they unplug your server for scrap. Its a really large number; A program that does nothing but iterate from 0 to this value as fast as it can go takes years to complete. It should be a 'safe' value.

-100 is close to this value, and equally usable for the same reasons.
Rate this response:  
Not yet rated
balu99999
Participant



Joined: 13 May 2015
Posts: 5

Points: 205

Post Posted: Tue Oct 10, 2017 8:56 am Reply with quote    Back to top    

We are inserting this Dummy record (Surrogate Key = -100) manually to the table.


When source has a Key that matches to the dummy record's key, job is failing.
If I filter out that record from source, job runs fine.

_________________
Best Regards,
Balakrishna V
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