Category: Tutorial

Oracle GoldenGate for Informix - Six Tutorials to Help You Get Started

12/22/2015

0 Comments

The following are the tutorials to help you get started with Oracle GoldenGate for Informix.

Oracle GoldenGate for Informix: Support and Feature Overview
Oracle GoldenGate for Informix Installation
- Making Informix IDS Database Ready for Replication
- Installing and Configuring ODBC
- Installing Oracle GoldenGate
Examples
- Initial loading and one-way replication from Informix IDS to Informix IDS database
- I nitial loading and one-way replication from Informix IDS to Oracle database
- Dealing with special characters
Oracle GoldenGate for Informix - Frequently Asked Questions

For any comments or questions, you can send messages to me @jinyu512 with subject #ogginformix, Let's get started!

0 Comments

Oracle GoldenGate Globalization

7/2/2015

2 Comments

Created: 5/6/2014, Last Updated: 7/2/2015

If you are new to the globalization concept, please read this: What is Globalization?.

Summary
When using Oracle GoldenGate, it's important to know how the character-based content are encoded, processed and converted during in data replications. This blog provides an overview of this topics by explaining the key concepts, describing the processes and answering the top FAQs.

Key Concepts
There are four types of character sets related to Oracle GoldenGate globalization processing:

Operating system
Terminal
DB character set, DB N-charset
DB client/session

Oracle GoldenGate components and processes are related to the encoding of these character sets in the following ways:[1]

1. GGSCI

Always operates in the character set of the local operating system. (OS)

2. Trail File

File name is encoded based on the local operating system (OS).
UTF-8 is used for table name and user tokens.
Source database character encoding is used for CHAR/VARCHAR/CLOB.
UTF-16 is used for NCHAR/NVARCHAR/NCLOB.

3. Parameter File

File name is encoded based on the local operating system (OS).
Content is encoded based on the OS encoding or the CHARSET parameter setup.

4. Defgen File

File name is encoded based on the local operating system (OS).
Content is encoded based on the OS encoding or the CHARSET parameter setup.

5. Obey File, Error Log File, Report File, Trace File, Discard File

File name is encoded based on the local operating system (OS).
Content is encoded based on the local operating system (OS).

6. Session
Sessions refer to the connection session Oracle GoldenGate extract and replicat processes to the source/target data store. Oracle GoldenGate extract and replicat processes set up the session character set when connecting to the databases.

For Oracle databases, the session character set is set to be the same as the database character set for both extract and replicat.
For Sybase, Teradata and MySQL, the session character set is taken from the SESSIONCHARSET option of SOURCEDB and TARGETDB, or from the SESSIONCHARSET parameter set globally in the GLOBALS file. The GLOBAL setup has lower priority.
For other database types, it is obtained programmatically. The session character set is setup to be the same as the source database for extract and the same as the target database for replicat.

Oracle GoldenGate processes use the session character set to communicate and transfer data with databases. This affects the operations like SQL queries, fetches (FETCHCOLS), and applying data.

The globalization process is explained as shown in the following diagram:

The basic rules include:

No/minimum character set conversion on the source. Except for DB2 z/OS and DB2 for iSeries, extractions on all databases doesn't involve character set conversion.
Single character set in the trail file.
On the replicat side, the target database character set should be an equivalent or a super-set of the source database character set. If not, use CHARMAP to map unsupported characters before apply data to the target.
The session character set should be the same as the database character set to avoid unnecessary conversion. This is especially true for MySQL and Sybase.

Oracle GoldenGate Character Set Conversion Process

The discussion now based on the current version of Oracle GoldenGate: 12.1.2.1.1. If you'd like to know prior version support and the differences across releases, please read this: What's New in Oracle GoldenGate Globalization Support?

Let's learn this from some examples:

Example 1: Using NOCHARSETCONVERSION to let Oracle GoldenGate use target client libraries to perform the character set conversion.

Running replication from Tandem to DB2 z/OS.
The trail file from the source is in ASCII.
The target DB2 z/OS database is in EBCDIC.

Solution: Using Oracle GoldenGate 11.2.1, the replication is set up with NOCHARSETCONVERSION to use the target client library for character set conversion. The approach reduced the resource consumption.

Example 2: How to use CHARMAP for invalid character set replacement?

Example 3: Replicat MySQL Database with DB Charset is UTF8 but one column in UTFMB4
DB charset : UTF8
One of Table is,
col1 : UTF8
col2 : UTF8
col3 : UTF8MB4
....
Solution: Utf8mb4 is a superset of utf8. In this case the SESSIONCHARSET can be set to be UTF-8.

References

Oracle Support Doc 1500608.1: Oracle GoldenGate - A Guide to Globalization aspects when working with OGG
Oracle GoldenGate 12.1.2 Documentation - Supported Character Set, Supported Local

2 Comments

Oracle GoldenGate for Informix - Dealing with International Characters

12/10/2014

0 Comments

Notes:
If you are not familiar with Oracle GoldenGate's handling of character set encoding, please read Oracle GoldenGate Globalization first.

For Informix, Oracle GoldenGate extract and replicat both use the database characters (DB_LOCAL) as the session character set. There is no need to set the SESSIONCHARSET parameter. For database character set, you can check the Informix IDS database character set with the following SQL query:

Select site from systables where tabid=91;

The restrictions is the Informix IDS database charsets for source database and syscdc1 should be the same.

0 Comments

Oracle GoldenGate for Informix - Frequently Asked Questions

12/8/2014

0 Comments

This blog provides answers to some frequently asked questions about Oracle GoldenGate for Informix.

0 Comments

How to Create Bi-Directional Replication for IBM Informix IDS Database with Oracle GoldenGate?

12/4/2014

0 Comments

Let's talk about how to setup a bi-directional replication for IBM Informix IDS databases with Oracle GoldenGate 12.1.2.1.0.

0 Comments

Oracle GoldenGate for Informix - Setting Up One-Way Replication

10/27/2014

0 Comments

This tutorial provides a step-by-step example for setting up an one-way replication between Informix IDS databases.

0 Comments

Oracle GoldenGate for Informix - Installing Oracle GoldenGate

10/9/2014

0 Comments

Installing Oracle GoldenGate is very straight forward. You just need to unzip the downloaded file and run the create subdirs command in GGSCI to create the directory structure for the Oracle GoldenGate installation.To start the Oracle GoldenGate manager, you can configure the port and parameters of the manager by setting up the parameter file. This tutorial provides an example.

Step 1: Unzip the Oracle GoldenGate software and start the GGSCI
>./ggsci

Why I get the "The program can't start because MSVCP100.dll is missing from your computer error?
Answer: In searching for an answer to this, I was able to identify that this DLL is a part of the Microsoft Visual C++ 2010 Redistribution Package (x64) (download here). After installing the package, the GGSCI command will run successfully.

Step 2: Create the directory structure.
GGSCI> create subdirs
Creating subdirectories under current directory /home/informix/ogg
Parameter files                /home/informix/ogg/dirprm: already exists
Report files                   /home/informix/ogg/dirrpt: created
Checkpoint files               /home/informix/ogg/dirchk: created
Process status files           /home/informix/ogg/dirpcs: created
SQL script files               /home/informix/ogg/dirsql: created
Database definitions files     /home/informix/ogg/dirdef: created
Extract data files             /home/informix/ogg/dirdat: created
Temporary files                /home/informix/ogg/dirtmp: created
Credential store files         /home/informix/ogg/dircrd: created
Masterkey wallet files         /home/informix/ogg/dirwlt: created
Dump files                     /home/informix/ogg/dirdmp: created

Step 3: Connect to Source Database
GGSCI (ogg-1.pssa.us.oracle.com) 1> dblogin sourcedb syscdcv1
2013-10-25 11:42:23  INFO OGG-03036  Database character set identified as ISO-8859-1. Locale: en_US.
2013-10-25 11:42:23  INFO OGG-03037  Session character set identified as UTF-8.
Successfully logged into database.

Step 4: Configure and Start the Oracle GoldenGate Manager
GGSCI> edit param mgr
PORT 15000
DYNAMICPORTLIST 15010-15020
PURGEOLDEXTRACTS ./dirdat/*, USECHECKPOINTS
AUTORESTART ER *

GGSCI> start mgr
Manager started.

GGSCI> info mgr
Manager is running (IP port brown.oracle.jp.15000, Process ID 18105).

Now, the Oracle GoldenGate is ready for use.

0 Comments

Oracle GoldenGate for Informix - Installing and Configuring ODBC

10/9/2014

0 Comments

Oracle GoldenGate for Informix uses ODBC to talk to the source and target databases. Therefore, you need to setup the ODBC connections. This tutorial explains the steps needed for this setup.

0 Comments

Oracle GoldenGate for Informix: Support and Feature Overview

10/8/2014

1 Comment

Oracle GoldenGate for Informix 12.1.2.1.0 was released on OTN 9/17/2014. In this version, the support is available on the following platforms:

Informix IDS (Dynamic Server) 11.5, 11.7 and 12.1
Operation System (64bit): Oracle/RHE LINUX x86 5 and 6 , Windows 2008R2 and 2012 , Solaris 10 and 11, AIX 6.1 nd 7.1

This is a new product added to the Oracle GoldenGate's heterogeneous database support. Oracle GoldenGate for Informix provides real-time data capture and delivery for Informix IDS databases. The new offering enables IBM Informix IDS users to replicate high volumes of data in real-time within the heterogeneous enterprise environment.

1 Comment

Oracle GoldenGate for Informix - Six Tutorials to Help You Get Started

Oracle GoldenGate Globalization

Oracle GoldenGate for Informix - Dealing with International Characters

Oracle GoldenGate for Informix - Frequently Asked Questions

How to Create Bi-Directional Replication for IBM Informix IDS Database with Oracle GoldenGate?

Oracle GoldenGate for Informix - Setting Up One-Way Replication

Oracle GoldenGate for Informix - Installing Oracle GoldenGate

Oracle GoldenGate for Informix - Installing and Configuring ODBC

Oracle GoldenGate for Informix: Support and Feature Overview

Oracle GoldenGate

Categories

Archives