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?
Updating records in SCD type 2
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 3593
- Joined: Thu Jan 23, 2003 5:25 pm
- Location: Australia, Melbourne
- Contact:
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
http://www.db2magazine.com/story/showAr ... =186500841
Certus Solutions
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
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
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
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?
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?