SharePlex 8.6.6 - Upgrade Guide

Filter DML operations

You can configure SharePlex to filter out the following DML from replication when wildcarding is being used.

• DML type (INSERT, UPDATE, DELETE)
• DML related to sequences, materialized views, and SQL*Loader direct-path loads.

Filter out a DML type

You can configure SharePlex to filter any type of DML operation so that the operation is not replicated to the target table. DML filtering is compatible with most other SharePlex configuration syntax. See the Restrictions.

Configure a DML filter

To configure a DML filter, add the following syntax to the source table specification. Leave a space between the table specification and the filter specification. You can specify multiple operation types to filter. Any additional syntax for other features, such as a column list or key definition, must follow the DML filter specification.

!dml(DML_type[,DML_type][,...])

Where DML_type is one of the following:

Examples

Example 1

The following example filters DELETE operations from being replicated to the target table.

Example 2

The following example filters DELETEs and INSERTs so that only UPDATEs are replicated to the target table. This example also shows how a DML filter is compatible with a column mapping specification.

View current DML filters

Use the verify config command to view the DML that is being filtered for each table in the configuration file. This command can be used for an active or inactive configuration file.

sp_ctrl> verify config myconfig

7: "SCOTT"."EMP" "SCOTT"."EMP" prodsys@o.proddb

Filter out >>>>> DELETES

Unique Key : (EMPLOYEE_ID)

Restrictions

• The copy and compare commands do not support tables that include DML filtering in their specifications.
• If there are multiple specifications of a source table in the configuration file, the DML filter specification must be identical for all of them. Multiple specifications of the same source table occur if you are routing to multiple targets without using a compound routing map (see Routing specifications in a configuration file), or if you are combining full-table replication with horizontally partitioned replication (see Configure horizontally partitioned replication).

Filter DML related to specific Oracle objects from replication

You can prevent SharePlex from replicating sequences, materialized views, and SQL*Loader direct-path loads. By default the replication of these objects is enabled.