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.
- 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:
- Shutdown apply process
- Set filter condition from the console
- 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
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
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