FILTER PARAMETER

Steps for performing filtering the data between two oracle databases through initial load

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 (or use a data definitions file)

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

Test/Verification:

The output of the filter condition:

On Source Machine :

Sample insert statements for checking the replication

Insert into sender.university values ( 100,’SAM’,’UAE’);

Insert into sender.university values ( 101,’SAM’,’UK’);

commit;

On Target Machine :

The filter will filter records where the country is “UAE”.

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)

----------------------------All the best------------------------------------------------