IBM Change Data Capture (CDC) Replication step by step guide to install, configure and create instance

This tutorial covers step by step guide  to install and  configure  IBM Change Data Capture (CDC) (Data Replication)  in Linux machine. Also covers that how to create CDC instance for replications. Now let us see the steps to install and configure Change Data Capture in Linux.


Brief Steps

1. Install IBM Change Data Capture Engine (IBM CDC) (in this example, version 6.5) on both source and target machines

2. Create CDC instance on both servers

3. Install Access Server, create  user and start Access Server.

4. Install Management Console in the  client machine and connect to Access Server

5. Create Subscriptions and start replication


Installing  IBM Change Data Capture (IBM DB2) 6.5

1. Copy necessary setup files to any folder in the server where you want to install IBM CDC.

2. Change to the folder where installation binary file is located

3. Make the installation binary file executable and start installation

[root@test ISCDC_652_DB2LUW]# ./setup-cdc-linux-x86-db2luw.bin
-bash: ./setup-cdc-linux-x86-db2luw.bin: Permission denied
[root@test ISCDC_652_DB2LUW]# chmod 777 setup-cdc-linux-x86-db2luw.bin
[root@test ISCDC_652_DB2LUW]# ./setup-cdc-linux-x86-db2luw.bin

Preparing to install...
Extracting the JRE from the installer archive...
Unpacking the JRE...
Extracting the installation resources from the installer archive...
Configuring the installer for this system's environment...

Launching installer...

Preparing CONSOLE Mode Installation...

===============================================================================
Installer                                        (created with InstallAnywhere)
-------------------------------------------------------------------------------


===============================================================================
Introduction
------------

InstallAnywhere will guide you through the installation of IBM InfoSphere
Change Data Capture (IBM DB2) 6.5

Respond to each prompt to proceed to the next step in the installation.  If you
want to change something on a previous step, type 'back'.

You may cancel this installation at any time by typing 'quit'.

PRESS <ENTER> TO CONTINUE:


Now Press Enter

===============================================================================


    International Program License Agreement is displayed

Press Enter to continue viewing the license agreement, or enter "1" to
   accept the agreement, "2" to decline it, "3" to print it, or "99" to go back
   to the previous screen.: 1

===============================================================================
Choose Install Folder

Where would you like to install?

  Default Install Folder: /opt/IBM/InfoSphereChangeDataCapture/ReplicationEngineforIBMDB2


Change install folder if required and  press Enter


ENTER AN ABSOLUTE PATH, OR PRESS <ENTER> TO ACCEPT THE DEFAULT
      : /opt/IBM/CDC/Engine

INSTALL FOLDER IS: /opt/IBM/CDC/Engine
   IS THIS CORRECT? (Y/N): Y

Press Y

===============================================================================
Pre-Installation Summary


Please Review the Following Before Continuing:

Product Name:
    IBM InfoSphere Change Data Capture (IBM DB2)

Install Folder:
    /opt/IBM/CDC/Engine

Link Folder:
    /tmp/install.dir..../Do_Not_Install

Disk Space Information (for Installation Target):
    Required:  .... bytes
    Available: .... bytes

PRESS <ENTER> TO CONTINUE:

Press enter to continue..


===============================================================================
Installing...
-------------

 [==================|==================|==================|==================]
 [------------------|------------------|------------------|------------------]



===============================================================================

Install Complete

Congratulations. IBM InfoSphere Change Data Capture (IBM DB2) has been successfully installed to:
   /opt/IBM/CDC/Engine

You can launch the Configuration Tool at any time by running
   /opt/IBM/CDC/Engine/bin/dmconfigurets



Now IBM CDC installation is complete.  Now let us configure and create CDC Instance



Creating New Instance 

Launch Configuration Tool? (1=Yes, 2=No) (DEFAULT: 1): 1
Welcome to the configuration tool for IBM InfoSphere Change Data Capture (IBM DB2). Use this tool to create instances of IBM InfoSphere Change Data Capture (IBM DB2).

Press ENTER to continue...
Initializing. Please wait...

Select 1 to create new instance

CONFIGURATION TOOL - CREATING A NEW INSTANCE


Enter the name of the new instance: sourcedb
Enter the server port number [10901]:
Enter the auto-discovery port number or type 'DISABLE' [DISABLE]:

Staging Store Disk Quota is used to limit the disk space used by IBM InfoSphere Change Data Capture staging Store. If this space is exhausted, this instance may run at a lower speed. The minimum value allowed is 1 GB.

Enter the Staging Store Disk Quota for this instance (GB) [100]: 10
Enter the Maximum Memory Allowed for this instance (MB) [1024]:
Enter the bit version (32/64) [64]:
Select a DB2 Instance

1. db2inst1
2. Other...

Select a DB2 Instance: 1
Select a database name

1. test
2. Other...

Select a database name: 1
Would you like to configure advanced parameters (y/n) [n]:
Enter the username: db2inst1
Enter the password:
Retrieving schema list...
Metadata schema:

1. ADMINISTRATOR
2. DB2INST1
3. NULLID
4. SQLJ
5. SYSCAT
6. SYSFUN
7. SYSIBM
8. SYSIBMADM
9. SYSIBMINTERNAL
10. SYSIBMTS
11. SYSPROC
12. SYSPUBLIC
13. SYSSTAT
14. SYSTOOLS


Press ENTER to continue...
15. Other...

Select a database schema for metadata tables: 2
Enter the refresh loader path: /opt/IBM/CDC/Loader
Note : Please ensure The Refresh Loader Path directory is created and the user  have read and write permission for the directory.

Creating a new instance. Please wait...


Instance sourcedb was successfully created.

Would you like to START instance db2inst1 now (y/n)?y


Starting instance sourcedb. Please wait...

Instance sourcedb started successfully. Press ENTER to go to the Main menu...



Now the CDC instance for the source database is created and started



To launch configuration tool by command.
Change to the folder
 /opt/IBM/CDC/Engine/bin

 run the below command

 #./dmconfigurets


Initializing. Please wait...


MAIN MENU


1. List Current Instances
2. Add an Instance
3. Edit an Instance
4. Delete an Instance
5. Consolidate Instances

6. Exit

Enter your selection: 1


LIST OF CURRENT INSTANCES
-------------------------

Name      Server Port Database      Schema      Status
--------- ----------- ------------- ----------- ------------
sourcedb  10901       test          DB2INST1    started



to start the CDC instance in 32 bit through command


[root@test bin]# ./dmts32 -I sourcedb
IBM InfoSphere Change Data Capture is running.


to start in 64 bit


[root@test bin]# ./dmts64 -I sourcedb
IBM InfoSphere Change Data Capture is running.


to stop IBM Change Data Capture:

./dmshutdown -I sourcedb



Follow the above steps for the target server where target database is installed. Target database may be in the same server or any different server.  i.e.  Install, Configure CDC  and create CDC instance for the target database.

Start CDC instance 

Now assume that CDC instance is running in both servers.


[root@test bin]# ./dmts64 -I targetdb
IBM InfoSphere Change Data Capture is running.



Example

Source Instance : sourcedb

Destination Instance : targetdb




To make communications between Management Console and replication engine processes, IBM CDC  Access servcer needs to be installed. It can be installed in the same or different machine. Default port to connecte to access server is 10901.  The port can be changed during installation time.

To Install Access Server:

1. Copy necessary setup files to any folder in the server where you want to install access server

2. Change to the folder where installation binary file is located

3. Make the installation binary file executable and start installation

       #./<installation_file>



Create an  user so that access server can be connected from Management console.

To create an Admin user

./dmcreateuser admin admin admin password ADMIN TRUE FALSE TRUE


To start the access server:

[root@test# cd /opt/IBM/CDC/AccessServer/bin
[root@accesssvr bin]# ./dmaccessserver



Now install, Management console in your windows machine. This is a gui based tool to create and manage subscriptions. The same task can also be done in Accessserver with commands. But it is always easy to manage subscriptions using Management console

Once management console is installed, open the Management Console, create subscriptions and start mirroring.

In the next tutorial, we will see how to create and manage subscriptions for data replication.

Reference: IBM Change Data Capture Guide

Read More

db2diag log file path location and how to change location, size and reset db2diag.log file

db2diag.log file is useful for DBA for trouble shooting the database.  All errors, warnings, event messages, and administration notification messages  starting from installation are captured in the db2diag.log file. Also any failure in the db2 server / database are captured in db2diag.log file. DBA  can use this log file and trace. 

We can use db2diag tool with various options for reading and displaying db2diag.log file.

db2diag.log grows indefinitely. db2diag.log can either be a single file or a rotating file with fixed size  depending upon the database configuration made.

Now let us see where the db2diag.log file is stored in Windows and Linux by default.

In Windows (Vista or any Latest OS)

C:\ProgramData\IBM\DB2\<DB2 COPY NAME>\DB2\DIAG0000

eg. C:\ProgramData\IBM\DB2\DB2COPY1\DB2\DIAG0000

where ProgramData is hidden folder.

In Windows (XP or any Old OS)

C:\Documents and Settings\All Users\Application Data\IBM\DB2\<DB2 copy name>\DB2\DIAG0000


You can retrieve the DIAGPATH  from dbm configuration using the command

db2 get dbm cfg | find/I "diagpath"   in windows.

In Linux :

/home/db2inst1/sqllib/db2dump


Get the current diagpath using command in Linux.


db2 get dbm cfg | grep -i "diagpath"   


1. How to change the default path location of db2diag.log?

Command to change db2diag.log location:

db2 update dbm cfg using DIAGPATH local_path

For Eg in Windows, I issued the following command.

C:\Program Files\IBM\SQLLIB_01\BIN>db2 update dbm cfg using DIAGPATH c:/db2log
DB20000I  The UPDATE DATABASE MANAGER CONFIGURATION command completed
successfully.

Now restart the db2 server.

db2 get dbm cfg | find/I "diagpath"
 Diagnostic data directory path               (DIAGPATH) = c:\db2log\
 Current member resolved DIAGPATH                        = c:\db2log\


2. How to reset the db2diag.log file?


If db2diag.log file is single file, it grows indefinitely. You can reset at point of time so that the content of the file is moved to db2diag.log_current_timestamp and  new db2diag.log file is created.

To reset, use the db2diag tool with the option A

db2diag -A

For Example :

c:\ProgramData\IBM\DB2\DB2COPY2\DB2\DIAG0000>db2diag -A db2diag.log
db2diag: Moving "c:\ProgramData\IBM\DB2\DB2COPY2\DB2\DIAG0000\db2diag.log"
         to     "c:\ProgramData\IBM\DB2\DB2COPY2\DB2\DIAG0000\db2diag.log_2015-05-12-08.20.34"



3. How to make db2diag.log  a rotating file with fixed size?


Simply set the size of the db2diag.log file using the below command.

db2 update dbm cfg using DIAGSIZE 10

Now the size of the log file size is fixed to 10 MB. The rotating file starts with db2diag0.log

C:\db2log>db2 get dbm cfg | find/I "diagsize"
 Size of rotating db2diag & notify logs (MB)  (DIAGSIZE) = 10

Read More

JNDI DataSource Configuration in JBoss with DB2 in Eclipse Example

Assume that Eclipse is configured with JBoss Application Server 4.0. Before an application can get data from any Database (db2, Postgress, MySql , Oracle, etc..), it needs to establish a connection to the database. In our example we are going to connect with db2 database (eg. Employee). This can be done using JDBC in following ways.

1. Use the DriverManager class to establish a connection -> Load the Db2 JDBC Driver using Class.forName() & Create a connection to a db2 database using DriverManager.getConnection() method

2. Connect to the db2 database through a DataSource object. This is to use an implementation of the javax.sql.DataSource interface in conjunction with the Java Naming and Directory Interface (JNDI).

Now we are going to connect using the IInd way i.e using an implementation of the javax.sql.DataSource interface in conjunction with JNDI. Please follow the following steps for successful data base connection with db2 . .

It is always better to recycle and reuse existing connections to a data base than opening a new connection. So it needs to maintain a DB connection pool. JBoss supports DBCB which uses the Jakarta-Commons Database Connection Pool

Steps to be followed to configure JNDI Data Source in JBoss 4.0 with DB2 (in our example: jdbc/JbossTestDS)

1) Make an appropriate JDBC driver for DB2 available to JBoss & to your web application (Necessary driver class files for Db2 are available in the JARs db2jcc.jar, db2jcc_license_cisuz.jar, db2jcc_license_cu.jar. Copy these jars to Jboss (Drive:\jboss-4.0.0\server\default\lib) also include these jars to build path of the application )

2) Three Important XML files are to be configured. They are web.xml, Jboss-web.xml, *-ds.xml. Details are given below.

Assume that installation folder of Jboss 4.0 Application Server is D:\jboss-4.0.0. JNDI name is JbossTestDS, Database Name is Employee.

a) Define a reference lookup name (eg. jdbc/JbossTestDS) to an external resource in the WEB-INF\web.xml. Modify the web application deployment descriptor (/WEB-INF/web.xml) to define the JNDI name for the Datasource (ie. jdbc/datasourcename) for the web application level.

web.xml

The following lines will be added in the web.xml before </web-app> for declaring resource requirements.

<resource-ref>
<description>Database Connection</description>
<res-ref-name>jdbc/JbossTestDS</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref> 


b) Next Modify the jboss-web.xml located in the WEB-INF in the application.

jboss-web.xml

The following lines will be added in the jboss-web.xml between <jboss-web> </jboss-web>

<resource-ref>
<res-ref-name>jdbc/JbossTestDS
<res-type>javax.sql.DataSource<jndi-name>java:/jdbc/JbossTestDS </resource-ref>

More than one resource reference also can be defined.

c) Deployment descriptor (web.xml) only define a resource adaptor. This resource adapter can be used by integrating into the JBoss application server using a ds.xml descriptor file. Configure JBoss *-ds.xml ( in our example : jdbc-db.xml ) located in D:\jboss-4.0.0\server\default\deploy
jdbc-ds.xml

The following lines will be added in the jdbc-ds.xml between <datasources> </datasources>

<local-tx-datasource>
<jndi-name>jdbc/JbossTestDS</jndi-name>
<connection-url>jdbc:db2://localhost:50000/employee</connection-url>
<driver-class>com.ibm.db2.jcc.DB2Driver</driver-class>
<user-name>abc</user-name>
<password>xyz</password>
<min-pool-size>0</min-pool-size>
<metadata>
<type-mapping>DB2</type-mapping>
</metadata>
</local-tx-datasource>

Note that the jndi name jdbc/JbossTestDS must match the resource reference specified in the web application deployment descriptor

Finally write Java Code to use the resource.

ConnectionFactory.java

    
 public static Connection getConnection() throws CDRCFatalException {

  Context  m_ctxLookup = null;

  DataSource  ds = null;

  Connection  conn = null;

 try {

    Context initCtx = new InitialContext();

    Context envCtx = (Context) initCtx.lookup("java:comp/env");

     ds = (DataSource)   envCtx.lookup("jdbc/JbossTestDS");

 } catch (NamingException exc) {

   System.out.println("Lookup fail db " + exc.toString()+ " The context is " + m_ctxLookup);

   throw new CDRCFatalException( exc.getMessage(), "01001" );

  }



  try {

      conn = ds.getConnection();

  }catch (SQLException exc) {

   System.out.println(" Fail to connect database " + exc.toString()+ " The context is " + m_ctxLookup);

   throw new CDRCFatalException( exc.getMessage(), "02006");

  }

  return conn;

 }

You can call the above getConnection() static method whereever connection is required in Java Code

 Connection conn = null;

conn = ConnectionFactory.getConnection();

 

Read More

Error "The CODEPAGE option is incompatible with the LOBSINFILE option" Solution

Error : The "CODEPAGE" option is incompatible with the "LOBSINFILE" option

Cause of the Error: The error comes due to code page mismatch between source database and target database. See the below example.  CODEPAGE is page encoding. By default OS also having default encoding (1252).

db2move sourceDB export
                          - where codepage of sourceDB was set to 1208

db2move targetDB import
                           - where codepage of sourceDB was set to 1252

in the above case, error is caused due to codepage mismatch.

Solution : Change the target database codepage to 1208. Follow the below steps to change code page.
1. db2set db2codepage=1208
2. db2start
3. db2stop


You can find out the code page of a database using the following commands
1. db2 connect to dbname
2. db2 get db cfg | find "code"

You may get the below lines

Database code page                                      = 1208
Database code set                                       = utf-8
Database country/region code                            = 1

Read More

How to Configure JDBC Datasource (JNDI) in Tomcat 7 with DB2 in Ecplise Example.

The following steps tells how to Configure JDBC Data Resources (JNDI) in Tomcat 7 with DB2 in Ecplise with Example. Assume that Eclipse is configured with Apache Tomcat Application Server 7.0.

A database connection pool creates and manages a pool of connections to a database. It is always better to recycle and reuse already existing connections to a dB than opening a new connection. Tomcat supports DBCP connection pool from the Commons project by default. It is also possible to use any other connection pool that implements javax.sql.DataSource

Suppose if you need to access db2 database via a JDBC driver, follow the following steps for successful data base connection with db2.

Steps to be followed to configure JDBC Data Resources (JNDI) in Tomcat 7 with DB2 (in our example jdbc/javaOnlineDS)

In Brief :
1) Place appropriate JDBC drivers of DB2 to Tomcat for  your web application (i.e place it inside WEB-INF\lib)
2) Configure web.xml, context.xml files as described in the detail section.

In Detail :
Copy all necessary JDBC drivers of DB2 database, to the lib folder under WEB-INF. i.e  copy db2 jdbc drivers db2java.zip, db2jcc.jar, db2jcc_license_cisuz.jar, db2jcc_license_cu.jar to the lib folder.

Next, Declare Resource Requirements in web.xml. Modify the web application deployment descriptor (/WEB-INF/web.xml) to declare the JNDI name for the Database resource (ie. jdbc/javaOnlineDS) for the web application level.

web.xml
The following lines will be added in the web.xml before </web-app> for declaring resource requirements.

<resource-ref>

<description>DB Connection Pool</description>

<res-ref-name>jdbc/javaOnlineDS</res-ref-name>

<res-type>javax.sql.DataSource</res-type>

<res-auth>Container</res-auth>

</resource-ref >

context.xml

The following lines will be added in the context.xml under META-INF between <Context> </Context>

<Resource name="jdbc/javaOnlineDS" auth="Container"   type="javax.sql.DataSource" username="abc" password="*****" driverClassName="com.ibm.db2.jcc.DB2Driver"  url="jdbc:db2://localhost:50000/test" maxActive="8" maxIdle="4" />

Note that the resource name jdbc/javaOnlineDS must match the value specified in the web application deployment descriptor


Now let us write Java code to use the resource. Create a connection class

ConnectionFactory.java

package javaonline;

import java.sql.Connection;

import java.sql.SQLException;

import javax.naming.Context;

import javax.naming.InitialContext;

import javax.naming.NamingException;

import javax.sql.DataSource;


public class ConnectionFactory {

 
  public static Connection getConnection()  {

    Context  m_ctxLookup = null;

    DataSource  dataSource = null;

    Connection  conn  = null;


   try {
 
      Context initCtx = new InitialContext();

      Context envCtx = (Context) initCtx.lookup("java:comp/env");

      dataSource = (DataSource)   envCtx.lookup("jdbc/javaOnlineDS");

    } catch (NamingException exc) {

      System.out.println("Lookup fail of database " + exc.toString()+ "The context is " + m_ctxLookup);

       }


    try {

      conn = dataSource.getConnection();

    }catch (SQLException exc) {

      System.out.println("Getting Connection. Fail of database " + exc.toString()+ "The context is " + m_ctxLookup);

    }

    return conn;

   }

}



Call the above getConnection() static method in DAO class

public class GetDetailsDAO { 
public String getData() throws  JSONException

 {  

  PreparedStatement ps = null;

     ResultSet rs = null;

     Connection conn = null;

    try

     {     

      conn = ConnectionFactory.getConnection();


      String strQry = "SELECT * from Employee";

      ps = conn.prepareStatement(strQry);

      rs = ps.executeQuery();

  ----

  ----
         } catch (SQLException se) {}

 }
}

Read More

Java to create & use DataSource Object in DB2 in the application using DataSource interface without using tools

The preferred way to connect to a database is using DataSource interface rather than using DriverManager interface. DriverManager class requires JDBC driver class name and driver URL. So we have to mention the Driver class and Driver URL which are specific to a JDBC vendor, driver implementation. This reduces the Portabilty of the Application. To improve potability of your applications among data sources, DataSource interface may be used. JDBC version 2.0 provides the DataSource interface. The following program creates datasource object to connect to a database using the DataSource interface in the application itself without using any tool like WAS. Let us see the steps involved to create datasource object and connect to database.

1. Import the package which contains the DataSource interface implementation.

2. Create object of the DataSource implementation (in our example DB2DataSource which has built in support for connection pooling )

3. Set the properties like DatabaseName, Description, User, and Password of the DataSource object.

4. To associate the datasource object with the logical name jdbc/studentDS, register the object with the Java Naming and Directory Interface Naming (JNDI). This is optional.

// Code Starts here

import java.sql.*;        
import javax.sql.*;       // JDBC 2.0 
import com.ibm.db2.jcc.DB2DataSource;   //DB2 Universal JDBC Driver interface supports connection pool
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.*;
import javax.naming.*;    
public class DB2DS {
  public static void main(String[] argv) {
DB2DataSource ds=null;
 try {
ds=new DB2DataSource();        //inbuilt support for connection pool
ds.setDatabaseName("student");          
ds.setDescription("Student Database");
ds.setUser("abc"); //db user id
ds.setPassword("*******"); // db password

 } catch (Exception e) {
      System.out.println("Error"); 
      e.printStackTrace();
      return;
      }
Connection conn = null;
 PreparedStatement pstmt = null;
 ResultSet rs=null;
   try {
    conn=ds.getConnection();      
    if (conn != null) System.out.println("Database Connection Established ");  else System.out.println("DB Connection Failed ");
    pstmt=conn.prepareStatement("Select * from stu_detail");
    rs=pstmt.executeQuery();
    if(rs!=null)
      {
      while(rs.next())
       {
       System.out.println("Student ID: "+rs.getString("ID"));
       System.out.println("Student Name: "+rs.getString("name"));
       }
      }
     } catch (SQLException e) {
        System.out.println("Error in Connection");
        e.printStackTrace();
        return;
     }
}
}

Read More

How to display the number of rows affected in DB2 INSERT / DELETE / UPDATE / MERGE commands

 By default, DB2 does not print the number of records affected when we run the DML  commands like INSERT,  DELETE, UPDATE or MERGE in the command prompt. So to display number of records affected when you run the above DML commands, you can use  following options.

You  can use the option m, in the command itself as given below

db2 -m update employee set payscale='42000-3%-72000' where doj<='1976-01-01'
  Number of rows affected : 50
DB20000I  The SQL command completed successfully.


Also, You can use the below db2set command to set the command options "m" permanently for all instances and databases,

db2set DB2OPTIONS=-m

db2stop

db2start

Or you can update the options m to ON  permanently using the update command options.

db2 update command options using m ON

            In windows, if you may get the  below error OR your changes done to the command option may not be  retained when you run the above command,

error :   db2 update command options using m ON SQL0104N  An unexpected token "m" was found following "USING".  Expected tokens may include:  "A".  SQLSTATE=42601. Solution will be

Just go to db2 prompt.

db2=> update command options using m ON

You can cehck the command options using the below command

list command options


db2 => delete from Employee where emp_code='E1022'
  Number of rows affected : 1
DB20000I  The SQL command completed successfully.

Read More