Hello!
I have few tables that does not primary key column/s - I know this is a bad DB design, but unfortunately, we have no plan to correct this in near future.
Does anyone know how to create a CDC with history on such tables?
Your help will be greatly appreciated.
Thanks,
Matt
Mattdos11 (BOB member since 2006-02-07)
Are you talking about Oracle CDC or roll-your-own CDC?
eganjp 
(BOB member since 2007-09-12)
I dont know what roll-your-own cdc is. I am using Oracle tables as the source and putting them in SQL server table as target. Does that help?
Mattdos11 (BOB member since 2006-02-07)
Oracle CDC will provide you with a result set/table of just the rows that were changed. It does this based on your transaction log. If your source if very large (think billions of rows) then this is the way to go as you simply could not read in the entire table and compare it to the source in a reasonable amount of time - especially since you don’t have a primary key. Even with Oracle CDC you are going to have a difficult time figuring out which row in the target table to apply the changes to.
Roll-your-own requires you to read the entire table from the source and compare it to the target using the Table Compare transform. How you’re going to do this is the big question since you don’t have a defined set of columns that function as the primary key. If you know which columns can be used as a logical primary key then that should work. You need to have something to use as a basis for knowing which target row to compare to the source row.
If there is absolutely nothing available as a primary key then your next (best?) option is to truncate the target and reload it every day.
eganjp (BOB member since 2007-09-12)
Jim,
I appreciate your reply. However, I am not sure what Oracle CDC is. Is it a transform…I dont see it? I am not very experienced in DS. I apologize if I am asking some foolish questions.
Also, the requirements includes keeping history. So, I have created columns for Load_date and most_recent. If the row is modified, it should change the most_recent to ‘N’ and add a new row with most_recent as ‘Y’.
So, truncate and load will not work. Table is not very huge, so if a solution of my problem requires comparing each column of each row, it should be ok. I am just not sure how to achieve it.
Thanks for any help anyone can provide on this.
-Matt
Mattdos11 (BOB member since 2006-02-07)
What you are looking for is called Slowly Changing Dimenions Type II (aka SCD Type II). DS supports this through the use of a Table Compare transform and the History Preserving transform.
Oracle CDC is a feature of Oracle and requires DBA access to set it up. DS can work with the output of Oracle CDC.
eganjp (BOB member since 2007-09-12)