Only one target row is updated, instead of them all

[Ole_Dufour]

I have created a CDC task that includes just one table in source and target

One row in the source table  corresponds with multiple rows in the target table.
When I start the task and a value in the source table is updated, only the first one of the corresponding rows in the target table is updated.
I don't use filters.

My logs confirm this behaviour because at each update statement the following clause is added : 
AND ROWNUM = 1 ; 

I want all of the values in the target table to be updated, how can I achieve that ? Is it possible at all?

Many thanks

[SushilKumar]

Hello Team,

Replicate Captures the changes in term of the SQL Statement executed on the Source endpoint. are you using some kind of filter or Transformation . We would like to test the same in our LAB environments. 

We would request you to raise a Support ticket to do the Further analysis .

Regards,

Sushil Kumar

[Ole_Dufour]

Hi Sushil,

Thank you for your message. 
I don't think we are using any filters or transformations.
ID_FOYER is the column in the target table. There can be multiple ID_FOYER values for the same ROW_ID in the source table. 

See the screenshots below. Moreover there are no global rules for this task. 

[SachinB]

Hello @Ole_Dufour ,

Based on the information provided, it seems that there is a discrepancy between the primary key definition in the source database and the replicated target endpoint. In the source database, the primary key is defined as "ROW_ID," while in the replicate, it appears that "ID_FOYER" is defined as the additional primary key. This change was likely made to accommodate the capture of multiple records in the target endpoint. Hope I am right here

Furthermore, it appears that the replication process is configured to apply changes at the task level, and when the replicate process updates a record in the target endpoint, it also updates the primary column ("ID_FOYER"). To verify and understand this behaviour further, it is suggested to enable the store change, which will capture before and after images from the source side.

Regards,

Sachin B

[Ole_Dufour]

Hi @SachinB ,  @SushilKumar 

Thanks for your answer. In my source table I changed "Divorcé" to "Marié"
Find below the contents of my change table after changing the source value.

20230714135804000000000000000000053 U 0100 00000034.a2a430c4.00000001.0000.02.0000:77.1900.16 UPDATE 09001000000000000000000000000000 14/07/23 13:58:04,000000000 BXG-KSO Marié
20230714135804000000000000000000053 B 00000034.a2a430c4.00000001.0000.02.0000:77.1900.16 BEFOREIMAGE 09001000000000000000000000000000 14/07/23 13:58:04,000000000 BXG-KSO Divorcé

Concerning he discrepancy between the primary key definition in the source database and the replicated target endpoint do you have any suggestions as to configure it correctly ?
"ID_FOYER" is the corresponding target column name of  "ROW_ID " which is the primary key of the source table.

Only the value encircled in red is updated each time in the target table, but I want all values updated !

Thanks,

Ole Dufour
Paris

[Heinvandenheuvel · Accepted solution]

IMHO you lied to Replicate. Replicate sees 1 row updated on the source so it wants to update 1 row on the target. There should be only 1 row on the target is the PK corresponds, but games were played with this "ID" column (Sequence?) 

Early versions of  Replicate just issued the update without  "AND ROWNUM = 1" for Oracle or similar constructs  for other targets. Support cases where started by customers complaining that more than 1 row was updated/deleted in the case of duplicates (which would not happen with a normal PK as Replicate expects/requires). Based on that customer input Replicate was changed to update/delete just 1 row  for 1 change even when there are duplicates on target.

Now you want it the other way around! That just goes to show that no good deed goes unpunished.

Fortunately for you the new method was conditionally put in place with.

Please try again after change the target advanced internal parameter  updateOneRow to False (0) 

Cordialement,

Hein.

[Ole_Dufour]

Hi @Heinvandenheuvel ,

Thank you for your prompt response.

Below I have summarized our current configuration. We have 2 source tables and one target table.

In the source database, multiple persons can be part of one foyer ('family'). When updating the address of a foyer, all of the persons in the target table get updated now.
So when updating 5 rue du Labyrinte to 10 rue du Labyrinte, the address of id's 44556, 44557 and 44558 in the target table will get updated correctly thanks to the internal parameter "updateOneRow" you gave us and we have set to False(0).

However, I'm sorry to say the situation is more complicated than that.
How should my CDC task be configured if the address in the target for Personnes should only be captured for personnes having the role 'Conjoint' ?
That means that the addresses of persons in all other roles should be null, so Luc Lafarge shouldn't have an address in the target table

I suppose that somehow in the transformation of Adresse in the 'Foyer' table we will need to evaluate the value of the Role column in the Personne table.

Please be aware that this example is just a simplified representation of a problem that we are current dealing with.

Thanks in advance,

Ole Dufour

*Source table 'Personne'

ID   FirstName       Name        IdFoyer       Role
1    Jean                    Lafarge          4              Conjoint
2     Valérie               Lafarge          4              Conjoint
3     Luc                      Lafarge          4              Enfant
4     Guillaume         Dupont         5             Conjoint

*Source table 'Foyer'

ID            Adresse
4             5 rue du Labyrinte
5             12 rue du Paradis

*Target table 'Personne'

ID           Source_ID      FirstName          Name               Adresse                                                                           Role
44556       1                     Jean                     Lafarge         5 rue du Labyrinte                                                         Conjoint
44557       2                     Valérie                 Lafarge         5 rue du Labyrinte                                                         Conjoint
44558      3                     Luc                        Lafarge        (should be null because Role <> 'Conjoint')        Enfant