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.
viewtopic.php?t=147220
Please help on this.
Decimal value is out of range
Moderators: chulett, rschirm, roy
Decimal value is out of range
Best Regards,
Balakrishna V
Balakrishna V
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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? ).
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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 table build, DML as part of the delivered DDL. They're matched to when the business key lookup during the fact load fails, i.e. NULL results either converted to post-lookup or NULL values converted to "N/A" pre-lookup.
Not really sure how this plays into the SCD stage, never having actually used it. Does it have a problem simply matching to / fetching the dummy record since the surrogate value for it is negative?
Not really sure how this plays into the SCD stage, never having actually used it. Does it have a problem simply matching to / fetching the dummy record since the surrogate value for it is negative?
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
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.
-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.