When are filters applied

[Yves_s]

Hello,

I have a question about filters, I would like to know when the filter is applied.

During a full load, he filter is set when reading the table or juste before writing to the target?

same question for cdc mode.

thank you for your reply 

Yves Savean

[john_wang]

Hello @Yves_s ,

This is a good question however it's too wide to cover all scenarios. I'm trying to list out the common behaviors.

There are 2 places we define a filer: Table settings level (support in all versions), and Global rules filter (which is supported from Replicate version 2021.5).

For both types of filters, the filter operations can be performed in source backend database,  and/or in Replicate server by Replicate sorter component, all records are filtered prior to send to target applying component.

Table settings level (FL means "Full Load")

Filter type	Mode	Filtering  processed position
Filter Conditions	FL + CDC	process on source DB if possible, or on Replicate Server
Record Selection Condition	FL + CDC	process on Replicate server
Fullload Passthru Filter	FL mode ONLY	process on source DB

Global rules filter

Filter type	Mode	Filtering processed position
Filter by columns	FL + CDC	process on Replicate server
Filter by record attributes	CDC mode ONLY	process on Replicate server

Take note Replicate try to perform the filter on source DB if possible to minimize the network data transferring volume. However if you want to use complex filter conditions (for example with "and" and "or" conditions etc) then the filter will be done on Replicate server.

Let me know if you need any additional information.

Regards,

John.

[Yves_s]

thanks for these informations,

if we take the case of a full load db2 z to kafka with table filter

how it works for the cases:

first case : column filter(primary key)
second case : columns filter(composite key(2 or more))
third case : filter between 2 dates written in a column

[john_wang · Accepted solution]

Hello @Yves_s ,

For a given scenario, you can try to find out the filter expression from task log file. The steps are:

1. Enable SOURCE_UNLOAD to Verbose;

2. Run the Full Load task.

3. Check the task log file to see if the source unload statement contains "WHERE" clause or not. 

     For example below statement does NOT contain the "WHERE" clause:

2021-11-11T17:45:19:246029 [SOURCE_UNLOAD   ]V:  Construct statement execute internal: 'SELECT `id`,`name`  FROM `sourcedb`.`kit`'

     If the filter takes action in source database then Replicate adds "WHERE" sub-clause in above SQL.

Let me know if you need additional assistance.

Regards,

John.