Choosing the Db2 replication key

Replication requires a unique constraint on each table to uniquely identify each row across all databases where the row might be replicated. This is the replication key. The apply program specifies this key is specified in a WHERE clause for updates and deletes to ensure that the correct row is updated.

Key values must have the same meaning in each database instance and be globally unique. A unique index that is associated with the constraint is mandatory for detecting and resolving conflicts.

Without an index, data can silently diverge between the source and target in case of conflicts (cases where the same row is updated at different sites before the data is replicated.

Important:
  • Row-organized tables must have an enforced primary key, unique constraint, or unique index.
  • Column-organized can have non-enforced primary key or unique constraint. Even if the key is not enforced by Db2, it must uniquely identify a row or data at the target can silently diverge over time.
  • Adding a unique constraint to a column-organized table that has existing data requires you to deactivate and reactivate the database.

When a table does not have any set of columns that can uniquely identify each row, a common practice is to add a hidden identity column to the table. Db2 then automatically generates a unique value for each row. To ensure that the replication key values are unique to each site, you can add a second column that identifies the site. By combining both the identity column and the site column as the replication key, you guarantee that unique values are generated wherever site your application runs. These columns can be declared hidden so that your applications are not impacted.

For example, the following statements create two hidden columns, IBMQREP_REPLKEY and IBMQREP_SITE, which uniquely identity each row: 

IBMQREP_REPLKEY BIGINT NOT NULL IMPLICITLY HIDDEN GENERATED ALWAYS AS IDENTITY(START WITH 1, INCREMENT BY 1)
IBMQREP_SITE SMALLINT NOT NULL IMPLICITLY HIDDEN WITH DEFAULT 1

Where the default for the IBMQREP_SITE column is 1 or 2 depending on the site at which it is created.

The following statement adds a unique index that references the two hidden columns:

ALTER TABLE table_name ADD CONSTRAINT IBMQREP_UNIQCONST UNIQUE(IBMQREP_REPLKEY, IBMQREP_SITE) NOT ENFORCED;

For a more detailed discussion of key requirements, including methods of adding a unique constraint to tables that do not have any, see pages 21-24 in Replicating Transactions on Db2 Columnar Tables with Db2 V11.5 .