IBM DB2 Tutorial : Db2 SQL Replication Steps with example . How to replicate data in db2 step by step

One of the very helpful and important feature in db2 is Replication technique. Replication technique allows you to copy data from one location to another location making the second location data identical to the first location. Data can be copied either in the local or remote machine .Replication is useful1. To consolidate data from multiple sources in a distributed environment

2. to support basic load balancing when your server is running many more read queries (SELECT) than write queries (insert / update / delete) .
3. Makes a backup of data in the local / remote which helps for disaster recovery
4. to reduce delay and bring the data closer to the user.

DB2 Universal Database (UDB), supports two types of replication.
SQL Replication can be done using Control center and Scripting language called ansclp .SQL replication capability is included in the base product. From the Control Center you can access the Replication Center, a graphical interface for the setup of replication. There is also a scripting language for replication called ansclp which allows you to create scripts to automate replication setup.

This tutorial overs that how to setup replication using the the Replication Center . You can access the Replication Center using Control Center -> Tools -> Replication Center.

What happens when we do replication.
Two programs are involved. Capture Program and Apply Program .Capture program capture the data changes in the source table to the CD Table . Changes of data in the source tables are captured in a CD Tables . When we insert new rows in the source table , new rows are captured in the CD Tables with flag "I" . When we update data in the source table , the updated rows are captured in the CD Tables with the flag "U" . SImilarly , When we delete data in the source table , the deleted rows are captured in the CD Tables with the flag "D" . Apply Program , replicate the the data changes captured in the CD table to the target tables.

Major Steps for replication :

1. Create control tables for the Capture program

2. Enable the source database for replication (i.e. to enable logretain on for archival log)

3. Register source tables

4. Create control tables for the Apply program

5. Create a subscription set and member

6. Start the operation to capture and apply

7. Testing the Replication


1. Create control tables for the Capture program
To Create table space and control tables do the following step
1. Right click on the Create Control Servers -> Select Create Capture Control Tables -> Select Custom

2. Select Capture control server (Source database) to create Capture Control Tables
Give user name and password and select Run then OK.

Now Capture control tables are created

2. Enable the source database for replication
Right click on the Source database name (Capture control server ) to enable database for replication . that means to enable archive logging .

Press ok button to set the LOGRETAIN value for the database to RECOVERY and initiate offline backup for the db and take full backup of db


3. Register a replication source

a) Before registering source tables , specify the schema and table name and table space to be used for the CD (Capture Data) tables

Right click on the Capture control server and Source database name and select Manage Source Object Profile

You can specify here
i) table schema and table name to be used as the defaults for the CD tables
ii) table space & its properties for the CD tables and naming convention for table spaces
iii) schema and name for the CD table indexes & naming convention for the the index name.

b) To register source tables
1. Select Capture control servers - > Source database -> capture schemas -> schema name.
2. Right click on the schema name and select Register tables
3. Select Retrive All to retrive all source tables and select the table you want to register. Now CD (Capture Data) table name and table space details appeared automatically based on the settings in the previous steps (i.e. 3 a)

4. Then Ok which will run the query .

4. Create control tables for apply program

To create control tables for apply program , Right click on Apply Control Server -> Create Apply Control Tables -> custom

Select the target database where the data to be replicated . Then Ok.

5. Create Subscription Sets
A subscription set defines a relationship between the source database (ORI_DB in our example) and a target database (DUP_DB in our example). A subscription-set member defines a relationship between the source table (SALES) and one or more target tables (TGSALES).

Here you have to specify , set information , source to target mapping , schedule time to replicate data.

Steps to do :
To create subscription sets , Expand Apply control server -> Right click on the newly created Apply control server ( target data base) -> Subscritption sets

Now you have to fill / select all the details like Apply control server , Set Name , Apply Qualifier , Capture control server and target server (Target database)
Check on the activate the subscription set

Now do the source to target mapping , which maps the source colums to target columns to replicate the data .

Now Set the replication schedule (Time based / event based) , Then Ok.

Now let us Add Members

Right click on the newly created set , select the member information tab and add the member using retrive all option . Use change to do column mapping and etc... Please add the column for index for target table using target table table . Then Ok.

Now the target table is created in the target server where the data to be replicated .

6. Finally Let us start the operation to capture and apply

To start Capture , Expand the operations on SQL replication under Replication center

Right click on the capture control servers and select add and give the capture control server already created (target database) , userid and password which adds capture control servers for operations

Right click on the capture control servers which is added now , then select start capture , then select capture schema , then Ok.
Now give user name and password for the database server to access

Similarly you can stop , resume or suspend capture later.

To start Apply Program
Similarly Expand the apply control servers under operations , Right click on the apply control server -> Apply qualifiers , then refresh .

Now right click on the apply qualifiers already created and the select start Apply , give Host Name or IP Address , then Ok. Now give your target server details by clicking Add New System ..

Similarly you can stop apply , if you need ..


7. Now test the replication ....
Insert any record to the source table

Now the new record is captured in the CD Table (In our example : CDSALES) with the flag (IBMSNAP_OPERATION) with following values

'I' - Insert operation tbe done on the target table.
'U' - Update operation to be done
'D' - Delete Operation to be done

Finally the changes captured in the CD table is applied in the target table by the apply program.



Note : I have created Capture control server and Apply control server on the same system.