Running SQL Procedure just before COMMIT

Kremnican

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

ORASCN

Hi ,

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

https://docs.oracle.com/goldengate/1212/gg-winux/GWURF/gg_parameters156.htm#GWURF660

https://docs.oracle.com/goldengate/1212/gg-winux/GWUAD/wu_customcode.htm#GWUAD571

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