Oracle GoldenGate 12.3 New Feature
Starting from Oracle GoldenGate 12.3, Oracle GoldenGate for DB2 z/OS supports remote execution [1]. Oracle GoldenGate for DB2 z/OS Remote Execution feature allows you to run Oracle GoldenGate capture and delivery process mainly on a server outside of the source and target database. The feature provides the following benefits:
- Reducing the MIPS consumption
- Allowing the support of AES encryption and the credential store management
Operating System of Remote Server: LINUX DB2 for z/OS DB Name: db2zos5 DB Version: 11.1.5 User Name: JWANG Password: oracle
Please also refer to the Fusion Middleware Installing and Configuring Oracle GoldenGate for DB2 z/OS documentation for the details.
1. Installing Oracle GoldenGate
Before installing Oracle GoldenGate, you need to make sure the remote system can connect to the DB2 z/OS database. This requires DB2 connect is installed. If you don't have any DB2 connect installed, IBM Data Server Driver for ODBC and CLI is the most lightweight driver and is recommended for most configurations, although the other drivers are suitable also.
After logging into LINUX box and confirm the connection to the DB2 on z/OS shown as follows:
1. Installing Oracle GoldenGate
Before installing Oracle GoldenGate, you need to make sure the remote system can connect to the DB2 z/OS database. This requires DB2 connect is installed. If you don't have any DB2 connect installed, IBM Data Server Driver for ODBC and CLI is the most lightweight driver and is recommended for most configurations, although the other drivers are suitable also.
After logging into LINUX box and confirm the connection to the DB2 on z/OS shown as follows:
> /home/mouellet/sqllib/bin/db2 (c) Copyright IBM Corporation 1993,2007 Command Line Processor for DB2 Client 10.5.0 ... db2 => connect to db2zos5 user jwang using oracle Database Connection Information Database server = DB2 z/OS 11.1.5 SQL authorization ID = JWANG Local database alias = DB2ZOS5
With the remote database connection confirmed, you can start installing Oracle GoldenGate on the LINUX box. You can the zip file from OTN or eDelivery and choose the one for your platform:
- Oracle GoldenGate 12.3.0.1.0 for DB2 for zOS 10.1, 11.1 on AIX (119 MB)
- Oracle GoldenGate 12.3.0.1.0 for DB2 for zOS 10.1, 11.1 on Linux x86-64 (57 MB)
- Oracle GoldenGate 12.3.0.1.0 for DB2 for zOS 10.1, 11.1 on zLinux (61 MB)
> unzip 123010_ggs_Linux_s390x_UDB111_64bit.zip > tar xvf ggs_Linux_x64_UDB111_64bit.tar
From the Oracle GoldenGate directory, run the GGSCI program: ggsci
$ ./ggsci Oracle GoldenGate Command Interpreter for DB2 Version 12.3.0.1.0 OGGCORE_MAIN_PLATFORMS_160808.1856 Linux, x64, 64bit (optimized), DB2 UDB 11.1 on Aug 9 2016 04:45:46 Operating system character set identified as UTF-8. Copyright (C) 1995, 2016, Oracle and/or its affiliates. All rights reserved
In GGSCI, create the Oracle GoldenGate working directories: create subdirs and confirm the DB connection.from GGSCI.
GGSCI> create subdirs Creating subdirectories under current directory /scratch/jiwang Parameter files /scratch/jiwang/dirprm: created Report files /scratch/jiwang/dirrpt: created Checkpoint files /scratch/jiwang/dirchk: created Distribution paths /scratch/jiwang/dirdsp: created Process status files /scratch/jiwang/dirpcs: created SQL script files /scratch/jiwang/dirsql: created Database definitions files /scratch/jiwang/dirdef: created Extract data files /scratch/jiwang/dirdat: created Temporary files /scratch/jiwang/dirtmp: created Credential store files /scratch/jiwang/dircrd: created Masterkey wallet files /scratch/jiwang/dirwlt: created Dump files /scratch/jiwang/dirtmp: already exists 2016-09-14 12:20:09 INFO OGG-03037 Session character set identified as UTF-8. 2016-09-14 12:20:09 INFO OGG-03037 Session character set identified as UTF-8. (...create the useridalias ggadmin) GGSCI> dblogin sourcedb db2zos5 useridalias ggadmin Successfully logged into database.
2. Configuring Stored Procedure on DB2 z/OS
First, you would create a folder to receive the installation package.
First, you would create a folder to receive the installation package.
$ ls $ mkdir gginstall $ cd gginstall
Check if the stored procedure is already installed. If the package is installed you would see the result as follows:
db2 => select schema,name from sysibm.sysroutines where schema like 'OGGIFI%'; SCHEMA NAME ---------------------------------------------------------------------------- --------- SQL0965W There is no message text corresponding to SQL warning "98" in the message file on this workstation. The warning was returned from module "DSNHTOKR" with original tokens "". SQLSTATE=01568 OGGIFI0001 OGGINITA OGGIFI0001 OGGINITB OGGIFI0001 OGGREADA OGGIFI0001 OGGREADB OGGIFI0001 OGGX2B 5 record(s) selected with 1 warning messages printed. Note: The two procedures are: OGGINITA - initialize the environment. OGGREADB - read the log.
If you don't see the packages in the query result, you then need to installed the stored procedures. Before the installation, please make sure you have the followings in your z/OS system:
- A library (PDSE) must exist on the z/OS system, and it must be in the authorized libraries list. This library will be in the location that the Oracle GoldenGate objects will reside in. If PDSE doesn't exist, you can create one.
- A WLM environment must exist and be APF authorized that references the PDSE from the preceding step. It is recommended that the number of TCBs is greater than one if you are planning to run more than one extract at a time.
$ unzip zOSPrograms.zip Archive: zOSPrograms.zip inflating: zOSPrograms.tar $ tar xvof zOSPrograms.tar x oggia001, 49152 bytes, 96 tape blocks
Copy the PL/SQL program files to the PDSE, please customized the name of the PDSE in the followng command:
$cp -X ogg[ir][ab][0-9]* "//'SYS4.WLMDS5E.USER.AUTHLOAD'"
Modified the example SQL file based on your environment setup (WLM environment and the DB buffer size).
- For DB2 11 and later version, use the demo_db2_setupb_os390.sql.
- For <DB2 10 and prior version, use the demo_db2_setupa_os390.sql.
DROP PROCEDURE OGGIFI0001.OGGINITA;
DROP PROCEDURE OGGIFI0001.OGGREADA;
COMMIT;
CREATE PROCEDURE OGGIFI0001.OGGINITA (
INOUT CONTEXT BINARY( 240 ),
OUT RCREADS SMALLINT,
OUT RCTESTA SMALLINT,
OUT DATASHR SMALLINT,
OUT BUFFER CHAR( 250 ),
IN WQLS BLOB( 16 )
)
RESULT SETS 0
EXTERNAL NAME OGGIA001
LANGUAGE C
NO SQL
PARAMETER STYLE GENERAL
WLM ENVIRONMENT <WLMDSXXA>
STAY RESIDENT YES
PROGRAM TYPE SUB
RUN OPTIONS 'H(,,ANY),STAC(,,ANY,),STO(,,,4K),BE(4K,,),
LIBS(4K,,),ALL31(ON)'
CONTINUE AFTER FAILURE
COMMIT ON RETURN
NO ASUTIME
NO LIMIT;
CREATE PROCEDURE OGGIFI0001.OGGREADA (
IN CONTEXT BINARY( 25 ),
OUT BUFFER BLOB( 32M )
)
RESULT SETS 0
EXTERNAL NAME OGGRA001
LANGUAGE C
NO SQL
PARAMETER STYLE GENERAL
WLM ENVIRONMENT <WLMDSXXA>
STAY RESIDENT YES
PROGRAM TYPE SUB
RUN OPTIONS 'H(,,ANY),STAC(,,ANY,),STO(,,,4K),BE(4K,,),
LIBS(4K,,),ALL31(ON)'
CONTINUE AFTER FAILURE
COMMIT ON RETURN
NO ASUTIME
NO LIMIT;
COMMIT;
DROP PROCEDURE OGGIFI0001.OGGREADA;
COMMIT;
CREATE PROCEDURE OGGIFI0001.OGGINITA (
INOUT CONTEXT BINARY( 240 ),
OUT RCREADS SMALLINT,
OUT RCTESTA SMALLINT,
OUT DATASHR SMALLINT,
OUT BUFFER CHAR( 250 ),
IN WQLS BLOB( 16 )
)
RESULT SETS 0
EXTERNAL NAME OGGIA001
LANGUAGE C
NO SQL
PARAMETER STYLE GENERAL
WLM ENVIRONMENT <WLMDSXXA>
STAY RESIDENT YES
PROGRAM TYPE SUB
RUN OPTIONS 'H(,,ANY),STAC(,,ANY,),STO(,,,4K),BE(4K,,),
LIBS(4K,,),ALL31(ON)'
CONTINUE AFTER FAILURE
COMMIT ON RETURN
NO ASUTIME
NO LIMIT;
CREATE PROCEDURE OGGIFI0001.OGGREADA (
IN CONTEXT BINARY( 25 ),
OUT BUFFER BLOB( 32M )
)
RESULT SETS 0
EXTERNAL NAME OGGRA001
LANGUAGE C
NO SQL
PARAMETER STYLE GENERAL
WLM ENVIRONMENT <WLMDSXXA>
STAY RESIDENT YES
PROGRAM TYPE SUB
RUN OPTIONS 'H(,,ANY),STAC(,,ANY,),STO(,,,4K),BE(4K,,),
LIBS(4K,,),ALL31(ON)'
CONTINUE AFTER FAILURE
COMMIT ON RETURN
NO ASUTIME
NO LIMIT;
COMMIT;
Run the SQL command in DB2 to create the Pl/SQL procedure:
$ db2 -tf demo_db2_setupb_os390.sql
Now, the installation is completed. You can start to configure Oracle GoldenGate replications.
3. Creating an Example Replication
First, let's create some tables: pm_members is the table on the source and pm_member_tgt is the table for the target.
3. Creating an Example Replication
First, let's create some tables: pm_members is the table on the source and pm_member_tgt is the table for the target.
db2 => create table pm_members (id smallint not null, name varchar(50), primary key (id)); db2 => describe select * from jwang.pm_members; Column Information Number of columns: 2 SQL type Type length Column name Name length -------------------- ----------- ------------------------------ ----------- 500 SMALLINT 2 ID 2 449 VARCHAR 50 NAME 4 db2 => create table pm_members_tgt (id smallint not null, name varchar(50), primary key (id)); db2 => describe select * from jwang.pm_members_tgt; Column Information Number of columns: 2 SQL type Type length Column name Name length -------------------- ----------- ------------------------------ ----------- 500 SMALLINT 2 ID 2 449 VARCHAR 50 NAME 4
The configure the replication, the parameter files are defined as follows:
extrmt01.prm: extract extrmt01 --trailcharsetascii sourcedb db2zos5, useridalias ggadmin exttrail ./dirdat/em transmemory directory(./dirtmp, 450m, 450m),transallsources 450m table jwang.pm_members; reprmt02.prm: replicat reprmt02 targetdb db2zos5 useridalias ggadmin map jwang.pm_members, target jwang.pm_members_tgt;
Add extract and replicat:
GGSCI> add trandata jwang.pm_members Logging of supplemental log data is enabled for table JWANG.PM_MEMBERS GGSCI>add extract extrmt01, tranlog DB2ZOS.JWANG, begin now GGSCI>add exttrail ./dirdat/em, extract extrmt01 GGSCI> add extract extrmt01, tranlog DB2ZOS5.JWANG, begin 2016-09-30 10:30 EXTRACT added. GGSCI> add exttrail ./dirdat/em, extract extrmt01 EXTTRAIL added. GGSCI> add checkpointtable jwang.checkpoint_globals Successfully created checkpoint table "jwang"."checkpoint_globals". GGSCI> add replicat reprmt02, exttrail ./dirdat/em, nodbcheckpoint REPLICAT added. GGSCI> add replicat reprmt02, exttrail ./dirdat/em, checkpointtable jwang.checkpoint_globals REPLICAT added.
Insert data on DB2 z/OS and compare the records in the source and target table:
insert into jwang.pm_members values (1, 'jinyu'); insert into jwang.pm_members values (2, 'Michel'); insert into jwang.pm_members values (3, 'Jack'); select * from jwang.pm_members_tgt; select * from jwang.pm_members;
Now, you have run the replication via the remote execution feature.
Resources
- Oracle GoldenGate 12.3 Documentation: Fusion Middleware Installing and Configuring Oracle GoldenGate for DB2 z/OS