SCD error - Updatable lookup requires sorted keys

Marley777 · Post by **Marley777** » Mon Feb 14, 2011 2:18 pm

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?

DSguru2B · Post by **DSguru2B** » Mon Feb 14, 2011 2:21 pm

Do an exact search on "Updatable lookup requires sorted keys". There are a few posts out there. See if any info in them helps.

Marley777 · Post by **Marley777** » Mon Feb 14, 2011 2:36 pm

Before posting I did the search, but finding cases are similar but solutions didn't help. Still can't figure out why we get the error even after sorting. Any help is appreciated.

dougcl · Post by **dougcl** » Mon Feb 14, 2011 3:39 pm

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.

Marley777 · Post by **Marley777** » Mon Feb 14, 2011 4:08 pm

We are sorting both links into the SCD stage and have $APT_NO_SORT_INSERTION = FALSE. We were using AUTO partitioning on both links, but that also didn't work. It's as if it's ignoring all sorting attempts regardless. Any further help anyone can offer is appreciated.

dougcl · Post by **dougcl** » Mon Feb 14, 2011 4:45 pm

We are using SAME and SAME and no manual sorts. We have APT_NO_SORT_INSERTION set to false at the job level.

I suspect that this workaround is not always going to work. If you get to the bottom of this, please post the answer here. I am also curious if this problem occurs in 8.5

Doug

Marley777 · Post by **Marley777** » Tue Feb 15, 2011 9:14 am

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.

dougcl · Post by **dougcl** » Tue Feb 15, 2011 6:19 pm

Hi thanks for the followup. This fix did not work for us. The only thing that worked was setting the env variable to false. Are you on 8.1 or 8.5?

chulett · Post by **chulett** » Tue Feb 15, 2011 7:19 pm

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.

Marley777 · Post by **Marley777** » Tue Feb 15, 2011 10:20 pm

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.