Loading....

Oracle Goldengate provides a mechanism for capturing the before images of the row before any modifications were made to the row. Something very similar to the undo segment concept of the Oracle database.

This can be done using the GETUPDATEBEFORES parameter in either the Extract or Replicat parameter file which will essentially control whether or not the before images of updated columns are included in the records which are processed by GoldenGate.

We can use this not only for conflict resolution purposes but more importantly by comparing before and after images, we can identify net results of transactions, perform some delta calculations and also to maintain a transaction history.

Let us for example see a case where we are storing some currency exchange data in a table which is naturally subject to changes on a continuous basis because the currency rates naturally fluctuate over time.

We would like to maintain a history of such currency exchange rate changes and we can use GoldenGate for this purpose.

On the source database we have a CURRENCY_RATES table with the following structure.

SQL> desc currency_rates
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 TRAN_DATE                                          DATE
 CURRENCY_CODE                                      VARCHAR2(4)
 CURRENCY_DESC                                      VARCHAR2(20)
 EXCHANGE_RATE                                      NUMBER

On the target database the same table has some additional columns which we will use for transaction history.

SQL> desc currency_rates
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 TRAN_DATE                                          DATE
 CURRENCY_CODE                                      VARCHAR2(4)
 CURRENCY_DESC                                      VARCHAR2(20)
 EXCHANGE_RATE                                      NUMBER
 LAST_UPDATE                                        DATE
 TRAN_TYPE                                          VARCHAR2(20)
 BEFORE_AFTER 						    VARCHAR2(10)

On the source, we have configured an EXTRACT parameter file with the following parameters:

GGSCI (indvdb01) 7> view params SOORMA
EXTRACT soorma
USERID idit_prd, PASSWORD idit_prd
RMTHOST insodb02, MGRPORT 7809
RMTTRAIL ./dirdat/yy
GETUPDATEBEFORES
TABLE idit_prd.currency_rates;

On the target, we have configured a REPLICAT parameter file with the following parameters:

REPLICAT soorma
SETENV (NLS_LANG="AMERICAN_AMERICA.WE8ISO8859P1")
SETENV (ORACLE_SID=GGDB2)
ASSUMETARGETDEFS
USERID idit_prd,PASSWORD idit_prd
INSERTALLRECORDS
MAP idit_prd.currency_rates, TARGET idit_prd.currency_rates, &
COLMAP (USEDEFAULTS, LAST_UPDATE = @GETENV ("GGHEADER", "COMMITTIMESTAMP"), &
TRAN_TYPE=@GETENV ("GGHEADER", "OPTYPE"), &
BEFORE_AFTER=@GETENV("GGHEADER","BEFOREAFTERINDICATOR"));

Now on the source database, we make some changes to the CURRENCY_RATES table – we insert a row and then make two updates to the same row.

SQL> insert into currency_rates
  2  values
  3  (sysdate,'USD','US Dollar',1.01);
 
1 row created.
 
SQL> commit;
 
Commit complete.
 
SQL> update currency_rates
  2  set
  3  EXCHANGE_RATE=1.15
  4  where  CURRENCY_CODE='USD';
 
1 row updated.
 
SQL> commit;
 
Commit complete.
 
SQL> update currency_rates
  2   set
  3   EXCHANGE_RATE=1.10
  4  where  CURRENCY_CODE='USD';
 
1 row updated.
 
SQL> commit;
 
Commit complete.

SQL> select * from currency_rates;
 
TRAN_DATE CURR CURRENCY_DESC        EXCHANGE_RATE
--------- ---- -------------------- -------------
13-MAY-11 USD  US Dollar                    1.10

Let us now see how many rows the same CURRENCY_RATES table has on the target database

We can see that the table has got data which shows both the values of the row before the update was done as well as after the update was done and in this case by using this data, we can track trends in currency rate changes for example over a period of time.

SQL> select * from currency_rates;
 
SQL> /
 
TRAN_DATE CURR CURRENCY_DESC        EXCHANGE_RATE LAST_UPDA TRAN_TYPE  BEFORE_AFT
--------- ---- -------------------- ------------- --------- ---------- ----------
13-MAY-11 USD  US Dollar                     1.01 13-MAY-11 INSERT     AFTER
13-MAY-11 USD  US Dollar                     1.01 13-MAY-11 SQL COMPUP BEFORE
13-MAY-11 USD  US Dollar                     1.15 13-MAY-11 PK UPDATE  AFTER
13-MAY-11 USD  US Dollar                     1.15 13-MAY-11 SQL COMPUP BEFORE
13-MAY-11 USD  US Dollar                      1.1 13-MAY-11 PK UPDATE  AFTER

We can use a query on these lines to find out by how much has the exchage rate risen or fallen as well.

 SELECT AFTER.CURRENCY_CODE, AFTER.EXCHANGE_RATE, AFTER.EXCHANGE_RATE -BEFORE.EXCHANGE_RATE
FROM CURRENCY_RATES AFTER, CURRENCY_RATES BEFORE
 WHERE AFTER.CURRENCY_CODE = BEFORE.CURRENCY_CODE AND
AFTER.BEFORE_AFTER = 'AFTER' AND BEFORE.BEFORE_AFTER = 'BEFORE'
and after.tran_type='PK UPDATE'

SQL> /
 
CURR EXCHANGE_RATE AFTER.EXCHANGE_RATE-BEFORE.EXCHANGE_RATE
---- ------------- ----------------------------------------
USD            1.1                                      .09
USD           1.15                                      .14
USD            1.1                                     -.05
USD           1.15                                        0

Please follow and like us:

Last Update: May 8, 2017  

June 9, 2011 132 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 ?