db2 SQL replication

neosunca

我写了一些关于实现db2 Sql replication的心得。是英文的（主要是要交给老外），我不想重新写了，所以做为附件发上。

DB2 Replication

Regarding the database replication, I setup a DB2 SQL Replication among 3 machines with two Linux operating systems and one Windows 2003 server operating system.

In order to realize the two-way replication among these 3 machines (A<->;B<->;C), I use the Update-Anywhere replication which SQL replication supported, as the figure 1 display:


Figure1

Here is the detail information to realize the Update-Anywhere replication:

The default database instance is DB2INST1 in Linux platform and DB2 in Windows platform. Here I use the Sample database which DB2 provided (run db2sampl at each server to create the database). Assume the three DB2 servers are A, B, C respectively, and the database Sample1 existing in server A, database Sample existing in server B, database Sample2 existing in server C. I take the server B as the Master Server and server A and Server C as replicas. As Figure2 displays:


Prerequisites for replication:
1.        Update the database configuration parameters LOGRETAIN from NO to RECOVERY at each DB2 server and backup the database, for example (in server B):

CONNECT TO SAMPLE
UPDATE DB CFG USING LOGRETAIN RECOVERY
FORCE APPLICATION ALL
STOP DB MANAGER
START DB MANAGER
BACKUP DATABASE SAMPLE USER xxxxx PASSWORD xxxxxx TO /home/db2inst1
CONNECT RESET
TERMINATE

2. Connect the other database in Master server (here is server B). This step is only done on the Master server.

CATALOG TCPIP NODE node-name1 REMOTE xxx.xxx.xxx.xxx SERVER 50000 REMOTE_INSTANCE db2inst1 (xxx.xxx.xxx.xxx is the ip address of Server A)
CATALOG DATABASE sample AS sample1 AT NODE node-name1

CATALOG TCPIP NODE node-name2 REMOTE xxx.xxx.xxx.xxx SERVER 50000 REMOTE_INSTANCE db2 (xxx.xxx.xxx.xxx is the ip address of server B)
CATALOG DATABASE sample AS sample2 AT NODE node-name2

After finishing these steps, you start the Replication Center tool which DB2 provides to define the Capture Control Server and Apply Control Server. For update-anywhere replication, you need to only create the capture control server on Master server (on server B). At replicas, you have to create both capture control server and apply control server (on server A and server C).

Next, you need to register the source tables or views on Master server (server B) through Replication Center. Here I use the EMPLOYEE table existing in SAMPLE database as the test table. When you register the source table, select “Capture changes from all columns” if you want to capture changes from all columns or “Capture changes from registered columns” if you want to capture changes the register columns only.
Do not register any tables on replicas (server A and server C) at this time. After the source table is registered, next is to create the subscription sets from Master (server B) to replicas using Replication center. First you will define the apply control server, capture control server, target server and activate this subscription sets. Then you will setup the mapping from source to target. Now, you add the source table, target tables and Replica target type.  Next you will define the replicate schedule. Here you can set the time-based replication by minutes, hours, days and weeks or let the replication runs continuously. In order to check the results promptly, I select continuously.

After you define subscription sets from Master to replicas, the system will save the information into apply control tables, and you will find that the target table is already defined in replicas.

Here some options need to pay attention: in the update-anywhere replication, you will define the re-capture in master and no re-capture in replicas



Uncheck the option “Capture changes from replica target tables” when you define the registered tables to disable re-capture.

After defining the tables and creating the subscription sets, you can start the capture process and start the apply process. You only need start these processes on master.

Here I started these processes using replication commands instead of replication center because I found replication center did not work well.

In order to start these processes using replication commands, a password file which is used to connect all databases has to be created.

ASNPWD INIT (create the password file with default name “asnpwd.aut”)
ASNPWD ADD ALIAS SAMPLE ID xxxxx PASSWORD xxxx USING asnpwd.aut
ASNPWD ADD ALIAS SAMPLE1 ID xxxxx PASSWORD xxxx USING asnpwd.aut
ASNPWD ADD ALIAS SAMPLE2 ID xxxxx PASSWORD xxxx USING asnpwd.aut
Here id and password are the user id and password being used to connect to the databases.

Then execute the following command to start the capture and apply process (I run these command on Linux platform):

ASNCAP CAPTURE_SERVER=SAMPLE CAPTURE_PATH=/home/db2inst1 PWDFILE=asnpwd.aut &
ASNCAP CAPTURE_SERVER=SAMPLE1 CAPTURE_PATH=/home/db2inst1 PWDFILE=asnpwd.aut &
ASNCAP CAPTURE_SERVER=SAMPLE2 CAPTURE_PATH=/home/db2inst1 PWDFILE=asnpwd.aut &

ASNAPPLY APPLY_QUAL=xxx CONTROL_SERVER=SAMPLE1 APPLY_PATH=/home/db2inst1 PWDFILE=asnpwd.aut &
ASNAPPLY APPLY_QUAL=xxx CONTROL_SERVER=SAMPLE2 APPLY_PATH=/home/db2inst1 PWDFILE=asnpwd.aut &

Password file can be stored in capture_path and apply_path directory, if not, specify the absolute path for password file.

After starting these processes, the replication will begin to work. When you insert, update, delete the records from the registered table in one database server, the other server will replicate these changes.

In update-anywhere replication, the conflicts can happen when you do the replication. This situation occurs when the target keys of registered table are updated. Following figure show what happen:


In order to avoid this situation, you can use “capturing before-image together with after-image” to realize the correct replication. Also, you can set the conflict detect level to avoid the conflict. I only use the “standard detect” and before-image to avoid this situation occur.

Beside the update-anywhere replication, I also tried the peer-to-peer replication and realized it between two servers. However, it is very complicated to add a new server into the peer-to peer replication. Referring to DB2 document, it is not recommended to use peer-to-peer replication in SQL replication. Q replication is more suitable for peer-to-peer replication.

In order to realize the replication, the user must own the privileges like SYSADM and DBADM. When you run the capture and apply program, you have to provide the user id and password.

has-been

Hi neosunca,

Thanks for your sharing.

The replication of DB2 is a new concept to me, it will be better if you would list all your action step by step for each machine.

neosunca

我只在一个机器上做了所有的配置，你可以用catalog命令去创建节点和数据库，然后在一台机器上做所有的操作就可以了

zzjijun

顶！

benh

sql replication的主要步骤：
1 create capture control table
2 register source table
3 create apply control table
4 create subsrciption
5 start capture program
6 start apply program
在所有这些之前，应该先catalog database and logretain them.
整个replication实际上就是由capture和apply程序完成的。capture负责捕获source table的改变，apply负责将改变应用到目标表。subscription就是将source table/server, target tables/server，对应起来的一个映射。
1-4是配置，5，6就开始capture/apply的正常工作了。
replication centre是不怎么好用。

myuhan

V7下 经常做，V8没做过，上次随便看了一下V8的环境，好象和V7差别还很大。