DSXchange

I'm trying to use the SCD stage to implement SCD2 logic. The issue I have is that a new surrogate key is generated for both updated records and new records. That is if a records is updated then the result is two record in the dimension update link:

1) entry to expire the old record with the SK and EXP_DT
2) entry to add the new updated record with all columns.


The issue is that for the updated record it doesn't keep the original key from the dimension lookup. It generates a new key. But a new key should ONLY be generated if the record was not found in the dimension lookup.

Is this yet another bug in the SCD stage or am I missing something?

I just answered my own question.

It is a bug in the SCD stage:

even thought the dimension table is not displayed in the source column pane you can still access it through transformer script.

example:

if isNull(out_odbc_Products.PRODUCT_SK) then
NextSurrogateKey()
else
out_odbc_Products.PRODUCT_SK


It's actually kind of funny because when you type this in then datastage draws a column link going to an invisible table:-)

Does this bug still exist in version 8.1?

yes.

You said you were running 8.01. Probably it's 8.0.1.
This is not the same as 8.1.

I am working on 8.0 version and used SCD stage. Its is working fine for me and generating 2 records for type2, one with old SK for update and one with new SK for insert.

hi nirdesh2,

what do you have in the expression for the SK column on the Dim update tab?

Is it just NextSurrogateKey() or something else?

I am using NextSurrogateKey() in expression nothing else.

That's odd. I tried that initially and it didn't work. It would always generate a new SK. Maybe I'm missing some DataStage fix or patch or something.

HiI tried Scd type 2 .it is working fine with file but not with database.It is having bug in 8.0.1. If anybody have implement with ODBC stage can help need urgently .

I am using version 8.1.0, and I'm facing the same problem.

Does anybody know of a patch for this?

Thank you micheald !

~Hiral