Creating a replication set for Db2 Warehouse

After you activate replication, you create a replication set to specify a set of tables to be replicated to a designated target.

Before you begin

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.

Restrictions:

  • Do not include the same source table in more than one replication set.
  • If you are replicating row-organized tables, the source and target tables must have an enforced primary key.
  • If you must add a unique constraint to a column-organized table for replication, you must deactivate and reactivate the database before adding the table to the replication set. Otherwise, Db2 Warehouse performance for delete operations on the table can be significantly degraded because Db2 Warehouse supplemental logging does not detect the constraint until its cache is refreshed on all data members in the system.

Procedure

  1. From the replication home page of the web console, click Create replication set.
  2. Select an existing target database on which replication has been activated, or add a new target.
  3. If you are adding a new target, enter the credentials for connecting to the target database:
    • Host name or IP address: The fully qualified host name or IP address of the target database.
    • User ID and Password for connecting. The user must be an admin user on the target database (bluadmin).
    • The Database name and database Port number.

    After you enter the connection details, click Next.

  4. Select tables to add to the replication set. You can filter by schema and select all tables that belong to a given schema.

    The console finds all schemas and tables from the source database and gets row counts from the Db2 Warehouse catalogs. It also checks to see if tables have a unique constraint.

    • To select all tables in a schemas, use the check box to the left of each schema.
    • To select a subset of the tables, click the schema row to view all tables that are part of the schema and select individual tables from the right-side area. The number of tables that you selected appears in the upper right corner. You can search for tables based on the name by using the search boxes.

    When you create the set, one or more target tables are created to match the source tables that you specified for the set, unless such tables already exists.

  5. Enter a name for the replication set and choose among the following options:
    Start replication immediately
    Uncheck this option if you want to manually start replication individually for each table or for groups of tables after the set is created.
    Copy existing table data and replicate future updates
    The target tables are truncated and loaded with the data from the source. Any changes to the source table during the target loading process are staged and applied after the load completes.
    Important: Do not choose this option if you want to preserve the data in the target tables, as in the following examples:
    • The source and target tables are already in sync.
    • Replication is configured in both directions and the load needs to be performed in only one direction (for example, if you are setting up reverse replication from a standby server back to the primary server for supporting a failover/failback scenario).
    Only replicate future updates
    Changes to the source table are replicated to this target. Choose this option when setting up reverse direction replication.

    After making your selections, click Finish.

Results

While the target table is prepared, the Replication Set Status column on the replication home page shows a value of Configuring. If you selected Start replication automatically, the value changes to Active when the process completes. Otherwise it changes to Inactive and you should start the set manually.

When you create a replication set:

  • IBM® MQ objects including message queues are created at the target system.
  • Q Replication subscriptions are created and activated for all selected tables.
  • If you specified Copy existing table data and replicate future updates, tables are created at the target database to match the source tables.
  • Target tables are loaded with data from the source by using remote external table load, and are synchronized with the source.

None of these processes affect applications at the source that work with source tables.