I've searched the site on "SCD", "MD5", and "message digest". A summary of my research:
1) MD5 and CRC64 are not supported OOTB in DS
2) Others have gotten MD5 support as an external module:
viewtopic.php?t=112259&highlight=MD5, but this post is light on the details of how it worked.
3) Unfortunately I don't know the details for finding, compiling, and implementing either MD5 or CRC64 as an external routine, nor do I understand the performance implications of external routines.
4) CRC32 is available OOTB, but might not be unique:
viewtopic.php?t=98883&highlight=MD5
viewtopic.php?t=112188&highlight=CRC64
viewtopic.php?t=110300&highlight=CRC64
4) I'll use the approach documented here to implement our SCD Type 2 requirements:
http://etl-tools.info/en/datastage-tuto ... astage.htm
Another ETL tool I've used used an MD5 hash of the "variables of interest" to detect changes. Given the above links, clearly CRC32 is being used in DS for this purpose.
Using the CRC32 routine is IMO really just "syntactic sugar", to avoid code like:
If (Var1 <> Var1_New) or (Var2 <> Var2_New) or (VarN <> VarN_New) then "Changed" Else "Not Changed"
Instead, the code would be something like:
If CRC32(Old.Var1:Old.Var2:Old.VarN) <> CRC32(New.Var1:New.Var2:New.VarN) Then "Changed"
Now onto my questions:
1) Is this a good approach, i.e. use of CRC32?
2) Would this incur a large performance hit vs. the "ugly code" above?
3) Will "pre-calculating" the CRC for existing data and maintaining in a hash help mitigate any performance hit? See Craig's approach here, search for his posts:
viewtopic.php?t=112188&highlight=CRC
4) If I implement Craig's approach above, can you explain how this would change the approach in http://etl-tools.info/en/datastage-tuto ... astage.htm?
5) Could someone (Craig?) post a (URL to a) screenshot of an SCD2 implementation using a hashed file to detect change records? If you don't have your own public web server to upload a screenshot to I find Google Docs to be useful http://docs.google.com.
A lot to ask I know...partial replies still most welcome!
![Wink :wink:](./images/smilies/icon_wink.gif)
Thanks,
Scott