Introduction

IBM Informix Dynamic Server (IDS) uses InfoSphere Change Data Capture (InfoSphere CDC) to provide support for heterogeneous data replication. Heterogeneous replication means that you can replicate data between IDS servers and other types of database servers—even database servers from other vendors. This article shows you how to configure InfoSphere CDC for IDS to enable heterogeneous replication.

InfoSphere CDC Version 6.3 for Informix captures and delivers changed data across diverse data stores in real time. Figure 1 shows some of the applications this functionality can have in data warehousing, real time analytics, business intelligence and reporting, and eBusiness. InfoSphere CDC also provides functions for data translation, filtering, replication, and high availability. (InfoSphere Change Data Capture was previously named DataMirror Transformation Server.)

Back to top

Overview of IDS Change Data Capture

Informix Dynamic Server provides a Change Data Capture API that external clients can use to capture high level online transactional data from the logical logs of an IDS server. The API provides functions to create and end a transaction capture session and to initiate and end capture on a given table for a given capture session. It also provides functions for other administrative tasks. The syscdc system database in IDS contains the change data capture functions and system tables.

The IDS Change Data Capture API is a public API that is available to any user. The IDS Change Data Capture API Programmer’s Guide (see Resources for a link) contains a Change Data Capture sample program (cdcapi.ec). Currently, the IDS CDC API supports capturing transactional data from current logs but not from archived logical logs.

Back to top

InfoSphere Change Data Capture

Figure 2 shows the components of InfoSphere Change Data Capture (InfoSphere CDC).

The key components of InfoSphere CDC are:

• Datastore Replication Engine is an InfoSphere CDC process that sends or receives changed data. Replication engine processes on source and target servers are created when you install InfoSphere CDC on source and target servers respectively.
• Access Server is a client workstation where a set of background processes direct requests received from the InfoSphere CDC Management Console on other client workstations to the appropriate datastores. These processes also return information back to the applications. You can create datastores and other InfoSphere CDC users that you want to assign the Access Manager perspective of the Management Console. The datastores and users you create are associated with the Access Server.
• InfoSphere CDC Management Console is an interactive application that you can use to configure and monitor replication. It allows you to manage replication on various servers, specify replication parameters, and initiate refresh and mirroring operations from a client workstation.
• Datastores represent the InfoSphere CDC installation and the database made available for replication. A datastore is an InfoSphere CDC process on a source or target server. The process accepts requests from an instance of Access Server and communicates with the datastore replication engine to initiate and manage replication activity. These processes are defined in the Access Manager perspective of the Management Console. Datastores were formerly referred to as replication agents.

Back to top

Configure IDS and InfoSphere CDC for replication

To set up replication with IDS and InfoSphere CDC, you need to have installed the following:

• Informix Dynamic Server (IDS) Version 11.50.xC3 or later
• Source database
• Target database
• InfoSphere CDC for IDS
• InfoSphere CDC for the database server you want to replicate with
• InfoSphere CDC Access Server
• InfoSphere CDC Management Console

InfoSphere CDC relies on the IDS CDC API to capture transaction data. So before you set up replication with InfoSphere CDC, you must do the following:

1. Run the syscdcv1.sql script in $INFORMIXDIR/etc directory as informix user:

   $ dbaccess - $INFORMIXDIR/etc/syscdcv1.sql

   
   
2. Turn on logging for all databases from which you intend to capture data changes. For example, create an IDS database with logging to enable IDS CDC.

   $ create database src_db_ids with log;

   
   

Set up InfoSphere for heterogeneous replication

This section gives an overview of the steps to set up a replication subscription between your source database and a target database. The following sections then provide detailed explanations and screen captures for the steps.

The example used in this article illustrates the steps for setting up IDS as the source database server and IBM DB2® Universal Database (DB2 UDB) as the target database server in a Windows® environment. The setup between IDS and any other type of database would follow the same basic procedure.

1. Create a new instance of InfoSphere CDC for the source database server (IDS).
2. Create a new instance of InfoSphere CDC for the target database server (DB2 UDB).
3. Create datastores and assign users to datastores:
   1. Log in to the Management Console and connect to the Access Server.
   2. Create datastores for source and target databases.
   3. Assign the datastores to users.
4. Set up a subscription for replication:
   1. Create tables in source and target datastores for CDC replication.
   2. Create a new subscription.
   3. Map tables for replication and select the replication method.
   4. Start replication on subscriptions.

Create and start a new instance of InfoSphere CDC for the source database server

This section describes the steps required to create and start a new InfoSphere CDC instance for IDS. For the example in this article, IDS is the source database server.

To add a CDC instance, you use the IBM InfoSphere Change Data Capture Configuration Tool. Use the following selection sequence to bring up the tool for IDS on Windows:

Start -> All Programs -> DataMirror -> Transformation Server for Informix -> Configure IBM InfoSphere Change Data Capture

The selection sequence for bringing up the tool for other types of database servers running on Windows would be similar. Figure 3 shows a screen shot of the tool. In the figure, the new CDC instance for IDS has already been added and started.

After you bring up the tool, click Add to add a new instance. This takes you to the IBM InfoSphere Change Data Capture New Instance dialog shown in Figure 4.

In the Instance area of the dialog, the Server Port field identifies the number of the port that InfoSphere CDC uses for communication with client workstations running Management Console and other servers. This is the same port number you use when specifying access parameters for your datastore in the Access Manager perspective of the Management Console.

In the Database area of the dialog, enter information for the database that contains the tables you want to replicate. Enter IDS database details including the IDS server name, host name, database name, user information, and IDS server port from SQLHOSTS /SETNET32 (Windows). Later in these instructions, you add a datastore and provide users access for this same database .

Once you have finished adding and starting the new instance, it will appear in the tool as shown in Figure 3.

Create and start a new instance of InfoSphere CDC for the target database server

Use the steps from the previous section as a guide to creating and starting a new instance of InfoSphere CDC for the target database server. For the example in this article, the target database server is DB2 UDB .

Use the following selection sequence to bring up the IBM InfoSphere Change Data Capture Configuration Tool for DB2 UDB on Windows:

Start -> All Programs -> DataMirror -> Transformation Server for UDB -> Configure IBM InfoSphere Change Data Capture

Create datastores and assign datastores to users

This section describes the steps required to create datastores for your source and target databases and assign them to users.

Log in to the Management Console and connect to Access Server

The first step is to log in to the Management Console. By logging into the Management Console, you are also connecting to Access Server. Figure 5 shows the Management Console login dialog.

When logging in, use the admin User Name, Password, and Port Number provided when InfoSphere Access Server was installed. In the Server Name field, enter the hostname of the workstation running Access Server.

Create datastores for source and target databases

This section describes how to create datastores for your source and target databases. Datastores are required to access the database tables.

For the example in this article, the source database is on an IDS server and the target database is on a DB2 UDB server. The steps describe adding a datastore for the source database. Use the same steps as a guide for adding a datastore for your target database.

After you have logged in to the Management Console, go to the Access Manager perspective. InfoSphere CDC Access Manager is used for datastore management, user accounts management, and connection management. With Version 6.3 of InfoSphere, the Access Manager is integrated with the InfoSphere Management Console.

Figure 6 shows the Access Manager perspective of the Management Console. In the figure, datastores have already been added for the source and target databases, and a user has been added to the source datastore.

Add a new datastore by clicking the Add new datastore icon as shown in Figure 6. This takes you to the New Datastore dialog shown in Figure 7.

In the Identification section of the New Datastore dialog, enter a unique Name and a Description for the datastore. For the Server field, enter the hostname of the database server. For the Port field, enter the port number that you defined for the instance of InfoSphere CDC for the source database server (see Figure 4). Click Connection Parameters... to bring up the Connection Parameters dialog. Enter the information needed to log into the source database server.

After you finish adding a datastore for your source database, use the above steps as a guide and add another datastore for your target database.

Assign datastores to users

In the Access Manager perspective of the Management Console, you can either assign a datastore to a user or assign a user to a datastore. Users need this relationship so that they can connect to a datastore.

Add a new user by clicking the Add new user icon in the User Management console as shown in Figure 6.

To assign a datastore to a user, right click on the user entry in the User Management console. This brings up the Select Datastore dialog as shown in Figure 8.

From the Select Datastore dialog, select the datastore you want to assign to the user.

Assign both the datastores for your source and target databases to users.

Set up a subscription for replication

A subscription is a connection that is required to replicate data between a source datastore and a target datastore. The subscription defines the details of the data being replicated and how the source data is applied to the target.

Add a new subscription

To set up a new subscription, go to the Configuration perspective of of the InfoSphere CDC Management Console and click on the New subscription icon as shown in Figure 9.

In the New Subscription dialog, enter a name and description for the subscription and select the source and target datastores. The source and target shown in Figure 9 correspond to the datastores you created in the previous section. The source is the datastore for the IDS database and the target is the datastore for the DB2 UDB database.

Map source and target tables

This example describes a simple table mapping for a source table to a similar target table using the Map Tables wizard. The Monitor Console also provides options to map to a custom table, filter rows and columns, and define advanced table mappings.

To start the Map Tables wizard, right click on a subscription in the Configuration perspective of the Management Console and select Map Tables.

Figure 10 shows the first screen of the Map Tables wizard.

For this example, the source and target tables have similar structures, so select One-to-one Mappings.

Click Next and the wizard takes you to the Select Source Tables screen as shown in Figure 11.

Expand the database user or schema in the Source Tables list to show the tables in the source database. Optionally, you can use Filter Columns... to filter the source columns. Make the selection to replicate the entire table.

Click Next and the wizard takes you to the Select Existing or Create New Target Tables screen as shown in Figure 12.

Make the selection to map to an existing target table and click Next. This takes you to the Map Source Tables to Existing Target Tables screen. From the Target Tables list, expand the database user or schema you want to use and select the target table from the list. For this example, choose the T_TAB1 table in the SC_KHOT database.

Click Next and the wizard takes you to the Set Replication Method screen as shown in Figure 13.

The two replication methods you can choose from are:

• Mirror (Change Data Capture) immediately replicates changes made to the source table to the target table.
• Refresh (Snapshot) replicates a snapshot of the source table to the target table.

For this example, select Mirror (Change Data Capture) and click Next. Verify the table mappings summary looks correct and click Finish.

Start replication on subscriptions

To start replication on a subscription, go to the Monitoring perspective of the InfoSphere CDC Management Console as shown in Figure 14.

From the Subscriptions view, right click on a subscription. Choose one of the three ways to start replication: Start Refresh, Start Mirror (Continuous), or Start Mirror (Net Change).

After replication is enabled, you can verify the target table values. If you select Mirroring Continuous replication, any data inserted into the source database is replicated continuously to the target database.

The example below shows data inserted into the IDS database being replicated to the DB2 UDB database.

C:\informix>dbaccess src_db_ids - -
>insert into s_tab1 values (10, 100.1234, “char10”);

db2 => connect to tgt_db
db2 =>  select * from t_tab1
COL1		COL2		   COL3
----------- ------------ ----------
	10    100.1234 	 char10

Back to top

Conclusion

This article shows you the steps for configuring Informix Dynamic Server with InfoSphere Change Data Capture to enable heterogeneous replication. It uses a sample scenario where the source database for the replication is an Informix database and the target database is a DB2 UDB database.

Back to top

Acknowledgments

The authors would like to thank Krishna P. Doddi, Vinayak Shenoi and Nilesh Ozarkar for reviewing this article.

Resources

Learn

• Get help in the IBM Informix Dynamic Server v11.50 Information Center.
  
  
• The Change Data Capture API Programmer's Guide describes how to program applications to process changed data from IDS databases using the Informix Change Data Capture API.
  
  
• developerWorks Information Management zone: Learn more about Information Management. Find technical documentation, how-to articles, education, downloads, product information, and more.
  
  
• Stay current with developerWorks technical events and webcasts.
  
  

Get products and technologies

• Build your next development project with IBM trial software, available for download directly from developerWorks.
  
  

Discuss

• Participate in the discussion forum.
  
  
• Check out developerWorks blogs and get involved in the developerWorks community.
  
  

About the authors

Priya Govindarajan is a software engineer working in IDS security team. She is involved in implementing IDS security features. Priya holds a master's degree in Computer Science from University of California, Irvine.

Satish Khot is a software engineer working on the Informix Dynamic Server (IDS) integration team. He is involved in integration of IDS features and products. Satish also works in certification and integration of partner products and tools that support IDS. Satish holds a Masters degree in engineering from Karnataka University, India.

Comments

Back to top

Trademarks  |  My developerWorks terms and conditions

Table of contents

• Introduction
• Overview of IDS Change Data Capture
• InfoSphere Change Data Capture
• Configure IDS and InfoSphere CDC for replication
• Conclusion
• Acknowledgments
• Resources
• About the authors
• Comments

Next steps from IBM

• Try: Informix Dynamic Server Enterprise Edition
• Demo: Use DB2 First Steps and the DB2 Control Center
• Webcast: A smarter way to successful SOA - Information as a trusted strategic asset
• Briefing: Information on Demand
• Buy: Informix Dynamic Server Enterprise Edition

My developerWorks community

Dig deeper into Information Management on developerWorks

DB2 pureScale

See how true transparent scaling architecture can be achieved on distributed platforms

Special offers