Filter the data to be replicated

Dbvisit Replicate allow you to replicate only those data that fulfills certain condition. For example, only replicate data from the current year. You can set a common filtering condition for all DMLs or each type of DML can have different condition. You can configure filtration of data to be replicate in Step 3 of setup wizard. 

For filtering to work, the columns used in the conditions must always be included in the redo log. This can be achieved simply by forcing Oracle to include them for every DML operation on the table by creating a supplemental log group. Therefore when data is filtered for replication, enable supplemental logging for participating  column. It is also helpful when PL/SQL is used for conflict handling.

Click here to learn more about Supplemental logging and how to turn it on.

When using FILTER conditions with LOAD (which is used to instantiate/provision the data set at the target) note that the FILTER conditions will be invoked in this process. If this is not desired then you can add the filtering condition(s) after the baselining LOAD has occurred via the console (see bellow).

Filter for all DMLs

In following example, only those data where column jobcode starts with '1' will be replicated. In other words the condition must be satisfied in order for the rows to be replicated.

Filter the data to be replicated? If yes, use single condition for ALL DML operations or use CUSTOM conditions for each operation? (NO/ALL/CUSTOM) [NO] ALL 
Filter condition for table AVI.SAMPLE: [] jobcode like '1%'

Specific filter for each DML

In following example, different filtering criteria are configured for each type of DML.

Filter the data to be replicated? If yes, use single condition for ALL DML operations or use CUSTOM conditionsfor each operation? (NO/ALL/CUSTOM) [NO] CUSTOM
Filter condition for INSERT for table AVI.SAMPLE (empty means no condition): [] SDATE >= to_date('1998-01-01','YYYY-MM-DD')
Filter condition for UPDATE (old values) for table AVI.SAMPLE (empty means no condition): [ SDATE >= to_date('1998-01-01','YYYY-MM-DD')]
Filter condition for UPDATE (new values) for table AVI.SAMPLE (empty means no condition): [ SDATE >= to_date('1998-01-01','YYYY-MM-DD')]
Filter condition for DELETE for table AVI.SAMPLE (empty means no condition): [ SDATE >= to_date('1998-01-01','YYYY-MM-DD')] jobcode like '1%'

Set up filter from the console

Although it is normal to set the filtering when you initially use the Setup Wizard you can also set up filtering after the fact.

Make sure that the table is already prepared.  The normal procedure is to run the command with the OFFLINE feature.

Below are examples  for each filter conditions.

  1. Inserts equal to 10 for deptno column on the SCOTT.EMP table are replicated.  Any other values would not be replicated.

      2. Only updates when the value of existing comm greater than 200 would be replicated for theSCOTT.EMP table.

      3. Replicates only if the values are greater than 20 for the deptno column for SCOTT.EMP table

      4. Deletes only if the condition satisfies for the column job='CLERK' are replicated and other values are not replicated.

dbvrep> FILTER OFFLINE TABLE SCOTT.EMP on INSERT SET TO deptno = 10
dbvrep> FILTER OFFLINE TABLE SCOTT.EMP on PRE-UPDATE SET TO comm >200
dbvrep> FILTER OFFLINE TABLE SCOTT.EMP on POST-UPDATE SET TO deptno >20
dbvrep> FILTER OFFLINE TABLE SCOTT.EMP on DELETE SET TO job='CLERK'

The correct steps are:

  1. Shutdown apply process
  2. Set filter condition from the console
  3. Start the apply process

Do not put a semicolon ; at the end of the filter. This will cause APPLY to stop as the syntax will be placed in the SQL statements and the database won't know what to do with the semi colon.

Advanced filters

You can use any valid SQL syntax for the WHERE clause to be used as a filter.

The following filter will only update DEPT table if there are no employees in the given department:

dbvrep> FILTER OFFLINE TABLE scott.dept ON PRE-UPDATE SET TO deptno not in (select deptno from scott.emp) 

In the next example we use the CASE statement, built-in function and PL/SQL user function to only update those employees who

  • have salary high enough (based on their location) or
  • are with the company long enough or
  • fulfill the conditions defined by PL/SQL user function 
case when deptno in (select deptno from scott.dept where loc='NEW YORK') then 3000
     when deptno in (select deptno from scott.dept where loc='DALLAS') then 2000
     else 1500 end < sal
or months_between(sysdate, hiredate) > 436
or scott.my_plsql_function(ename) = 1
dbvrep> FILTER OFFLINE TABLE scott.emp ON PRE-UPDATE SET TO case when deptno in (select deptno from scott.dept where loc='NEW YORK') then 3000 when deptno in (select deptno from scott.dept where loc='DALLAS') then 2000 else 1500 end < sal or months_between(sysdate, hiredate) > 436 or scott.my_plsql_function(ename) = 1

User defined function scott.my_plsql_function must be called using the schema name. User dbvrep must be granted an execute privilege on this function.

User defined function scott.my_plsql_function only needs to be defined on target database. There's no need to create it on source DB too.

When target is not oracle database then avoid using inbuilt functions in filtering as same functions might not be available on target environment.

Disable filtering

To disable filtering use same syntax, but with blank condition as:

dbvrep> FILTER OFFLINE TABLE SCOTT.EMP on INSERT SET TO
dbvrep> FILTER OFFLINE TABLE SCOTT.EMP on PRE-UPDATE SET TO

Filter deletes at a transaction level

       Please see : https://support.dbvisit.com/hc/en-us/articles/216676057-Filtering-Deletes-at-a-Transaction-Level-so-Data-is-not-deleted-in-APPLY