Objects Filtering

Filters are used by Apply Process to filter out data while processing DML operations. The filter should be configured for either single table or group of the tables and at least for one DML operation. The filtering statement might also be provided, which is to be used to construct “where” condition of the DML statement. If the filtering statement is not provided, then the Apply Process will ignore this DML operation.

The syntax used is:

where:

• id – The id, which is unique to each Filter has the purpose of determining the order in which the Filter is used by the Apply Process.
• table_mask – It is used to specify list of the tables. The format is schema _name.table_name and the % symbol may be used in order to match any number of characters.
• dml_operations – List of the DML operations separated by comma. The valid DML operations are: INSERT, UPDATE,DELETE.
• filter_statement – The statement to be used to build the “where” clause of DML operation. The operation is skipped for the rows that satisfy the statement.

A filter is a parameter of the Apply Process. In repcli the id is part of the name, and in the REST API it is one [name, value] pair, for example ["filter=1", "rule=(scott.logs;DELETE;\"\")"]. It is set with Prepare Apply Process or Alter Process, and filter=filterID,default removes it. For an Amazon S3 or an Apache Kafka target a filter can only skip operations, it has no condition. In the Web UI the filters are on the tab “Data Filter” of the Apply Process Configuration (see Edit Replication Processes).

Examples

The following examples show various ways of defining the Filter parameters:

• Skip any “delete” operations for “logs” table in “scott” schema:

• Skip any “delete” operations for the dbo.orders table if processed column equal to “1”:

• Skip any DML operations for the dbo.emp table if the info column has “do_not_replicate” value:

• Do not insert record into the dbo.acct table if the record with the same id already exists: