Replication technology using Informix Dynamic server

Ibm®informix®dynamic Server (IDS) has long been a database of powerful replication technologies. Enterprise Replication (Enterprise replication,er) and high Availability data replication (high-availability) provide reliable services to customers over the years. In newer IDS version 11.1 and current 11.5, new technologies have been added: remote Standalone standby servers and shared disk (Shared-disk) standby servers. This article will use these techniques to briefly understand the characteristics and details of various technologies.

Enterprise Replication (ER)

What's ER?

A database can store important business data for you, and business people realize that it's a smart strategy to store data in at least two different locations. Wouldn't it be nice if you could tell the database to automatically replicate the data you need to another server? Yes, IDS already have this capability since Version 7. This is achieved through Enterprise Replication (ER) technology, which is flexible and powerful.

To use ER technology, first identify the server that needs to replicate the data. This creates a network topology that is used to transmit data-including root, leaf, non-root, and NON-LEAF nodes. Each ER node may be a single server or a cluster of servers, as described later in this article. All of these interconnected nodes are collectively referred to as an ER field (domain). The domain does not define which data is transferred, but only the path or route that is passed when the data is transmitted.

Next, determine which data you want to replicate. This is defined by a SQL query that involves servers, databases, tables, and columns. This query acts as a filter, and its output determines which data to replicate, and is a very flexible tool. If you want to copy the entire table, the query should be SELECT * from .... If you only need to copy the first three columns of the table, you should use the query SELECT column1, Column2, column3 from .... Or, if you only want to copy some of the rows of the table, you only need to use the WHERE clause in the query. ER can copy data using very coarse or very fine filters. To achieve better replication, ER requires the table to define a primary key.

After the query has been written, determine which nodes need to participate in the data replication. If you ask nodes A, B, and C to put the same data in the Employee table, the A, B, and C nodes are the participating nodes. ER can be configured so that if data is modified on any server, the other participating nodes are also updated. This situation is called the update anywhere scene. What if you just want to start with a and update B and C, but not back to a? ER also provides flexibility for these primary-target scenarios. For data modifications that occur in multiple participating nodes, the rows that are modified in server B may conflict with the modifications in server C. ER allows you to define rules to handle this conflict automatically. These rules include comparing timestamps, returning a stored procedure, and ignoring conflicts.

Now that you know about querying, participating in nodes, and scenario knowledge, you will use this information to implement replication (replicate). Consider a copy as a faucet that can control the flow of query data. You can turn the faucet on or off, and you can use any number of taps. You can divide replication into collections that make it easier for users to control multiple replications. Similarly, you can use templates to quickly create replication for multiple tables. All of this work can be done through the server utility CDR.

After you create and start a replication, how do you actually move the data? First, replication is done asynchronously. This means that data is submitted from one node for a period of time before it is rendered to another node. Of course, only the submitted data will be replicated. ER reads the logical log first, verifies that the log records need to be replicated, and finally sends the information to the appropriate participating node.

A simple example

Let's create a simple ER example. We will use the Update-anywhere method to replicate the Employees table of the "DB1" database between server 1 and server 2 (the following steps assume that the Informix account is used).

Step 1: Prepare the Sqlhosts file

The files for each server should contain one entry for two servers and two sets of definitions. Each group represents an ER node:grp1　　　　group　　 　　- 　　- 　　i=1
server1　　　　onsoctcp　　host　　port　　g=grp1
grp2　　　　group　　　　- 　　- 　　i=2
server2　　　　onsoctcp　　host　　port　　g=grp2

Step 2: Prepare ER disk space

Perform the following steps for each server:

Edit ONCONFIG to include a smart blob space:CDR_QDATA_SBSPACES sbs1

Start the server.

Prepare a block for the smart blob space and add the space to the server. For example, in UNIX:touch /ids/chunks/sbs1_chunk1
chmod 660 /ids/chunks/sbs1_chunk1
onspaces -c -S sbs1 -p /ids/chunks/sbs1_chunk1 -s 100000 -o 0

Step 3: Add the node to a new ER domain

On the Server1, execute the following command:cdr define server --init grp1

。 On the Server2, execute the following command:cdr define server --sync=grp1 --init grp2

These commands create an ER domain of two root nodes.

Step 4: Prepare the table to perform the replication

In Server1 and Server2, run the following SQL statement:CREATE DATABSE db1 WITH LOG;
CREATE TABLE employees(id int primary key, ...);

Step 5: Perform replication

On the Server1, execute:

cdr define replicate --conflict=ignore r_employees \
"db1@grp1:informix.employees"　"select * from employees" \
"db1@grp2:informix.employees"　"select * from employees"

Step 6: Start replication

On any server, run the following command:cdr start replicate r_employees

Now you are performing data replication! Inserts, updates, or deletes are mapped to another server, either for Server1 or for rows in Server2. This is just the basis of ER application.