Let's look at an example of creating a one-way replication between two DB2 z/OS databases with Oracle GoldenGate for DB2 z/OS 12.2 and later versions.
​First, we need to create the source and target tables shown as follows:
​First, we need to create the source and target tables shown as follows:
Source: create table pm_members (id smallint not null, name varchar(50), primary key (id)); Target: create table pm_members_tgt (id smallint not null, name varchar(50), primary key (id));
Then, we create the extract process. The parameter file is shown as follows:
extract extrmt01 sourcedb db2ds5e, userid jwang, password xxx exttrail ./dirdat/em transmemory directory(./dirtmp, 450m, 450m),transallsources 450m table jwang.pm_members;
In the parameter file the db2ds5e is the database name, jwang is the user name for the DB2 z/OS login. In GGCS, we create the extract with the following commands:
GGSCI> dblogin sourcedb db2ds5e userid jwang password xxx 2016-09-15 14:03:41 INFO OGG-03037 Session character set identified as UTF-8. Successfully logged into database. GGSCI> add trandata jwang.pm_members Logging of supplemental log data is enabled for table JWANG.PM_MEMBERS GGSCI> add extract extrmt01, tranlog DB2DS5E.JWANG, begin 2016-09-15 14:00 EXTRACT added. GGSCI> add exttrail ./dirdat/em, extract extrmt01 EXTTRAIL added.
In GGCS, we can then create the repliat with the following commands:
replicat reprmt02 targetdb db2ds5e userid jwang password xxx map jwang.pm_members, target jwang.pm_members_tgt;
GGSCI> dblogin sourcedb db2ds5e userid jwang password temppass 2016-09-15 14:13:02 INFO OGG-03037 Session character set identified as UTF-8. Successfully logged into database. GGSCI> add checkpointtable jwang.checkpoint_globals Successfully created checkpoint table "jwang"."checkpoint_globals". GGSCI> add replicat reprmt01, exttrail ./dirdat/em, checkpointtable jwang.checkpoint_globals REPLICAT added.
We then can start the extract and replicat with the following GGSCI command:
GGSCI> start * GGSCI> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT RUNNING EXTRMT01 00:10:49 00:00:03 REPLICAT RUNNING REPRMT01 00:00:00 00:00:02
Next, let's test some replication. First, we insert a new record to the source table:
insert into jwang.pm_members values (1, 'jinyu');
Checking the replication status, we can get the following output:
GGSCI (slc09uww) 11> stats extrmt01 Sending STATS request to EXTRACT EXTRMT01 ... Start of Statistics at 2016-09-15 14:30:04. Output to /scratch/jiwang/./dirdat/em: Extracting from JWANG.PM_MEMBERS to JWANG.PM_MEMBERS: *** Total statistics since 2016-09-15 14:29:32 *** Total inserts 1.00 Total updates 0.00 Total deletes 0.00 Total discards 0.00 Total operations 1.00 *** Daily statistics since 2016-09-15 14:29:32 *** Total inserts 1.00 Total updates 0.00 Total deletes 0.00 Total discards 0.00 Total operations 1.00 *** Hourly statistics since 2016-09-15 14:29:32 *** Total inserts 1.00 Total updates 0.00 Total deletes 0.00 Total discards 0.00 Total operations 1.00 *** Latest statistics since 2016-09-15 14:29:32 *** Total inserts 1.00 Total updates 0.00 Total deletes 0.00 Total discards 0.00 Total operations 1.00 End of Statistics.
This means the DML operation is capture. Then we can check on the target table side.
db2 => select * from pm_members_tgt; ID NAME ------ -------------------------------------------------- 1 jinyu
Now, this concludes the one-way replication example.
Resources
- Oracle GoldenGate for DB2 on zOS Tutorial
- Oracle GoldenGate Documenation: ADD EXTRACT