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.
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%'
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%'
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.
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'
Before you set new filtering condition pause APPLY process. After you have set it restart apply process. Without restart APPLY process filtering won't work.
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.
When target is not oracle database then avoid using inbuilt functions in filtering as same functions might not be available on target environment.
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 (as per above).
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