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.
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
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
(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
(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
(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
(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
(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
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
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
(See a larger version of Figure 9.)
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
(See a larger version of Figure 10.)
Figure 11. OAT view of databases on the g_three server (no atest database present)
(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
(See a larger version of Figure 12.)
From the first page of the Define New Template wizard (Figure 13), enter the name
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
(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
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
(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
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
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
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
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
(See a larger version of Figure 20.)
Figure 21. Table named tab in atest database on g_three server synchronized with g_two server
(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.)
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
(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
(See a larger version of Figure 23.)
On the first screen of the Check or Sync Replicate Set wizard (Figure 24),
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
(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
(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
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
(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
(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
(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.
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
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
(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
(See a larger version of Figure 31.)
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
(See a larger version of Figure 32.)
Figures 33 shows an example of the Capture tab.
Figure 33. Capture tab
(See a larger version of Figure 33.)
Figures 34 shows an example of the Disk Usage tab.
Figure 34. Disk Usage tab
(See a larger version of Figure 34.)
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.
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.
- OpenAdmin Tool Web site: Find out all about the latest OAT for IDS versions, including new feature details, screenshots, and tutorials.
- Manage your Informix
database with IDS OpenAdmin Tool developerWorks series: Find more information regarding OAT:
- Part 1: Configuring and using the OpenAdmin Tool with IDS (developerWorks, July 2008): Learn about some of the most useful features of OAT.
- Part 2: Migrate from Informix Server Administrator to the IDS OpenAdmin Tool (developerWorks, July 2008): Migrate from the Informix Server Administrator (ISA) to the OAT. This article also describes how the basic capabilities of ISA are made available on OAT, along with enhanced functionalities and greater ease-of-use.
- Part 3: Performance analysis of Informix Dynamic Server made easy through the OpenAdmin Tool (developerWorks, July 2008): Easily monitor performance through statistical data and charts that give you a quick way to comprehend the information.
- Build plug-ins for IBM's OpenAdmin Tool for Informix Dynamic Server (developerWorks, Aug 2008).
- Replication technologies in Informix Dynamic Server (developerWorks, July 2008).
- developerWorks Informix page: Find articles, tutorials, and other resources to expand your Informix skills.
- Informix Dynamic Server, V11.50 Information Center: Learn more about IDS.
- developerWorks Information Management zone: Find technical documentation, how-to articles, education, downloads, product information, and more.
- Stay current with developerWorks technical events and webcasts.
Get products and technologies
- Informix Dynamic Server Express Edition: Download a trial version of Informix Dynamic Server Express Edition to get started with IDS.
- Informix Dynamic Server Enterprise and Developer Edition: Download a free trial version of Informix Dynamic Server Enterprise or Developer Edition.
- Download IBM product evaluation versions.
- Participate in the discussion forum.
- Check out developerWorks blogs and get involved in the developerWorks community.