Version 25.3.9396

• Prerequisites
  • Requirements
  • Set Up DB2 for Enhanced CDC
• Add the DB2 Connector
• Authenticate to DB2
• Complete Your Connection

You can use the DB2 connector from the CData Sync application to capture data from DB2 and move it to any supported destination. To do so, you need to add the connector, authenticate to the connector, and complete your connection.

Prerequisites

Before you add and set up the DB2 source connector, you need to set up the DB2 database for enhanced change data capture (CDC), as explained in the following sections.

Requirements

Before you set up DB2 for enhanced CDC, ensure that you have DB2 11.5 or later.

Sync enhanced CDC uses DB2 SQL replication technology, which depends on Abstract Syntax Notation (ASN) libraries. These libraries are essential for operation and require an IBM InfoSphere Data Replication (IIDR) license. However, IIDR installation is not necessary.

Set Up DB2 for Enhanced CDC

Before you add your DB2 connector, you need enable change data capture (CDC) in the database and put your tables in capture mode, as follows:

1. Log in to DB2 as the db2inst1 user.

2. Enable (start) the ASN agent (capture program), create the schema where all captured table changes will be stored, and add the tables for which you want to capture changes, as follows. These steps are based on the DB2 portions of the IBM SQL Replication Guide and Reference.

   1. Download the required User-Defined Functions (UDF) from here and add them to your DB2 server directory (for example, $HOME/asncdctools/src).

      The DB2 server’s PATH environment variable contains the DB2 path to the bldrtn command (for example, export PATH=$PATH:/opt/ibm/db2/V11.5.0.0/samples/c/ command appends the directory /opt/ibm/db2/V11.5.0.0/samples/c/ to the existing PATH variable). As a result, you can run the bldrtn command from anywhere in your terminal without specifying the full path. Use this command to compile the UDFs, as shown below:

      cd $HOME/asncdctools/src   
      bldrtn asncdc
      

   2. Make sure that the JDBC driver can read the DB2 metadata catalog by submitting the following commands:

      # Replace the {...} placeholders as required
      cd $HOME/sqllib/bnd  
      db2 connect to YourDatabaseName  
      db2 bind db2schema.bnd blocking all grant public sqlerror continue
      

   3. Ensure that the database was recently backed up. The ASN agents must have a recent starting point from which to read. If you need to perform a backup, run the following commands:

      db2 backup db YourDatabaseName to YourBackupLocation
      db2 restart db YourDatabaseName
      

      These commands trim (prune) the data so that only the most recent version is available. If you do not need to retain older versions of the data, specify dev/null for the backup location.

   4. Connect to your DB2 database to install the UDFs. Log in as the db2inst1 user and execute the following commands:

      db2 connect to YourDatabaseName
      cp $HOME/asncdctools/src/asncdc $HOME/sqllib/function  
      chmod 777 $HOME/sqllib/function
      

   5. Enable the functions that will start and stop the ASN capture agent.

      db2 -tvmf $HOME/asncdctools/src/asncdc_UDF.sql

   6. Create the ASN control tables.

      db2 -tvmf $HOME/asncdctools/src/asncdctables.sql

   7. Create the functions that will be used to add control table to or remove them from capture mode.

      db2 -tvmf $HOME/asncdctools/src/asncdcaddremove.sql

3. Create a connection with your database. Then, enable the DB2 ASN capture agent and add tables for capture mode.

   -- start the ASN agent
   VALUES ASNCDC.ASNCDCSERVICES('start','asncdc'); 
   
   --- add a table to capture mode
   CALL ASNCDC.ADDTABLE('SchemaName', 'TableName');
   CALL ASNCDC.ADDTABLE('DB2INST1', 'TEST_TABLE');
   
   ----------------------------------------------------
   -- Other commands that you can use:
   -- Reinitiate or stop the ASN agent
   VALUES ASNCDC.ASNCDCSERVICES('reinit','asncdc');
   VALUES ASNCDC.ASNCDCSERVICES('stop','asncdc');
   -- check the status of the ASN agent
   VALUES ASNCDC.ASNCDCSERVICES('status','asncdc');
   -- remove a table from the change data capture mode: 
   CALL ASNCDC.REMOVETABLE('SchemaName', 'TableName');  
   

For details about using enhanced CDC, see Enhanced CDC Jobs in CData Sync.

Add the DB2 Connector

To enable Sync to use data from DB2, you first must add the connector, as follows:

1. Open the Connections page of the Sync dashboard.

2. Click Add Connection to open the Select Connectors page.

3. Click the Sources tab and locate the DB2 (Native) row.

4. Click the Configure Connection icon at the end of that row to open the New Connection page. If the Configure Connection icon is not available, click the Download Connector icon to install the DB2 (Native) connector. For more information about installing new connectors, see Connections.

Authenticate to DB2

After you add the connector, you need to set the required properties.

• Connection Name - Enter a connection name of your choice.

• Server - Enter the address or host name of the DB2 (Native) server. The default server is localhost.

• Port - Enter the port on which your DB2 server is listening. The default port is 50000.

• Database - Enter the name of your DB2 (Native) database.

• User - Enter the username that you use to authenticate to the DB2 (Native) database.

• Password - Enter the password that you use to authenticate to the DB2 (Native) database.

Complete Your Connection

To complete your connection:

1. Define advanced connection settings on the Advanced tab. (In most cases, though, you should not need these settings.)

2. Click Create & Test to create your connection.