Updating records in SCD type 2

[seanc217]

I have a job that uses the change capture stage. I know that if I have a change_code 1 I can insert. If I have a change_code 3 I have an edit and I need to update.

Instead of updating I want to insert a new record and update the old one with an end date. How would I go about doing this using the Oracle stage?

[ray.wurlod]

Two stages, one handling the inserts and one handling the updates. The logic is determined upstream in the job design.

[vmcburney]

There is a picture of a type 2 SCD job in my db2magazine article comparing it to a job that uses the Hawk SCD stage. Shows one possible design. Includes a surrogate key retrieval.
http://www.db2magazine.com/story/showAr ... =186500841

[kumar_s]

Since the Chage capture will have both records, in transformer use pivot logic to split the record into two. One to Update link and Another to insert link. Both to the oracle stage.

[richdhan]

Hi Seanc,

From the CDC take the output to the transformer.

In the tranformer have 2 constraints.

1. change_code=1 or change_code=3 (New Inserts)

2. change_code=3 (Old record Updates)

The output from the constraint 2 should go to a join stage where you join with the old records.

Now you have 2 streams one for insert and one for update.

HTH
--Rich

[seanc217]

Here's How I did it.

I have 3 output links from a transformer. One catches the new inserts (change code 1). The other 2 output links catch the edit (change code 3). On the one output link I update the old record with the values from the transformer to set the end_date and the current record indicator to "N" using a user-defined sql in the Oracle stage. The other link does the insert of the new record with the sequence.

The important thing here is to make sure your link ordering is set so that the update happens first then the insert or else you will update both records. If this works then you do not need to do a join to find your old record. The only thing you will need is the natural key of the record and possibly an index to speed retrieval.

What do you think?

[seanc217]

OK testing this way did not work because of the parallel nature of the job. So I get both records with a record indicator of "N".

I will investigate the other ways which were suggested.

Thanks