- Remote Execution
The remote execution includes both remote capture and delivery for DB2 z/OS. Running Oracle GoldenGate off the z/OS server significantly reduces the MIPS consumption and allows the support of AES encryption and credential store management. - BATCHSQL is now supported by DB2 z/OS
- AES encryption and 64bit is supported.
- Credential Store Management
Users can use the built-in credential store to store and use the database logins - One build for DB2 z/OS 10.1 and 11.1
One download (build) of Oracle GoldenGate for DB2 z/OS is needed to replicat data from DB2 10.1 and 11.1. - TRANLOGOPTIONS DB2ZV11COMPATIBILITYMODE option (New in the December'17 Release)
The TRANLOGOPTIONS DB2ZV11COMPATIBILITYMODE option was added so that you can set the DB2 z/OS version.
Oracle GoldenGate 12.3 for DB2 Z/OS (#DB2z) introduces the following new features: Created: 9/4/2017, Last Updated: 1/4/2018
1 Comment
When using the new Remote Execution feature in Oracle GoldenGate 12.3 for DB2 z/OS, you need to install a DB2 client on the remote server. This blog provides a step by step example of the configuration.
The example uses the DB2 client comes with the DB2 server, you can use other DB2 clients based on the license you have.
1. Download and unzip the DB2 Server Software
# cd /u01/source # tar -xf v10.5fp7_linuxx64_server_t.tar # ls db2 db2checkCOL_readme.txt db2checkCOL.tar.gz db2ckupgrade db2_deinstall db2_install
2. Install the DB2 Server
# ./db2_install DBI1324W Support of the db2_install command is deprecated. Default directory for installation of products - /opt/ibm/db2/V10.5 *********************************************************** Install into default directory (/opt/ibm/db2/V10.5) ? [yes/no] yes Specify one of the following keywords to install DB2 products. SERVER CONSV EXP CLIENT RTCL Enter "help" to redisplay product names. Enter "quit" to exit. *********************************************************** SERVER Do you want to install the DB2 pureScale Feature? [yes/no] no DB2 installation is being initialized. Total number of tasks to be performed: 49 Total estimated time for all tasks to be performed: 1947 second(s) Task #1 start Description: Checking license agreement acceptance Estimated time 1 second(s) Task #1 end Task #2 start Description: Base Client Support for installation with root privileges Estimated time 3 second(s) Task #2 end Task #3 start Description: Product Messages - English Estimated time 14 second(s) Task #3 end ... The execution completed successfully. For more information see the DB2 installation log at "/tmp/db2_install.log.77921".
3. Add the License
# cd /opt/ibm/db2/V10.5/adm # ./db2licm -l Product name: "DB2 Advanced Enterprise Server Edition" License type: "Trial" Expiry date: "05/06/2018" Product identifier: "db2aese" Version information: "10.5" # ./db2licm -a /u01/source/db2ese_c.lic LIC1402I License added successfully. LIC1426I This product is now licensed for use as outlined in your License Agreement. USE OF THE PRODUCT CONSTITUTES ACCEPTANCE OF THE TERMS OF THE IBM LICENSE AGREEMENT, LOCATED IN THE FOLLOWING DIRECTORY: "/opt/ibm/db2/V10.5/license/en_US.iso88591" # ./db2licm -l Product name: "DB2 Enterprise Server Edition" License type: "CPU Option" Expiry date: "Permanent" Product identifier: "db2ese" Version information: "10.5" Enforcement policy: "Soft Stop" Features: IBM DB2 BLU Acceleration In-Memory Offering: "Not licensed" IBM DB2 Performance Management Offering: "Not licensed" IBM DB2 Encryption Offering: "Not licensed" IBM DB2 Business Application Continuity Offering: "Not licensed"
4. Create the DB2 Client
# groupadd db2cltg1 # useradd -g db2cltg1 -d /home/db2clnt1 -m db2clnt1 # passwd db2clnt1 Changing password for user db2clnt1. == XXXXX # cd /opt/ibm/db2/V10.5/instance/ # ./db2icrt -u db2clnt1 db2clnt1 DBI1446I The db2icrt command is running. DB2 installation is being initialized. Total number of tasks to be performed: 4 Total estimated time for all tasks to be performed: 309 second(s) Task #1 start Description: Setting default global profile registry variables Estimated time 1 second(s) Task #1 end ... The execution completed successfully. For more information see the DB2 installation log at "/tmp/db2icrt.log.139947". DBI1070I Program db2icrt completed successfully.
5. Configure the DB2 Client
In this step, we "cataloging" the TCP/IP node and DB which will be needed by the Oracle GoldenGate extract and replicat in the SOURCEDB & TargetDB parameter files. First, "su-ing" to the OS user that has access to the DB2 client libraries. You also need to include the ".../sqllib/lib64" and ".../sqllib/lib32" client libraries in your LD_LIBRARY_PATH environment variable and the "..../sqllib/bin" in the PATH environment variable. # su - db2clnt1
The following commands bind the remote ODBC packages needed in DB2 z/OS to allow remote connection from the LINUX server.
[db2clnt1]$ db2 catalog tcpip node PLEXU1 remote 147.11.0.12 server 7105 DB20000I The CATALOG TCPIP NODE command completed successfully. DB21056W Directory changes may not be effective until the directory cache is refreshed. [db2clnt1]$ db2 catalog db UB01 at node PLEXU1 DB20000I The CATALOG DATABASE command completed successfully. DB21056W Directory changes may not be effective until the directory cache is refreshed.
The key in the preceding step is the host name (147.11.0.12) and tcp port (7105) . Next, you should be able to login to DB2 z/OS from remote server with the z/OS user name and password as follows:
[db2clnt1]$ db2 connect to ub01 user ibmuser using passwd Database Connection Information Database server = DB2 z/OS 11.1.5 SQL authorization ID = IBMUSER Local database alias = UB01
Now, the DB2 client is ready to be used by Oracle GoldenGate.
Created: 12/1/2017, Last Updated: 1/3/2017, Thanks Kenal Wu and Mark Geisler for helping on this blog.
Oracle GoldenGate 12.3 introduces the the remote execution where Oracle GoldenGate runs off the mainframe machines. Starting from this release, Oracle GoldenGate no longer run on the mainframe server. This blog provides a quick summary. In Oracle GoldenGate 12.2, stored procedures will be installed on DB2 mainframe to capture the transactions and delivers the logical change records (LCR) to Oracle GoldenGate. Oracle GoldenGate for DB2 z/OS is installed on a server outside of mainframe server. The servers can be a zLinux partition in mainframe or a LINUX/AIX machine. Oracle GoldenGate processing including the trail file creations now runs on the remote servers. The feature significantly reduces the MIPS consumption and enables Oracle GoldenGate for Mainframe to provide advanced functions such as AES encryption and credential store management in 12.3. For the configuration example, please refer to How to Configure Remote Execution with Oracle GoldenGate for DB2 z/OS.
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:
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: > /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:
> 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. $ 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:
$ 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).
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;
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. 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 for DB2 z/OS supports all IBM supported DB2 z/OS versions (9.1, 10.1, & 11.1) running on IBM supported z/OS platforms (1.10, 1.11, 1.12, 1.13, 2.1, 2.2). (Please refer to certification matrix for detailed version support. The current version is 12.2.0.1+. Oracle GoldenGate for DB2 z/OS uses the IBM IFI (Instrumentation Facility Interface) API IFCID 306 to acquire change data and use ODBC API to deliver data. Oracle GoldenGate Extract and Replicat processes will use ODBC (Open Database Connectivity) to connect to the DB2 subsystem. For delivery, you can optionally install Oracle GoldenGate DB2Remote on a Linux, UNIX, or Windows system to deliver replicated data to the DB2 target on the z/OS system. Oracle GoldenGate (OGG) for DB2 z/OS performs non-intrusive data capture from DB2 z/OS, and has been widely used for the following use cases:
Last Updated: 1/20/2017
Yes, the feature is supported in Oracle GoldenGate 12.3. For Oracle GoldenGate 12.3, both GoldenGate for DB2 z/OS or GoldenGate for DB2 iSeries don't support batchSQL. The release notes in GoldenGate 12.2.0.1.160419OGGBP for Bug 22742935 shows: "Data is not always correctly replicated in BATCHSQL mode. The symptoms may include bad data in target tables, abnormal termination of replicat process, or dropping out of BATCHSQL mode. " Resources
Created: 10/27/2016, Last Updated: 1/4/2017
Oracle GoldenGate for DB2 z/OS doesn't replicat DDL operations. Therefore, if you have any DDL changes, you need to follow the process to stop the replication, perform the DDL operations on the source and the target database, and restart the replication.
Please refer to Oracle support note: Add Column(s) to DB2 for z/OS table (Doc ID 969767.1) for the details. You can consider using GoldenGate Event Handler to automate the process.
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: 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 z/OS 12.2.0.1.2 was released on 4/15/2016. You now can download the software from OTN and eDelivery. This is the first 12.2 release of Oracle GoldenGate for DB2 z/OS. The following new features are provided:
References
Answer: If you use ONLINE REORG without KEEPDICTIONARY parameter and the DB2 functionality implemented with PK19539, you will get this error. The error message is:
OGG-00800 Unable to decompress log record for table xxx.... The compression dictionary changed since the log record was written. |
GoldenGate for DB2 z/OSDiscuss the features, tip and tricks of GoldenGate for DB2 for z/OS. Archives
January 2018
Categories |