Db2 SQL Replication Step by step with example

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 useful
                       1. 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 schema -> 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) -> Subscription 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.