Hi All,
I am trying to implement SCD type 2, like following way. But the problem is, Record gets duplicated and can't add new record
I am doing like this.
1. Datas are comin from sequential file and giving the Surrogate Key by Surrogate Key Generator, then make look up table after reading from oracle table A, "Look up"ing with the sequential stream.
2. Look up keys are only unique in two fields out of 26 fields. If look up is not successful, reject link from look up and insert to the oracle table.
3. If look up successful, ie. two field datas are available in the input sequential stream, I am sending to the transformer. Applying the date conversion. By using oracle enterprise in UPSERT mode, I am updating the existing the end date (which is default one) and Current record flag to show the latest record in the historical storage. And inserting the new record.
My questions are;
a). how better I can capture the Changed Data capture or some other way to effective to apply change in the existing record and enter new one.
b). How can I apply condition for updating record "end date" change and current Flag setting in the Oracle Enterprise. (It won't accepting values other than "Orchestrate.inputvalues")
c). Input date is varchar, I am using "StringtoTimeStamp" conversion function(like StringToTimeStamp(string,format), it's giving error. I am giving the DSLink.Enddate in the place of string, format is same as in the DataStage help. I am getting error in the derivation. Why, Is there anything wrong. or better way to get TimeStamp.
I request anyone throw some light on this doubts.
Thank you very much in advance.
IOWAJAX
SCD Type 2 Implementation error
Moderators: chulett, rschirm, roy
Hi,
Firstly use the change capture stage to compare two datasets to find new, modified,deleted and exact copy records. The change capture stage will provide a change_code output column which can be used to identify the change. Pls follow the Parallel Job Developers Guide(page 353).
Secondly we are also using StringToTimestamp derivation and it works well. Eg StringToTimestamp("2004-04-30"). Here we dont use the formatting option for the String.
HTH.
--Rich
He who humbles himself will be exalted and he who exalts himself will be humbled
Firstly use the change capture stage to compare two datasets to find new, modified,deleted and exact copy records. The change capture stage will provide a change_code output column which can be used to identify the change. Pls follow the Parallel Job Developers Guide(page 353).
Secondly we are also using StringToTimestamp derivation and it works well. Eg StringToTimestamp("2004-04-30"). Here we dont use the formatting option for the String.
HTH.
--Rich
He who humbles himself will be exalted and he who exalts himself will be humbled