Replicating IBM Db2 on Cloud data

You can replicate data to and from IBM Db2 on Cloud with Data Replication.

To set up replication for Db2 on Cloud, first configure your Db2 on Cloud service, and then add a connection to the service in a project.

Restriction

You can replicate only some Db2 on Cloud data types. For more information, see Supported Db2 on Cloud data types.

Before you begin

Check the pricing plan for your Db2 on Cloud service instance. Data Replication can replicate from databases that are running on Standard or Enterprise Db2 on Cloud pricing plans, but not from databases that are running on Lite plans. Data Replication can replicate to databases running on any pricing plan.

To check the pricing plan for your Db2 on Cloud service instance:

From the main navigation pane, click Services > Service instances.
Find and select the Db2 instance name.
Click Go to UI.
From the left menu bar, click the About icon to view your plan details.

Configuring Db2 on Cloud as a source

To configure Db2 on Cloud as a source:

Data Replication replicates from Db2 row-organized tables only. Verify that the tables you intend to replicate from are row-organized and not column-organized. In this example, the 'R' in column TABLEORG indicates that table SALES_ROW is row-organized and can be replicated.
```
SELECT tabname, tableorg FROM SYSCAT.TABLES WHERE tabname like 'SALES%'

TABNAME TABLEORG
------------------------------- --------
SALES_COL C
SALES_ROW R

2 record(s) selected.
```
Configure database logging parameters. Enable log retention for each database that you intend to use for replication. If you want to replicate table structure changes, enable DDL statement logging as well.
```
CALL SYSPROC.ADMIN_CMD ('UPDATE DATABASE CONFIGURATION USING LOG_DDL_STMTS YES')
```
Complete the following requirements:
1. Set table DATA CAPTURE CHANGES to YES. In order for Data Replication to replicate schemas and tables, the tables need to have DATA CAPTURE CHANGES set to YES. You can enable this manually for each table you wish to replicate, if the Db2 user account you specify in your connection has both SYSADM and DBADM authorities.
  
  Example:
```
 ALTER TABLE "BANKING"."CREDIT_SCORE" DATA CAPTURE CHANGES;
```
2. Data Replication requires that the table must have a column designated as the primary key.
  
  Example:
```
ALTER TABLE "BANKING"."CREDIT_SCORE"
ADD PRIMARY KEY ("ID")
ENFORCED;
```

You can also replicate a range-partitioned IBM Db2 on Cloud table which is a table that is partitioned such that each partition contains rows with values that fit within a specific range. For example, tables can be partitioned based on the value in a "date" column.

The partitioned tables are replicated to the target data store as regular Db2 on Cloud tables without partitions. Data Replication replicates the following operations:

CREATE TABLE
ADD PARTITION
ATTACH PARTITION

The attach partition operation must be followed by a SET INTEGRITY SQL statement to make the data from the newly attached partition accessible to the source data store and the replication job.
DETACH PARTITION

You can replicate range-partitioned tables to the following target data stores:

IBM® Db2 on Cloud
IBM Db2 Warehouse
Apache Kafka

Note:

Range-partitioned table replication from an IBM Db2 on Cloud source data store is only supported with the Copy business goal. When you create Db2 on Cloud range-partitioned tables, use the PARTITION BY RANGE clause with inclusive STARTING and exclusive ENDING boundaries.

Configuring Db2 on Cloud as a target

To configure Db2 on Cloud as a target, assign database user privileges for target connections. To replicate data to your Db2 database as a target connection, you need only a Db2 user account that can create schemas and tables. Data Replication replicates into column-organized tables in the Db2 target.

Connecting to Db2 on Cloud in a project

To connect to Db2 on Cloud in a project, see IBM Db2 on Cloud connection.

What to do next

Running replication jobs