1. Oracle GoldenGate Classic Architecture Documentation
  2. Administer
  3. Customizing Oracle GoldenGate Processing
  4. Using the Oracle GoldenGate Event Marker System to Raise Database Events
  5. Case Studies in the Usage of the Event Marker System
  6. Propagate Only a SQL Statement without the Resultant Operations

Propagate Only a SQL Statement without the Resultant Operations

This example shows how different EVENTACTIONS clauses can be used in combination on the source and target to replicate just a SQL statement rather than the operations that result from that statement. In this case, it is an INSERT INTO...SELECT transaction. Such a transaction could generate millions of rows that would need to be propagated, but with this method, all that is propagated is the initial SQL statement to reduce trail and network overhead. The SELECTs are all performed on the target. This configuration requires perfectly synchronized source and target tables in order to maintain data integrity.

Extract:

TABLE source.statement, EVENTACTIONS (IGNORE TRANS INCLUDEEVENT);

Replicat:

TABLE source.statement, SQLEXEC (execute SQL statement), &
EVENTACTIONS (INFO, IGNORE);

To use this configuration, a statement table is populated with the first operation in the transaction, that being the INSERT INTO...SELECT, which becomes the event record.

Note:

For large SQL statements, the statement can be written to multiple columns in the table. For example, eight VARCHAR (4000) columns could be used to store SQL statements up to 32 KB in length.

Because of the IGNORE TRANS INCLUDEEVENT, Extract ignores all of the subsequent inserts that are associated with the SELECT portion of the statement, but writes the event record that contains the SQL text to the trail. Using a TABLE statement, Replicat passes the event record to a SQLEXEC statement that concatenates the SQL text columns, if necessary, and executes the INSERT INTO...SELECT statement using the target tables as the input for the SELECT sub-query.