SQLTeam.com | Weblogs | Forums

Net Changes with update mask - CDC


#1

We have a problem with fn_cdc_get_net_changes_<capture_instance> function in CDC. Here is the scenario where it fails:

When a row is inserted and then an update is done on one of the columns, [cdc].fn_cdc_get_net_changes_<capture_instance>] function is supposed to return a single row that reflects the final content of the row (with values of the updated columns from ‘update row’ along with the value of columns from ‘insert row’ that were not updated). Instead the function returns a single row with values of the columns updated in the ‘update row’ and nulls in the columns that were not updated after initial insert.

Our Source is Oracle and we are using CDC Service for Oracle Attunity. Any help is very much appreciated.

Here is the example:

Step 1: Inserted a record into source table:
insert into AR.HZ_PARTIES
(party_id, party_number, party_type, party_name)
values (99999998,99999998,'PERSON','ARR')

Step 2: Updates Party Name field in the record inserted above.
UPDATE AR.HZ_PARTIES
SET party_name = 'RRA'
WHERE party_id = 99999998

Step 3: Checked _CT table. Can see both insert and update operations.

Step 4: Run fn_cdc_get_net_changes_AR_HZ_PARTIES function:
select * from [cdc].[fn_cdc_get_net_changes_AR_HZ_PARTIES](sys.fn_cdc_map_time_to_lsn('smallest greater than or equal', GETDATE()-2), sys.fn_cdc_map_time_to_lsn('largest less than or equal', GETDATE()+1),'all with mask') WHERE PARTY_ID = 99999998

Below is the result:

I am expecting to see values in PARTY_NUMBER AND PARTY_TYPE from the initial insert. But it gives values only in the column updated at the end.

  • Regards,
    Ravali