Informix Enterprise Replication made easy with OpenAdmin Tool

IBM® Informix® Dynamic Server (IDS) is a powerful database with many useful built-in features. One such feature is the ability to send data committed on one server to another server automatically. Now you can use a Web browser to configure and monitor this Enterprise Replication (ER) feature using the OpenAdmin Tool (OAT).

Nicholas Geib, Software Engineer, IBM

Nicholas Geib photoNicholas Geib is a software engineer who works on IBM's Informix Dynamic Server. He is a member of the team that supports IDS's availability technologies, which includes Enterprise Replication, High-Availability clusters, and backups.



Pierre Rajha (prajha@us.ibm.com), Software Engineer, IBM

Pierre Rajha photoPierre Rajha is an information management software engineer at IBM. He is a member of the development team for the OpenAdmin Tool for Informix Dynamic Server.



Sumanth Rajagopal (sumanth@us.ibm.com), Software Engineer, IBM

Sumanth Rajagopal phooSumanth Rajagopal has been a part of the Informix development organization for eight years. Most recently, he has worked on the cluster and replication components of the server and OpenAdmin Tool. Sumanth is based in the San Francisco bay area.



Erika Von Bargen (vonbarg@us.ibm.com), Software Engineer, IBM

Erika Von Bargen photoErika Von Bargen has been a part of the IBM Informix development organization for five and a half, the last two and a half of which have been spent on the OpenAdmin Tool team. She is based in the IBM Lenexa lab.



18 March 2010

Introduction

A database is an essential business tool. Its concept of a transaction enables goods to be purchased and money to be transferred with the speed and ease we have come to take for granted. For various reasons, businesses frequently desire to have the data stored in one server be accessible on a second server. For example, having a copy of the data allows a business to operate in the event of a problem with the first environment. Also, you can query and manipulate data copied off a production server without impacting production processes. Data can be consolidated from multiple remote locations into a central server and be disseminated from a single location to a set of remote databases. For these reasons and more, being able to replicate data between databases can be very useful.

With Informix Dynamic Server (IDS), you can use the Enterprise Replication (ER) feature to replicate data to other IDS servers. This feature is built into IDS and requires no additional hardware or software. There are also various other tools you can use to replicate data between IDS and non-IDS databases, but this article focuses on IDS to IDS replication.

In order to use Enterprise Replication, you first need to properly configure the servers between which data will be transferred and the tables where transactions will be identified for replicating. Previously, you would have used the Continuous Data Replication (CDR) utility to perform this configuration and then monitor and administer Enterprise Replication tasks. CDR is a command-line utility and an earlier name used for the Enterprise Replication feature.

ER plug-in for OAT

Make sure the ER plug-in is installed in OAT. If Enterprise Replication is available as a selection on the left navigation pane, the plug-in is installed. If you need to install the plug-in, from the left navigation pane select Admin > Plug-in Manager.

Now you can use the OpenAdmin Tool (OAT) to configure, monitor, and administer ER through a Web browser. The OpenAdmin Tool is an open-source Web application that allows you to monitor and administer IDS in general. The ER plug-in expands this functionality to include ER. The plug-in's wizards give simple, graphical, step-by-step instructions for performing tasks. Its organization makes it easy to monitor replicated data and take further action. The graphical interface means there are fewer concepts to understand and no command syntax to remember. This all makes ER easier and faster to use.

The following sections will show you how to see this in action.

Creating an ER domain

An ER domain consists of IDS servers that can replicate data between themselves based on user-defined criteria (this criteria is explained in later sections). The participating IDS servers can be arranged in various topologies. (Refer to the Choosing a Replication Network Topology topic in the IDS Information Center for more details.) This article shows you how to setup a domain with three servers (g_one, g_two, and g_three) in an Update Anywhere configuration. The focus is on showing you how to create a domain that is functional with the default options or certain minimal configuration changes. The article also shows you how to use several advanced options.

A prerequisite for adding servers to an ER domain is that each standalone IDS server must be running with configured SQLHOSTS files and connected to a network. Listing 1 contains a SQLHOSTS file for each of the three servers being used for this sample scenario. Once the three servers are running with this SQLHOSTS file, they are ready to be part of an ER domain.

Listing 1. A sample SQLHOSTS file
g_one group - - i=100
sumanth_er onsoctcp hostA sumanth19 g=g_one
g_two group - - i=200
sumanth_er1 onsoctcp hostA sumanth20 g=g_two
g_three group - - i=300
sumanth_er2 onsoctcp hostA sumanth21 g=g_three

You can use OAT's four part wizard to add a server to an ER domain. Following are instructions for adding the first server.

Under the Enterprise Replication menu group in the OAT left navigation pane, select Enterprise Replication > Domain to go to the Domain page. Because this is the first server in the domain, you see a confirmation screen asking if you would like to define this server as an ER server (Figure 1). Click Yes to continue defining this server as an ER server.

Figure 1. Domain creation confirmation screen
Would you like to define this server as an ER server? Select Yes or No

If the server you want to add is not already in the list on the first screen of the wizard, click Add a server to the list. This displays the dialog shown in Figure 2. Enter the requested information for the server name, OAT group, host, port, user, password, and protocol, and then click OK.

Figure 2. IDS server connectivity information
Dialog with prompts for server, OAT group, host, port, user, password, and protocol

(See a larger version of Figure 2.)

The new server now appears in the list. Select it as shown in Figure 3, and click Next.

Figure 3. Select the server
Select the new server from the list

(See a larger version of Figure 3.)

On the next screen of the wizard (Figure 4), all the default options are sufficient to create a domain. However, you may want to change certain options related to the topology and ATS/RIS (Aborted Transaction Spooling/Row Information Spooling) configuration. (Refer to the Failed Transaction (ATS and RIS) Files topic in the IDS Information Center for more details.) Click Next to proceed.

Figure 4. ER domain and node options
Select Create a new ER domain, and specify Root Node as the ER node type

(See a larger version of Figure 4.)

On the last screen of the wizard (Figure 5), the only required information is a smart blob space for the configuration parameter CDR_QDATA_SBSPACE. (Refer to the CDR_QDATA_SBSPACE Configuration Parameter topic in the IDS Information Center for more details.)

In the Create a new sbspace area of the screen (highlighted in the figure), enter a name for the space, the path to the file in the host, the offset (use 0 for cooked files, that is, a file managed by the operating system), and the size. Click Create to proceed.

Figure 5. CDR_QDATA_SBSPACE information entry
Enter name for the space, the path, offset, and size

(See a larger version of Figure 5.)

As shown in Figure 6, the sbspace named cdrsbsp1 is selected to be created. Click Next.

Figure 6. Selection of the QDATA sbspace
Complete defining the space

(See a larger version of Figure 6.)

On the wizard confirmation page (Figure 7), check that everything looks as it should, and click Finish to proceed.

Figure 7. Confirmation screen
Shows the choices that you have selected

After the actions you selected are performed, this server is part of the newly created ER domain and you see the results screen shown in Figure 8. Click Close to proceed.

Figure 8. Results screen
The new ER server was defined successfully

Now you can use the same basic steps to add the other two servers (g_two and g_three). The only difference is that on the second screen of the wizard (Figure 4), you would select Add the server as a new node in an existing ER domain and then choose the server to sync to. To get started, select Define New Server from the Actions drop-down menu.

After you define g_two and g_three as ER servers, the domain has three servers (Figure 9).

Figure 9. ER domain created with three servers
Routing topology of ER domain with three servers named g_one, g_two and g_three

(See a larger version of Figure 9.)

Templates

Templates are a mechanism you can use to easily establish replication and synchronize data with new servers in the ER domain. You can define templates for an entire database or individual tables within a database. When you add a new server with no data to the ER domain, you can instantiate the template on the new server. First, this creates the database and its tables with the appropriate column attributes and primary key constraints of the source database server, and then initiates the data transfer from a source server.

This section outlines the steps to define a template, realize (instantiate) it on a new server in the ER domain, and synchronize the data with a source server. The instructions describe how to define and instantiate a template with minimal options. Once you understand the basics of this process, you will be ready to explore the many options available to meet the needs of your own particular environment.

The scenario is as follows: a server named g_two has a database named atest. The atest database has a table named tab that is populated with data (Figure 10). A second server named g_three does not have the atest database (Figure 11). By defining a template and instantiating it on g_three, you can create the atest database on g_three and replicate the data from g_two to g_three.

Figure 10. OAT view of the data in the atest database on the g_two server
OAT showing sample data in tab table of atest database

(See a larger version of Figure 10.)

Figure 11. OAT view of databases on the g_three server (no atest database present)
OAT showing g_three server databases; at this point in the scenario, the atest database is not on this server

(See a larger version of Figure 11.)

In OAT, select Enterprise Replication > Replicates to go to the Replicates page. Templates is the default tab that is open when you go to the page.

To start defining a new template, select Define New Template from the Actions drop-down menu (Figure 12).

Figure 12. Start defining a new template
Selecting Define New Template action from Replicates page in OAT

(See a larger version of Figure 12.)

From the first page of the Define New Template wizard (Figure 13), enter the name atestTempl for the template. Then select the entire atest database from the Tables selection box on the left and click Add to include atest and all its tables in the Templates selection box on the right. This means that atest is the database that will be created and replicated by this template on other servers. Click Next to proceed to the second screen of the wizard.

Figure 13. First screen of Define New Template wizard
Template name entered as atestTempl; atest database has been added to template.

(See a larger version of Figure 13.)

The second screen of the wizard (Figure 14) lets you choose several other template options. For this example, you can simply use the default options. (Refer to the cdr define template topic in the IDS Information Center for more details on the specific options.) Click Finish to define the template.

Figure 14. Second screen of Define New Template wizard
Figure 14

Once the template has been created, you see the Define New Template wizard results screen (Figure 15). Click Close to return to the Templates tab of the OAT Replicates page.

Figure 15. Results screen of Define New Template wizard
Screen contains message: Template atestTempl created successfully

(See a larger version of Figure 15.)

Now you are ready to instantiate the new atestTempl template on the source (g_two) and target (g_three) servers. This creates the database on g_three and populates it with data from g_two.

From the Templates tab, select the atestTempl template and select Realize Template from the Actions drop-down menu (Figure 16).

Figure 16. Select the Realize Template action
Realize Template selected on Actions drop-down menu

On the first screen of the Realize Template wizard (Figure 17), you choose the servers on which to instantiate the template. Even though the g_two server is the source, it does not yet have the template instantiated on it, so select both the g_two and g_three servers. Click Next to proceed.

Figure 17. First screen of Realize Template wizard
Both g_two and g_three servers selected as servers on which to instantiate the template

On the second screen of the Realize Template wizard (Figure 18), you specify the parameters for the realize template action. Select the Synchronize the data with this source server option, and select g_two as the server from the drop-down list. Use the defaults for the other options on the screen. Click Finish to proceed.

Figure 18. Second screen of Realize Template wizard
Check box for Synchronize the data with this server is checked and g_two test is selected as the server

Once the template has been realized and the synchronization has started, you see the Realize Template wizard results screen (Figure 19). The time it takes for the synchronization to complete depends on the amount of data that needs to be replicated. Click Close.

Figure 19. Results screen of Realize Template
Results screen shows that template was instantiated on g_two and g_three servers. Status messages displayed for in process synchronization

The OAT views shown in Figures 20 and 21 show that the database and tables have been created on server g_three and populated using server g_two as the source.

Figure 20. Database named atest created on g_three server
atest database now appears in the list of databases on the g_three server

(See a larger version of Figure 20.)

Figure 21. Table named tab in atest database on g_three server synchronized with g_two server
tab table in atest database on g_three server; COL2 shows the street address 4100 Bohannon Drive

(See a larger version of Figure 21.)

Trivia: In Figure 21, what is the significance of the street address 4100 Bohannon Drive shown in COL 2 of the data? (See the Conclusion section of this article for the answer.)

Data consistency

The ER feature automatically replicates each committed transaction asynchronously to the appropriate servers and robustly handles many situations such as network link disconnection and IDS going offline. However, it is possible for out of sync data situations to occur. OAT allows you to easily start and monitor multiple check and repair jobs. You can use these jobs to correct errors, or to simply check that all your data is consistent. .

These jobs can be completed faster by verifying the data for multiple replicates simultaneously. This parallelism requires redundant processor resources. OAT allows you to select the desired parallelism and displays information about the task as a whole as well as details about the individual parts. OAT represents the progress of the task and the progress of each part with progress bars and other helpful user interface messages. This section describes how to use the Check or Sync Replicate Set wizard.

On the Replicate Sets tab in OAT (Figure 22), select a replicate set.

Figure 22. Select a replicate set
Select Replicates selected from menu on left of screen; template_A selected from list

(See a larger version of Figure 22.)

Select Check or Sync Replicate Set from the Actions drop-down menu (Figure 23) to start the Check or Sync Replicate Set wizard.

Figure 23. Select the Check or Sync Replicate Set action
Replicate page with Check or Sync Replicate Set selected on Actions pull-down menu

(See a larger version of Figure 23.)

On the first screen of the Check or Sync Replicate Set wizard (Figure 24), enter OAT_template_A as the Task Name and select Check and Repair as the task to perform. Click Next to proceed.

Figure 24. First screen of the Check or Sync Replicate Set wizard
Task name OAT_template_A entered; Check and Repair radio button selected

(See a larger version of Figure 24.)

On the second screen of the wizard (Figure 25), you select the number of processes to use. For best performance, you should specify the same number of processes as the number of replicates in the replicate set. For this sample scenario, select 4. Click Finish to proceed.

Figure 25. Second screen of the Check or Sync Replicate Set wizard
Shows number of processes and other server options

(See a larger version of Figure 25.)

The Check or Sync Replicate Set wizard confirmation page (Figure26) indicates that the check and repair task was successfully submitted. Click Close to proceed.

Figure 26. Check or Sync Replicate Set wizard confirmation page
Message: Check and repair task successfully submitted.

Navigate to the OAT Tasks Status tab. As shown in Figure 27, the tasks for this sample scenario are still running. To get detailed information about the task, double click on it to go to the tasks detail page.

Figure 27. Task Status tab
Table showing tasks and status

(See a larger version of Figure 27.)

On the task details page (Figure 28), OAT displays detailed information about the task, a progress bar representing the progress of the entire task (top progress bar), and progress bars representing the progress of each replicate in the template.

Figure 28. Task Details page
Shows progress of each task

(See a larger version of Figure 28.)

You can track the progress of the task until it is completed (Figure 29).

Figure 29. Task Details page with all tasks completed
Shows each task as being completed

(See a larger version of Figure 29.)

Monitoring an ER system

Just as important as the easy setup and administration of Enterprise Replication is the ease of monitoring and analyzing the system once it is setup. To that end, the ER plug-in for OAT provides three levels of monitoring for your ER system: monitoring your entire domain, monitoring individual servers or nodes, and monitoring replication activity. These three levels correspond to the three views on the OAT menu for ER.

Domain monitoring

Perhaps the most important part of monitoring ER is having an easy way to determine the health of your entire system from one place. Select Domain from the OAT menu to access the Domain Monitoring page, which is a one-stop, overall view of your system. This view gives you health and profile information about each node and its replication status.

The base of the Domain Monitoring page is a graphical representation of your ER topology. From the topology you can select any node and display a pop-up with that node's status. The node status shows you the most important data relating to the health of that node (Figure 30). The profile data displayed for each server is the same information you would be by issuing a cdr view profile command from the command line. The data on the domain page is automatically refreshed so that you are always viewing the up-to-date status and health of your ER system.

Figure 30. Domain monitoring showing status of the selected node
Shows topology with one of the nodes selected, and status displayed

(See a larger version of Figure 30.)

Most critically for ease of problem diagnosis, you can configure thresholds for each of the profile statistics. If one of these thresholds is reached, you then receive an alert through the domain monitoring page about potential problems of your system. By setting thresholds relating to data capture, the send and receive queues, apply statistics, the network status, and disk usage, you can create an early warning system to identify potential problems with your ER system.

For example, in Figure 31, the g_two and g_three servers are displayed with icon alerts that indicate they are disconnected.

Figure 31. Display of alerts in domain monitoring
Shows alerts for disconnected nodes

(See a larger version of Figure 31.)

Node monitoring

Besides monitoring on the domain level, you can also drill-down to a particular server and get a detailed look at the status of ER from the perspective of that node. This type of view is provided by the Node Details page in OAT.

The Node Details page provides a set of ten tabs that contain detailed information about each server in your domain:

  • The Summary tab gives high-level statistics for the node, which you can use to determine the current state of replication for this node and its partner nodes.
  • The Capture tab provides statistics on the data capture activity at the node.
  • The Send Queue tab allows you to monitor important statistics about the send queue for the selected node and its target nodes and replicates.
  • The Network tab allows you to monitor network activity between the selected node and its partner ER nodes.
  • The Disk Usage tab shows information and usage statistics about the three areas of space used by ER at the node.
  • The Apply tab provides information on replication apply activity for the selected node.
  • The ATS and RIS tabs can be used to identify and repair failed transactions.
  • The Errors tab provides a mechanism for reviewing errors related to the current node.
  • The Configuration tab allows you to view and edit the ER configuration parameters of the node.

Figures 32 shows an example of the Summary tab.

Figure 32. Summary tab
Summary tab of Nodes Detail page shows high-level statistics for node and partner nodes

(See a larger version of Figure 32.)

Figures 33 shows an example of the Capture tab.

Figure 33. Capture tab
Capture tab of Nodes Detail page shows statistics on the data capture activity at the node

(See a larger version of Figure 33.)

Figures 34 shows an example of the Disk Usage tab.

Figure 34. Disk Usage tab
Disk Usage tab shows information and usage statistics about the three areas of space used by ER at the node

(See a larger version of Figure 34.)

Replication monitoring

A final layer of monitoring is provided by the Replicates page. This page allows you to view the status of templates, replicate sets, and replicates. Therefore, you can monitor the status of all objects related to replication activity in your domain. Refer to the figures in the Templates section for examples that illustrate this point.

Flexibility of ER

Enterprise Replication in IDS is very flexible and can meet many business requirements. Here are some examples:

  • You can specify exactly what data gets replicated. Do you want tables 1 and 2 but not table 3? Do you only want the rows for states whose names start with "A"? ER can accommodate all such rules.
  • You can set what sources the replicated data comes from and to what targets it goes. For example, a business may want all purchase transactions at stores #208 and #209 to go to store #150 but store #150 never to send any of its data back.
  • ER works between different versions of IDS; for example, between a version 10 server and a version 11.50 server. This can be useful when you upgrade IDS and when you are using IDS on different operating systems.

If you have a business scenario with a specific replication requirement, chances are that ER already has the functionality to fulfil it.

Conclusion

The already rich functionality of ER is continuing to be enhanced. Recent enhancements are available through the OAT ER plug-in as well as in cdr. Some of the recent improvements include increasing the speed of check and repair tasks; for example by being able to specify that only recently modified data be examined. The OAT ER plug-in already contains all the useful cdr commands.

Enterprise Replication is a useful, already-in-the-box tool that allows IDS servers to exchange data. It comes in handy for quick data sharing as well as to power complex hierarchies of dozens of databases 24x7. The OAT ER plug-in makes ER easy and quick to use.

Answer to trivia question below Figure 21: 4100 Bohannon Drive was the address of the Informix company headquarters since its beginnings in the early 1980s until the IBM acquisition in 2001 and an IBM site until the end of 2007.

Resources

Learn

Get products and technologies

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=474397
ArticleTitle=Informix Enterprise Replication made easy with OpenAdmin Tool
publish-date=03182010