Reference for Oracle GoldenGate

Valid For

Extract and Replicat

Description

Use the GETUPDATEBEFORES and IGNOREUPDATEBEFORES parameters to control whether or not the before images of columns in UPDATE operations are included in the records that are processed by Oracle GoldenGate. Before images contain column details that existed before a row was updated.

(Oracle only) Oracle GoldenGate captures both the pre-change and post-change values for update operations in a single unified update record by default. For other databases, only the pre-change values are written to trail file. In previous releases the default was to only capture the post-change value. Beginning in this release, custom SQL statements (SQLEXEC) now only execute once per update operation with the new default update format. Prior to this release, custom SQL statements would execute twice, once when encountering the pre-change value and once when encountering the post-change value. If you are using the Oracle GoldenGate with the unified update format, you can explicitly pass the pre or post-value to the custom SQL statement using the @BEFORE, @AFTER, and @BEFOREAFTER functions. Though Oracle GoldenGate can use this update format by default, the old format cam be preserved if there are conflicting parameters that would have previously generated two separate pre and post change records. In these cases, an informational message is logged in the report file.

Use the GETUPDATEBEFORES parameter as follows:

in the Extract parameter file to extract before images from the data source.
in the Replicat parameter file to include before images in a Replicat operation.

You can compare before images with after images to identify the net results of a transaction or perform other delta calculations. For example, if a BALANCE field is $100 before an update and $120 afterward, a comparison would show the difference of $20. You can use the column-conversion functions of Oracle GoldenGate to perform the comparisons and calculations.

To reference before images in the parameter file, use the @BEFORE conversion function. For example:

COLMAP (previous = @BEFORE (balance))

GETUPDATEBEFORES is required when using the Conflict Detection and Resolution (CDR) feature. See Administering Oracle GoldenGate for more information about CDR.

The GETUPDATEBEFORES and IGNOREUPDATEBEFORES parameters are table-specific. One parameter remains in effect for all subsequent TABLE or MAP statements, until the other parameter is encountered.

Because you can selectively enable or disable these parameters between MAP statements, you can enable or disable them for different threads of a coordinated Replicat. Specify the GETUPDATEBEFORES threads in one set of MAP statements, and specify the IGNOREUPDATEBEFORES threads in a different set of MAP statements.

Limitations for GETUPDATESBEFORES:

For PostgreSQL, before images of LOB columns are not logged and will not be written to the trails.
For SQL Server, columns of IMAGE, NTEXT, and TEXT data types are logged as a NULL value for before image update operations, and columns of VARBINARY(MAX), VARCHAR(MAX), and NVARCHAR(MAX) are logged as a NULL value for before image update operations unless the column was updated.

Default

IGNOREUPDATEBEFORES

Syntax

GETUPDATEBEFORES | IGNOREUPDATEBEFORES

Example

This example shows how you can apply GETUPDATEBEFORES and IGNOREUPDATEBEFORES selectively to different MAP statements, each of which represents a different thread of a coordinated Replicat.

GETUPDATEBEFORES
MAP sales.cust, TARGET sales.cust, THREAD (1);
MAP sales.ord, TARGET sales.ord, THREAD (2);
IGNOREUPDATEBEFORES
MAP sales.loc, TARGET sales.loc, THREAD (3);