Steps for performing the Various filtering method between two oracle databases through initial Data load method

We will filter the records that are extracted on the source and applied on the target – only rows where the country column value equals “UAE” in the “University” table will be considered for extraction.

1. Make sure to create a same table structure on source and Target Machines and manager process must be running.

2. Enable supplemental redo logging on the tables to be replicated

3. Sample Table structure:

Create table sender.University (studcode number(10),studname varchar2(10),country varchar2(10) , constraint uty_key unique(studcode));
Create table receiver.University (studcode number(10),studname varchar2(10),country varchar2(10) , constraint uty_key unique(studcode));


On Source Machine

Step 1 :GGSCI> add extract fillter, sourceistable

Step 2 : GGSCI> edit params fillter

Extract fillter
Userid ogg@goldengate:1521/source,password ****
Rmthost goldengate, mgrport 7810
Rmttask replicat,Group repl2
Table sender.university, FILTER ( @STRFIND ( city, “UAE” > 0));

Step 3 :GGSCI > start extract fillter

On Target Machine

Step 1 :GGSCI> add replicat repl2, specialrun

Step 2: GGSCI> edit params repl2

Replicat repl2
Userid ogg@goldengate:1521/target,password ****
assumetargetdefs
MAP sender. university, TARGET receiver.university;

Step 3: GGSCI> start replicat repl2


The output of the filter condition:

On Source Machine :

An insert sample statements for checking the replication

Insert into sender.university( 100,’SAM’,’UAE’)
Insert into sender.university( 101,’SAM’,’UK’)
commit;


On Target Machine :

If the country value is “UAE” only should replicate to Target Machine from the Source Machine as per the filter condition.

SQL> select * from receiver.university;

Studcode Studname Country
100 SAM UAE

What is FILTER Keyword?

The FILTER clause offers us more functionality than the WHERE clause because you can employ any of GoldenGate’s column conversion functions to filter data, whereas the WHERE clause accepts basic WHERE operators.

For example we can use standard arithmetic operators like ‘+’,'-’,'/’,'*’ or comparison operators like ‘>’,’<', '=' as well as GoldenGate functions like @COMPUTE, @DATE, @STRFIND, @STRNUM etc

For example we can use the STRFIND function as part of the Extract parameter file to only extract records from the table that match a particular string value as shown below.


TABLE sender.university,FILTER (@STRFIND (city, “LONDON”) > 0)


Another few examples for filtering

Filtering Columns

Suppose that instead of extracting all the columns in the Employees table, you’re only interested in the EMPLOYEE_ID,FIRST_NAME and LAST_NAME columns. You can do this by adding the following to your extract TABLE Parameter.

TABLE HR.EMPLOYEES, COLS (employee_id,first_name,last_name)

In the example, all columns are ignored except the three columns listed in the COLS parameter. Keep in mind that if you use column filtering in your data pump extract, you can no longer use passthru mode.



Another example for Column filtering is when you want all the columns in a table to be extracted except for a few columns. Let’s assume you want to extract the entire

EMPLOYEES table except for the EMAIL column. You can do that in the Extract with COLEXCEPT parameter, as shown in the following example:

TABLE HR.EMPLOYEES COLSEXCEPT (email);

Keep in mind that you can’t exclude key columns with COLSEXCEPT.



Filtering Rows
Let’s look at another example using filtering based on the DML operation, such as insert, update, or delete. Suppose you only want to replicate deletes if the employee’s monthly salary is less than 1000.

You can set up a filter as shown next that only executes the filter for delete statements. This example
filters using the Extract TABLE parameter


Table HR.EMPLOYEES,
FILTER (ON DELETE, SALARY / 12 < 1000);


You’ve seen a few examples of the extensive capabilities of Goldengate to filter tables,rows and columns .