x

fn_cdc_get_net_changes_ - Not “rolling-up” all changes for a key into a single row.

We have a problem with fn_cdc_get_net_changes_ 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_] 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

Here is the output. alt text

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

hz-parties2.png (4.0 kB)
more ▼

asked Apr 05, 2016 at 09:40 AM in Default

avatar image

rachanta
1

In the _CT-table, is the LSN of the insert within the range you get from your two calls to sys.fn_cdc_map_time_to_lsn in the later Query?

Apr 05, 2016 at 10:27 AM Magnus Ahlkvist

Thanks for the reply. Yes it is with in the range. When I run fn_cdc_get_all_changes_ with the same input parameters, it gives me 3 records with __$operation value equal to 2,3,4 respectively. This means insert also falls with in the range.

Apr 05, 2016 at 01:34 PM rachanta

Sorry, but I don't Think I can be of much assistance. I Think the one row you get also looks somewhat strange. _$operation = 2, which I thought meant INSERT, but only the two columns involved in the UPDATE operation are set. No, sorry, I've never used CDC for Oracle Attunity, so I'm kinda lost. I just added my comment to help ruling out the obvious (which is not all that obvious after having looked at the same problem for a long time). I hope someone else can help you out.

Apr 05, 2016 at 09:06 PM Magnus Ahlkvist
(comments are locked)
10|1200 characters needed characters left

0 answers: sort voted first
Be the first one to answer this question
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1221
x11

asked: Apr 05, 2016 at 09:40 AM

Seen: 41 times

Last Updated: Apr 05, 2016 at 09:06 PM

Copyright 2017 Redgate Software. Privacy Policy