Suggest an Idea

[RichaDixit]

In Case of Upsert mode in attunity any violation that is not handled by this mode shows up as error in logs but there is not entry for the same  in the exception table on the target.

We are looking forward to move heavy data loads from SQL Replication to Attunity Replication but the unavailability of this feature puts us in risk of data loss in Prod environment as it is practically not feasible to scan the logs and try to figure of the errors and fix them manually.

Task Conflict settings:

• Insert -> Update the existing record
• Update -> Insert the missing record.

For more details you can refer to the case : 02012964

[JitenderR]

@RichaDixit  Looking at the case, i see that you have mentioned the error reason being 'data inconsistencies'. ideally the updates/inserts will not fail once the data inconsistencies are take care. Right? Also can you elaborate on the 'data inconsistencies'. 

In a scenario if these records were logged into an exception table or in the logs, how do you plan to put them back into target? by updating the target manually with these records? or updating source? 

Regards

JR

[John_Teichman]

Hello @RichaDixit,

Thanks for your post, however, I do not see an Idea being suggested. Please modify your request to add the actual Idea being requested. 

[RichaDixit]

Hello Everyone,

Idea: To Capture Recursive Error in Exception Table

Scenario Stimulation:

step 1:- insert a record at source (lets say PK =1)
step 2:- Attunity Reads from source and tries to push at destination
step 3:- Attunity tries to insert the record but the record already exists (PK=1)
step 4:- As per the conflict strategy (if record exists when inserting, then update the record), Attunity tries to update the record.
step 5:- While running the update command, on Target server, the command fails with UK error (lets say Name='John' which is not present on source but is present on destination)
step 6:- as the recursive-update(which was actually an insert) failed because of UniqueKey violation.

Attunity does not log the error in the exception table.

RetCode: SQL_ERROR  SqlState: 23000 NativeError: 2627 Message: [Microsoft][SQL Server Native Client 11.0][SQL Server]Violation of UNIQUE KEY constraint 'UQ__test_22J__72E12F1B7F3D641F'. Cannot insert duplicate key in object 
'dbo.test_22June'. The duplicate key value is (b). Line: 1 Column: -1
0 rows affected
Failed to apply UPDATE (3) for table 1
Failed to execute bulk insert statement.

How we are going to handle this inconsistency?

• We are thinking of creating a automated process that would read the failures from the exception tables and handle them
  • For Example: In case of unique key violation, we delete the older record that is leading to this unique key violation and insert/update the newer record. This is a dynamic process and we use one of the column in the table design to determine if the row is recent or old.

[John_Teichman]

Hello @RichaDixit 

Thanks!

[JitenderR]

@RichaDixit  Thank You for the detailed explanation. For the specific use case, where source and target primary keys are different why we are using the UPSERT logic? If we would set to below, it should route records to exception tables automatically. Assuming UPSERT logic is needed for your use case to work, this is definitely a good enhancement. 

Duplicate key when applying INSERT -> Log records to the exception table

Regards

JR

[Ola_Mayer]

Working with bulk apply/upsert modes provides significant performance improvement a specially during heavy loads, but it looses visibility granularity. Being able to identify specific exception means that we need to go back to one-by-one apply mode that will significantly increase latency.

This request is not valid