SCD error - Updatable lookup requires sorted keys
Moderators: chulett, rschirm, roy
SCD error - Updatable lookup requires sorted keys
Hi, thanks for reading. We are getting the following error when trying to use a Slowly Changing Dimension stage.
scdSetMessageStatus,0: Fatal Error: Updatable lookup requires sorted keys.
$APT_NO_SORT_INSERTION = FALSE. Also doing an hash sort on the keys used in the scd stage, but job still aborts with the error above. We tried using AUTO partitiioning and handling the hash sort by link sorting, but still get the error. Anyone ever experienced this or have a fix?
scdSetMessageStatus,0: Fatal Error: Updatable lookup requires sorted keys.
$APT_NO_SORT_INSERTION = FALSE. Also doing an hash sort on the keys used in the scd stage, but job still aborts with the error above. We tried using AUTO partitiioning and handling the hash sort by link sorting, but still get the error. Anyone ever experienced this or have a fix?
We have seen this problem in both the SCD stage and a lookup stage if a range lookup is used.
In the first case, we set the APT_NO_SORT_INSERTION variable to false and that fixed it. Manually inserting sorts did not help. The sort, by the way, did not show up in the score.
On the second case, sorting the reference link worked.
In the first case, we set the APT_NO_SORT_INSERTION variable to false and that fixed it. Manually inserting sorts did not help. The sort, by the way, did not show up in the score.
On the second case, sorting the reference link worked.
Problem solved :D
- Here is the error I was getting
scdSetMessageStatus,0: Fatal Error: Updatable lookup requires sorted keys.
- Here is how I fixed it
We had a reference link going directly into the SCD stage. We put a sort stage on the reference link after the DB2 UDB stage and before the SCD stage.
So our reference link into the SCD stage looks this
DB2 UDB => Sort => SCD
You can also use a ORDER BY statement in the sql if your reference link is reading from a table. If you are using a file you may have to add a sort stage... link sorts didn't work for me.
- Here is the error I was getting
scdSetMessageStatus,0: Fatal Error: Updatable lookup requires sorted keys.
- Here is how I fixed it
We had a reference link going directly into the SCD stage. We put a sort stage on the reference link after the DB2 UDB stage and before the SCD stage.
So our reference link into the SCD stage looks this
DB2 UDB => Sort => SCD
You can also use a ORDER BY statement in the sql if your reference link is reading from a table. If you are using a file you may have to add a sort stage... link sorts didn't work for me.
That seems odd to me as setting $APT_NO_SORT_INSERTION to FALSE is typically the same as not even including it in the job... unless your Project level default is TRUE for some reason.
I'm also thinking that the statement "You can also use a ORDER BY statement in the sql" is incomplete. Yes, you can do it but you'll also still need a Sort stage in line after it - the difference is you would set it to "Don't sort, already sorted'. That or ensure you have $APT_NO_SORT_INSERTION set to TRUE in that case.
I'm also thinking that the statement "You can also use a ORDER BY statement in the sql" is incomplete. Yes, you can do it but you'll also still need a Sort stage in line after it - the difference is you would set it to "Don't sort, already sorted'. That or ensure you have $APT_NO_SORT_INSERTION set to TRUE in that case.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Hi, in our case even with $APT_NO_SORT_INSERTION set to FALSE we still got the error. Tried link sorts, but the error didn't go away and the osh output didn't show a sort on the reference link. The error only goes away if we do an order by in the sql or add a sort stage between the db2 udb source and the scd stage. I have a ticket open with IBM Support...I'll keep you posted. Thanks for your help.