Replicating IBM Db2 on Cloud data

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:

1. From the main navigation pane, click Services > Service instances.
2. Find and select the Db2 instance name.
3. Click Go to UI.
4. 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:

1. 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.
   

2. 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')
   

3. 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;
      

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 in Cloud Pak for Data as a Service, see IBM Db2 on Cloud connection.

Next step

Running replication jobs

Parent topic: Supported Data Replication connections