Loading....

Let us have a look at some GoldenGate column conversion functions – CASE and EVAL

To illustrate this example, we have a source and target table called EMPSAL and the structure of the tables in both databases is slightly different.

In the source table we have the JOB column and in the target database we have a TAX_BRACKET column.

In the first example, we will use CASE function to populate the target database TAX_BRACKET column based on the value for the JOB column in the source database.

In the second example, we will use the EVAL function to populate the target database TAX_BRACKET column based on a particular range of values for the SAL column in the source table.

Remember that since the table structure in source and target are different, we need to run the DEFGEN utility to generate a definitions file which we will then copy to the target GoldenGate environment and instead of using the ASSUMEDEFS parameter in the replicate parameter file, we use the SOURCEDEFS parameter instead to indicate the location of the definitions file which we have generated and copied over from the source GoldenGate environment.

Source


SQL> desc empsal
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ENAME                                              VARCHAR2(20)
 SAL                                                NUMBER
 JOB                                                VARCHAR2(12)

Target

SQL> desc empsal
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ENAME                                              VARCHAR2(20)
 SAL                                                NUMBER
 TAX_BRACKET                                        VARCHAR2(10)

We now create the definitions file for the EMPSAL table and copy the generated definitions file to the target GoldenGate environment.


GGSCI (sunos1) 4> edit params defgen

"/export/home/oracle/gg/dirprm/defgen.prm" 3 lines, 97 characters
defsfile ./dirsql/empsal.sql
userid ggs_owner password ggs_owner
table ggs_owner.empsal;

sunos1:/export/home/oracle/gg $ ./defgen paramfile /export/home/oracle/gg/dirprm/defgen.prm

sunos1:/export/home/oracle/gg/dirsql $ scp -rp empsal.sql oracle@sunos2:/export/home/oracle/gg/dirsql

Let us have a look at the Extract parameter file – quite normal and straight forward here.

extract ext
userid ggs_owner, password ggs_owner
rmthost sunos2 , mgrport 7809
rmttrail ./dirdat/gg
table ggs_owner.empsal;

On the target, these are the contents of the replicat parameter file. Note the COLPMAP clause. Since both the tables have common columns ENAME and SAL, we do not have to explicitly map them and we can just specify USEDEFAULTS.

The TAX_BRACKET column on the target table is being populated using the CASE function which evaluates the values for the JOB column in the source table.

If the value for JOB is ‘OPERATOR’, then the TAX_BRACKET column is assigned the value ‘LOW’. If it is ‘DBA’ then the TAX_BRACKET column is assigned the value ‘MID’ and if the value is ‘MANAGER’, then the TAX_BRACKET column is assigned the value ‘HIGH’. If the value for JOB is none of the values, then a default value of ‘UNKNOWN’ is assigned.

REPLICAT rep
sourcedefs /export/home/oracle/gg/dirsql/empsal.sql
USERID ggs_owner, PASSWORD ggs_owner
MAP ggs_owner.empsal, TARGET ggs_owner.empsal,
COLMAP (
usedefaults, tax_bracket=@case(JOB, "OPERATOR","LOW","DBA", "MID","MANAGER","HIGH", "UNKNOWN"));


Let us now insert some values into the source table.


SQL> insert into empsal values
  2  ('JOHN',10000, 'DBA');

1 row created.

SQL> insert into empsal values
  2  ('JACK',5000, 'OPERATOR');

1 row created.

SQL> insert into empsal values
  2  ('TOM',20000, 'MANAGER');

1 row created.

SQL>  insert into empsal values
  2  ('MARY', 6000, 'ANALYST');

1 row created.

SQL> commit;

Commit complete.


SQL> select * from empsal;

ENAME                       SAL JOB
-------------------- ---------- ------------
JOHN                      10000 DBA
JACK                       5000 OPERATOR
TOM                       20000 MANAGER
MARY                       6000 ANALYST

Let us now see how the data has been replicated and how the transformation has taken place on the target.



SQL> select * from empsal;

ENAME                       SAL TAX_BRACKE
-------------------- ---------- ----------
JOHN                      10000 MID
JACK                       5000 LOW
TOM                       20000 HIGH
MARY                       6000 UNKNOWN

Let us now truncate both the tables and see the next example using EVAL instead of CASE.

We stop both the extract and replicat processes and will now change the replicat parameter file on the target to include the EVAL function.

So this is how our replicat parameter file will look like.

REPLICAT rep
sourcedefs /export/home/oracle/gg/dirsql/empsal.sql
USERID ggs_owner, PASSWORD ggs_owner
MAP ggs_owner.empsal, TARGET ggs_owner.empsal,
COLMAP (
usedefaults, &
tax_bracket=@eval(sal < 10000,"LOW", sal >= 20000 , "HIGH",sal >= 10000, "MID"));

Start the extract and replicat processes again and let us now insert the same set of values in the source table as we did before and see how they are now replicated and transformed on the target.

SQL> select * from empsal;

ENAME                       SAL TAX_BRACKE
-------------------- ---------- ----------
JOHN                      10000 MID
JACK                       5000 LOW
TOM                       20000 HIGH
MARY                       6000 LOW

For those rows where the SAL value was less than 10000, a value of 'LOW' was assigned to the TAX_BRACKET column and if the value was equal to or greater than 10000 (but less than 20000), a value of 'MID' is assigned and all SAL values above 20000 are assigned 'HIGH'.

Have a read of the all the diferent column conversion functions mentioned in the GoldenGate reference guide and there are quite a few. Let me know if you found any others which are useful and kindly share your experience with the community!

Please follow and like us:

Last Update: May 4, 2017  

August 24, 2011 179 Gavin Soorma
Total 0 Votes:
0

Tell us how can we improve this post?

+ = Verify Human or Spambot ?

Add A Knowledge Base Question !

You will receive an email when your question will be answered.

+ = Verify Human or Spambot ?

Leave a Comment

Your email address will not be published. Required fields are marked *

*
*

Social media & sharing icons powered by UltimatelySocial
Back To Top

Add A Knowledge Base Question !

You will receive an email when your question will be answered.

+ = Verify Human or Spambot ?