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
Popular Posts