This article will demonstrate heterogeneous replication using OGG from DB2 to Oracle.
1. Pre-requisites
OGG is installed on source and target systems.
2. Environment
DB2 on Mainfram Z/OS
Oracle Database : 11.2.0.3
3. Configuration on Source Server ( DB2 )
A. Parameter Files
Place all parameter files in the $GG_HOME/dirprm directory.
Extract Capture
> cat cdb2.prm extract cdb2 --trailcharsetascii sourcedb db2t, userid ggcap, password ggcap exttrail ./dirdat/lt transmemory directory(./dirtmp, 450m, 450m),transallsources 450m table user.table1;
Extract Data Pump
> cat pdb2.prm extract pdb2 rmthost target_server, mgrport 7809 rmttrail ./dirdat/rt passthru table user.table1;
Extract for Initial Load
> cat ildb2.prm extract ildb2 sourceistable sourcedb db2t, userid ggcap, password ggcap rmthost target_server, mgrport 7809, tcpbufsize 1048576, tcpflushbytes 1048576 rmtfile ./dirdat/la, megabytes 2048, maxfiles 999 TABLE user.table1;
B. Add Trandata
cd $GG_HOME > ./ggsci GGSCI> DBLogin sourcedb db2t, userid ggcap, password ggcap</strong> GGSCI> add trandata user.table1 GGSCI> exit
C. Source Table Definitions
> cd $GG_HOME/dirdef > cat defgen_db2t.prm DEFSFILE dirdef/db2t_source_tabs.def, PURGE sourcedb db2t, userid ggcap, password ggcap TABLE user.table1; > cd $GG_HOME >./defgen paramfile </strong><strong>./dirdef/defgen_db2t.prm NOEXTATTR
When done, copy the output file (dirdef/db2t_source_tabs.def) over to the target database server and put it in the $GG_HOME/dirdef directory.
D. Create and Start the Extract Processes
> ./ggsci GGSCI> DBLogin sourcedb db2t, userid ggcap, password ggcap GGSCI> add extract cdb2 , tranlog DB2T.BSDS01 , begin now GGSCI> add exttrail ./dirdat/lt, extract cdb2 GGSCI> add extract pdb2, exttrailsource ./dirdat/lt GGSCI> add rmttrail ./dirdat/rt, extract pdb2 GGSCI> start extract cdb2 GGSCI> start extract pdb2
E. Create and Start the Initial Load Capture Process
GGSCI> add extract ildb2 , sourceistable GGSCI> start extract ildb2
3. Configuration on Target Server ( Oracle)
A. Parameter Files
Place all parameter files in the $GG_HOME/dirprm directory.
Replicat
> cat rdb2.prm REPLICAT RDB2 SETENV (ORACLE_HOME="/u01/app/oracle/product/11.2.0.3/dbhome_1") SETENV (ORACLE_SID="ORA11G") SETENV (NLS_LANG="AMERICAN_AMERICA.AL32UTF8") USERID ggrep, PASSWORD ggrep DiscardRollover at 23:00 ReportRollover at 23:00 ReportCount Every 5 Minutes, Rate StatOptions ReportFetch DiscardFile ./dirrpt/RDB2.dsc, Append, Megabytes 100M HandleCollisions SOURCEDEFS ./dirdef/db2t_source_tabs.def DbOptions SuppressTriggers DbOptions DeferRefConst MAP user.table1, table1 user.table1;
Initial load Replicat
> cat ilrdb2.prm replicat ilrdb2 END RUNTIME SETENV(ORACLE_HOME="/u01/app/oracle/product/11.2.0.3/dbhome_1") SETENV(ORACLE_SID="ORA11G") SETENV(NLS_LANG="AMERICAN_AMERICA.AL32UTF8") UserID ggrep, Password ggrep SOURCEDEFS ./dirdef/db2t_source_tabs.def DISCARDFILE ./dirrpt/ilrdb2.dsc, Purge MAP user.table1, TARGET user.table1;
B. Initial load Steps – File to Replicat
After the initial load process from DB2 has completed, then continue with the configuration of the initial load replicat.
Configure and Start the Initial-Load Replicat
> cd $GG_HOME >./ggsci GGSCI> DBLogin UserID ggrep, Password ggrep GGSCI> add replicat ilrdb2, ExtTrail ./dirdat/aa, CheckPointTable ggrep.ggs_checkpoint GGSCI> start replicat ilrdb2
After the initial-load replicat is complete, continue on to the next step.
Configure and Start the Change Synchronization Replicat
> cd $GG_HOME >./ggsci GGSCI> add replicat rdb2, ExtTrail ./dirdat/rt, CheckPointTable ggrep.ggs_checkpoint GGSCI> start extract rdb2
Monitor the replicat for any errors and the current time and position of the records it is applying.
Next, verify that the replicat has passed the point where the initial-load replicat stopped. You can compare both time and the RBA by looking in the rdb2.rpt report file and the current position of the change synchronization replication as shown by the “info rdb2” command.
Turn Off Handle Collisions
> cd $GG_HOME >./ggsci GGSCI> send replicat rdb2, nohandlecollisions
Also, edit the parameter file and comment out the HANDLECOLLISIONS line so that the next time the replicat is restarted, this parameter will not take effect.
GGSCI> edit params rdb2 REPLICAT RDB2 SETENV (ORACLE_HOME="/u01/app/oracle/product/11.2.0.3/dbhome_1") SETENV (ORACLE_SID="ORA11G") SETENV (NLS_LANG="AMERICAN_AMERICA.AL32UTF8") USERID ggrep, PASSWORD ggrep DiscardRollover at 23:00 ReportRollover at 23:00 ReportCount Every 5 Minutes, Rate StatOptions ReportFetch DiscardFile ./dirrpt/RDB2.dsc, Append, Megabytes 100M --HandleCollisions SOURCEDEFS ./dirdef/db2t_source_tabs.def DbOptions SuppressTriggers DbOptions DeferRefConst MAP user.table1, table1 user.table1;
