To configure change history, you use special syntax in the SharePlex configuration file and, optionally, configure filter rules and other attributes to customize the history to your needs.

Create a change-history configuration file

1. Make certain that SharePlex is installed and the system is prepared according to the instructions in the SharePlex Installation Guide.

2. Create the Oracle target history tables with the same name and structure as the source tables whose history they will track, but omit all constraints on all columns.

   Important: The Oracle target tables must not have PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, or CHECK constraints, nor can columns be defined with a DEFAULT value. Because this is a history of changes, a row may have the same image as another row that has the same key. Post does not perform integrity checks on a change-history target.

3. Disable triggers on the target tables.
4. Allow no DML or DDL to be performed on the target tables except by SharePlex.

5. On the source system, create a configuration file using the following syntax. For more information about how to create a configuration file, see Configure data replication.

   datasource_specification
   src_owner.table	!cdc:tgt_owner.table	host@c.SID

   where:

   • Datasource:o.SID is the ORACLE_SID of the source Oracle instance.
   • src_owner.table is the fully qualified name of a source object (owner.object) or a wildcarded specification.
   • !cdc: identifies the target as a change-history table.
   • tgt_owner.table is the fully qualified name of the target history table or a wildcarded specification.
   • host is the target system.
   • c.SID specifies the target Oracle instance.

6. (Optional) Run the following script on the target tables to add default metadata columns with their default names. Post automatically populates the default metadata columns without any additional configuration. You can customize the script to meet your requirements.

   product_dir/util/add_change_tracking_columns.sql

   Notes:

   • The script only adds the default columns. To add optional columns, or to change a column name, use the target command to add them to the Post configuration. For a list of default and optional metadata columns, see the target command in the SharePlex Reference Guide.
   • The default columns are automatically added to new tables that are added to the SharePlex change history configuration.

Additional change history configuration options

This section describes how you can customize the SharePlex change history configuration.

Customize column names

You can use the target command with the colname option to customize the name of any target metadata column. For instructions, see the SharePlex Reference Guide.

Add the before image to each change row

You can include the before image of updates in the target table by setting the SP_OPO_TRACK_PREIMAGE parameter to U. This parameter causes Post to insert two rows to the target table for every change made to the tracked source table: one for the after image and one for the before image. The before image is composed of the key values plus the before values of the columns that were changed, unless the SP_OCT_USE_SUPP_KEYS parameter is used.

When before images are enabled, the SHAREPLEX_SOURCE_OPERATION column values for the two records will be:

UPDATE BEFORE

UPDATE AFTER

Note: The before row will not include the before image of any LOB columns, because the redo log does not contain the before image of LOBs.

You can override the global setting of SP_OPO_TRACK_PREIMAGE at the table level by using the set cdc preimage option of the target command.

For more information about SP_OPO_TRACK_PREIMAGE and the target command, see the SharePlex Reference Guide.

Include all columns of an operation in the history

This option is valid for Oracle data only. To include the values of all table columns in each target history record, rather than only the changed columns, configure the following:

1. Turn on supplemental logging for all columns of the source tables that are being tracked. For example:

   Alter table emp ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

2. Set the SP_OCT_USE_SUPP_KEYS parameter to 1.
3. Set the SP_OCT_INCLUDE_UNCHANGED_COL to 1.

Note: When both SP_OCT_USE_SUPP_KEYS and SP_OPO_TRACK_PREIMAGE are enabled, the before image includes all column values as they were before the change.

Disable change history of an operation type

To disable the change history of a DML operation type, set the SP_OPO_TRACK_OPERATIONS parameter to the appropriate value or values. Separate values with a slash (/). For example, to maintain change history only for inserts and updates, change the parameter to I/U.The default is I/U/D which sends all DML operation types to the history records.

Set rules and filters

You can use the set rule option of the target command to apply conditions on columns to control whether a change is applied to the target history table. For example, you can specify a rule that if column 1 and column 3 are changed, then apply the operation and discard any changes that apply to other columns. For instructions, see the SharePlex Reference Guide.

Include COMMITs

By default, the COMMIT record is not included in the history tables. To configure Post to insert a row for every COMMIT, set the SP_OPO_TRACK_COMMITS parameter to 1.