Replicate Multiple DB2 for i Accounts via the CData Sync CLI

CData Sync for DB2 for i is a stand-alone application that provides solutions for a variety of replication scenarios such as replicating sandbox and production instances into your database. Both Sync for Windows and Sync for Java include a command-line interface (CLI) that makes it easy to manage multiple DB2 for i connections. In this article we show how to use the CLI to replicate multiple DB2 for i accounts.

Configure DB2 for i Connections

You can save connection and email notification settings in an XML configuration file. To replicate multiple DB2 for i accounts, use multiple configuration files. Below is an example configuration to replicate DB2 for i to SQLite:

Windows

<?xml version="1.0" encoding="UTF-8" ?>
<CDataSync>
  <DatabaseType>SQLite</DatabaseType>
  <DatabaseProvider>System.Data.SQLite</DatabaseProvider>
  <ConnectionString>Server=myServer;Port=446;Database=myDatabase;User=myUser;Password=myPassword;</ConnectionString>
  <ReplicateAll>False</ReplicateAll>
  <NotificationUserName></NotificationUserName>
  <DatabaseConnectionString>Data Source=C:\my.db</DatabaseConnectionString>
  <TaskSchedulerStartTime>09:51</TaskSchedulerStartTime>
  <TaskSchedulerInterval>Never</TaskSchedulerInterval>
</CDataSync>

Java

<?xml version="1.0" encoding="UTF-8" ?>
<CDataSync>
<DatabaseType>SQLite</DatabaseType>
  <DatabaseProvider>org.sqlite.JDBC</DatabaseProvider>
  <ConnectionString>Server=myServer;Port=446;Database=myDatabase;User=myUser;Password=myPassword;</ConnectionString>
  <ReplicateAll>False</ReplicateAll>
  <NotificationUserName></NotificationUserName>
  <DatabaseConnectionString>Data Source=C:\my.db</DatabaseConnectionString>
</CDataSync>

Prerequisites

Before setting up the DB2 for i source connector, configure DB2 for i for change data capture (CDC) using journal receivers. The following capabilities aren't supported in Sync:

• Remote journals or failover functionality.
• Large object types such as CLOB, XML, TEXT, and BLOB.

Authenticate to DB2 for i

Set the following required properties:

• Server: The address or host name of the DB2 for i server. The default server is localhost.
• Database: The name of your DB2 for i database.
• Port: The port number for your server. The default port is 446.
• User: The username that authenticates to the DB2 for i database.
• Password: The password that authenticates to the DB2 for i database.

For details on journal/schema selection and creating a CDC job, refer to the Help documentation.

Configure Queries for Each DB2 for i Instance

Sync enables you to control replication with standard SQL. The REPLICATE statement is a high-level command that caches and maintains a table in your database. You can define any SELECT query supported by the DB2 for i API. The statement below caches and incrementally updates a table of DB2 for i data:

REPLICATE MyTable;

You can specify a file containing the replication queries you want to use to update a particular database. Separate replication statements with semicolons. The following options are useful if you are replicating multiple DB2 for i accounts into the same database:

You can use a different table prefix in the REPLICATE SELECT statement:

REPLICATE PROD_MyTable SELECT * FROM MyTable 

Alternatively, you can use a different schema:

REPLICATE PROD.MyTable SELECT * FROM MyTable

Run Sync

After you have configured the connection strings and replication queries, you can run Sync with the following command-line options:

Windows

DB2iNativeSync.exe -g MyProductionDB2iNativeConfig.xml -f MyProductionDB2iNativeSync.sql

Java

java -Xbootclasspath/p:c:\sqlitejdbc.jar -jar DB2iNativeSync.jar -g MyProductionDB2iNativeConfig.xml -f MyProductionDB2iNativeSync.sql