Full row from CDC

Alex1234

New Member
Hi!

I'm working on a BI team responsible to retrieve data from OpenEdge using CDC.
We are usually fetching the entire row when a change happens, but I've been digging through the documentation and I can't find such an option for CDC and OpenEdge, am I missing something or is this not possible?

Would Pro2 work for this scenario instead or does it have the same limitation?
 

TomBascom

Curmudgeon
You have the RECID of the changed record so you could fetch the source record using that information (this is what Pro2 does) but you should be aware that if multiple changes have occurred you could be getting later changes along with the ones that are associated with whichever tracking record you are processing. (It is also possible that the record got deleted and that a new record with the same RECID got created...)
 

TomBascom

Curmudgeon
I use the MAXIMUM policy, which means that I have unique key fields that identify the record and don't have to rely on the RECID. So first I fetch the current copy of the record from the TARGET database. Then I apply the updates to that record and write it back to the target. That way updates arrive in the proper order and the record on the target is always changing in the same sequence that it did on the source.
 

Alex1234

New Member
Since we strive to achieve a complete history the solution of going back to the source record would probably not be the correct way forward.
Does Pro2 send the entire row everything a change happens?
 

TomBascom

Curmudgeon
I have not tested this but... I am told that when a change occurs with policies MEDIUM or MAXIMUM, CDC records the values for all of the fields in the record that have a field policy. Whether they changed or not. So if you are tracking all fields you should have all of the elements of the full record as they were at that point in time in the change tracking table (the CDC_tableName table) without needing to first read the record from the target - so my algorithm above could be optimized to omit that step.

Pro2 uses the MINIMUM policy. So it only knows the RECID of the changed record. It then uses that RECID to fetch the CURRENT value of the record that has that RECID. Pro2 also has an option to "compress" changes - which means that it eliminates multiple intermediate changes to a record if there have been more than one change.

If "a complete history" means that you expect to see changes to records in the same sequence that they originally occurred then the Pro2 approach (with or without compression) is going to be challenging as you will almost certainly see some changes before they actually happened.
 

TomBascom

Curmudgeon
I have not tested this but...

It looks like I should have tested that. For now I will stick with reading from the target and then applying changes to keep changes correctly sequenced.
 
Top