Skip to Main Content
Forums

GoldenGate

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Running SQL Procedure just before COMMIT

KremnicanApr 6 2021

Hello,
I'm migrating from DB2 SQL Replication (DB2 source, DB2 target) to GoldenGate. The DB2 replication has a call to SQL Procedure in the statements for a specific Replication Set and I need to do the same in the GoldenGate.
Is there any way to achieve this behaviour?

Thank you

Comments

ORASCN

Hi ,
Use the SQLEXEC feature to execute database statements or call procs from GG processes. Please check the below,

SQLEXEC (0 Bytes)17 Customizing Oracle GoldenGate Processing (0 Bytes)
Regards,
Veera

Kremnican

Unfortunately SQLEXEC in the MAP statement executes even before the MAP is "executed" (insert / update / delete) as the result can be used in the MAP statement itself.
Standalone SQLEXEC has a timer, which means it either runs when not needed or there's no guarantee it will be run right before the COMMIT, e.g. the timing may cause to run the SQL procedure some time after COMMIT, which may cause inconsistent behaviour.

LexmanLexman-Oracle

As your requirement is ti run the SP on a subset of data, you must use sqlexec with MAP statement. Sqlexec for MAP statements have the option to run the SP on the data set before or after the mapping
Check options AFTERFILTER/BEFOREFILTER in sqlexec documentation
Also , you can choose the iteration of SP run , again option EXEC {MAP | ONCE | TRANSACTION | SOURCEROW}
https://docs.oracle.com/en/middleware/goldengate/core/19.1/reference/sqlexec.html#GUID-34A0589B-1450-4BC9-A573-683895AAA1EC

1 - 3

Post Details

Added on Apr 6 2021
#goldengate
3 comments
166 views