My Oracle Support Banner

Handling SQL/MX Statement Atomicity for GoldenGate Replication (Doc ID 1322949.1)

Last updated on OCTOBER 24, 2019

Applies to:

Enterprise Performance Management and Business Intelligence > Business Intelligence
Oracle GoldenGate - Version 10.0.0.0 and later
HP NonStop Itanium (OSS)

Purpose

HP SQL/MX has a feature called Statement Atomicity. Statement atomicity is a feature that guarantees the all-or-nothing completion of an individual SQL/MX statement. A statement either completes fully or, if an error occurs, the statement has no effect on the database. In Release 1.8, statement atomicity was implemented at the transaction level by aborting the current transaction following an unsuccessful INSERT, UPDATE, or DELETE operation.
In Release 2.0 or later, statement atomicity is implemented at the statement level. By default, Release 2.0 or later does not abort the current transaction following an error.

A warning 8875 is displayed when a statement failed within a transaction and the transaction is allowed to commit. For example, the statement below fails, the transaction is allowed to be committed and a warning 8875 is displayed saying that only the statement is roll back but the transaction does not abort.
>>Begin work;
--- SQL operation complete.
>>Insert into t1 values ( '2','ALI','US');
Commit work;
*** ERROR[8102] The operation is prevented by a unique constraint.
*** WARNING[8875] An error in an insert, update or delete statement caused that
statement to be rolled back without aborting the transaction.
--- 0 row(s) inserted.
--- SQL operation complete.
>>

This results in an insert followed by a delete of the same row being posted onto the TMF audit trails and this is what extract will capture.
The resulting issue for the replicat is that when the insert is encountered, it abends with
GGS ERROR 218 SQL error -8102

>>
>>
>>

Troubleshooting Steps

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Purpose
Troubleshooting Steps
 Workarounds:

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.