Creating a replication set

After you initialize the replication target on the replication source in your Db2 on Red Hat cluster host, you can create a replication set. A replication set is a collection of tables from your source database that you want to replicate on your target database.

Before you begin

This task is done on the cluster host for the replication source. Ensure that you collect the following information before you run the API call:
  • The basic authorization token of the source on the cluster host.1
  • The host names of both the replication source and replication target databases.
  • The external port numbers of both the replication source and replication target databases.2
  • Replication set name for the collection of tables and schema, if any, to be replicated.
  • The source schema and table list for table replication.
  • The schema and table include and exclude patterns for schema replication.
In addition, ensure that Db2® Transport Layer Security (TLS) is set up between your source and target databases. Also, ensure that your tables meet the replication key requirements.
1 You need to create a basic authorization token to run a cURL request. To create your token, run this command:
echo <source_db_username>:<source_db_password> | base64

Where source refers to either the source or destination database.

2 You can retrieve the external port numbers for both the replication source database and replication target database by running the following command:
oc get svc | grep <db2_instance_id> | grep db2u-engn-svc | grep 50001 | sed 's/.*50001://' | sed 's/\/TCP.*//')

About this task

Typically, your replication set would include tables for which you want to maintain transactional consistency, for example all of the tables that are used by an application. For a replication set, all dependent transactions are applied to the target in the source commit order and other transactions are applied in parallel.

Attention: Note the following restrictions:
  • Do not include the same source table in more than one replication set.
  • If you are replicating row-organized tables, ensure that both the source and target tables have an enforced primary key.
  • If you must add a unique constraint to a column-organized table for replication, ensure that you deactivate and reactivate the database before you add the table to the replication set. If this step is not done, the performance of Db2 DELETE operations on the table are significantly degraded. This impact on performance is the result of Db2 supplemental logging not detecting the constraint until the associated cache is refreshed on all data members in the system.

Procedure

  1. Create a replication set for the specified source and target systems:
    curl --insecure -X POST --header 'Content-Type: application/json' --header 'Accept: application/json' --header 'Authorization: Basic <your_authorization_token>' -d '{ "sourceHost": "<source_host_name>", "sourceDatabase": "BLUDB", "sourcePort": <source_rest_port>, "targetHost": "<target_host_name>", "targetDatabase": "BLUDB", "targetPort": <target_rest_port>, "tables": [ { "sourceSchema": "<source_table_schema>", "sourceTable": "<source_table_name>", "skipLoad": false } ], "schemaSubs": [ "schemaIncludePattern": "<schema_pattern>", "tableIncludePattern": "<table_pattern>", "excludePattern": [ { "schemaName": "<source_schema>", "objectName": "<object_name>" } ], "skipLoad": false } ], "startReplication": true }' 
    'https://<source_hostname>:<source_rest_port>/dr/v1/replicationset/<replication_set_name>'
    Note: You can specify zero or more source table schema and schema patterns in the JSON array.
    • If no source table schema or schema patterns are specified, an empty replication set is created.
    • If you do not want initial loading of the target table, include “skipLoad”: true.
    • If you do not want to start the replication set after it is created, include “startReplication”: false.

    This API call returns a job number (id).

    {
      "id": <job_number>,
      "url": "https://<source_hostname>:<source_rest_external_port>/dr/v1/target/progress/<job_number>"
    }
    
  2. Include the job number in the following API call to check the progress of the replication set creation:
    curl --insecure -X GET --header 'Accept: application/json' --header 'Authorization: Basic <your_authorization_token>' 
    'https://<source_hostname>:<source_rest_port>/dr/v1/replicationset/progress/<job_number>'

Results

A table that is added to a replication set is ready to replicate when a response message that contains executionState=COMPLETED and overallJobResult=PASS is returned. Until these values are returned, examine the error code, message, and resolution in the response.
The following table lists the possible HTTP status codes and response messages for the replication set creation API call. It also includes sample code to show the response syntax.
HTTP Status Codes Reason Response Model
202 Replication set creation started The user request returns a successful response for the completion of the API call (sample value):
{
  "id": 5,
  "url": "https://dr-source1.ibm.com:31023/dr/v1/replicationset/progress/5",
 "cgName": "DEMORE0001"
}
200 Status check on API is successful Look for executionState=COMPLETED and overallJobResult=PASS in the response.
4xx Client request error The user request was not fulfilled due to malformed request syntax or other client issues:
{
  "errorCode": "string",
  "errorMessage": "string",
  "errorResolution": "string"
}
5xx Server error The user request appeared valid, but was not fulfilled due to an issue with the server:
{
  "errorCode": "string",
  "errorMessage": "string",
  "errorResolution": "string"
}

Example

The following example shows the command syntax for creating a replication set from the source database. See step 1.
curl --insecure -X PUT --header 'Content-Type: application/json' --header 'Accept: application/json' --header 'Authorization: Basic ZHNhZG06MDNRVWlMczFhcmoxb3ky' -d '{ "sourceHost": "dr-source1.ibm.com", "sourceDatabase": "BLUDB", "sourcePort": 32621, "targetHost": "dr-target2.ibm.com", "targetDatabase": "BLUDB", "targetPort": 30138, "tables": [ { "sourceSchema": "DRADMIN1", "sourceTable": "T5R", "skipLoad": false }, { "sourceSchema": "DRADMIN2", "sourceTable": "T5C", "skipLoad": false } ] }' 
'https://dr-source1.ibm.com:31023/dr/v1/replicationset/DEMOREPLSET1/DEMORE0001/table'
The following example shows the command syntax for checking the status of the replication set creation by using the generated job number, 9. See step 2.

curl --insecure -X GET --header 'Accept: application/json' --header 'Authorization: Basic ZHNhZG06MDNRVWlMczFhcmoxb3ky' 
'https://dr-source1.ibm.com:30674/dr/v1/replicationset/progress/5'

What to do next

Deleting one or more tables from a replication set.