Replication of tables with identity columns

Q Replication allows identity columns in both source and target tables, but because of DB2® restrictions you might need to take extra steps if your source table has columns that are defined with the AS IDENTITY GENERATED ALWAYS clause.

DB2 does not allow inserts into columns that are defined as GENERATED ALWAYS, and so this clause is not supported for Q Replication target tables. Because of this restriction, identity columns are handled differently by replication depending on whether they are in the source or target table:

Source table

If you have an identity column in a source table and you want to replicate it to a target table, create a Q subscription for the source table as usual. The corresponding column in the target table is created with the same data type and with the AS IDENTITY GENERATED BY DEFAULT clause. This behavior enables replication of identity values from the source table because DB2 allows inserts into identity columns that are defined with BY DEFAULT. For bidirectional or peer-to-peer replication, the target table is created with a BY DEFAULT clause only if the source table column includes this same clause; if the source column is defined as GENERATED ALWAYS it cannot be included in a multidirectional Q subscription.

Target table

The replication administration tools create target tables with identity columns that match the source column data type and with the AS IDENTITY GENERATED BY DEFAULT clause. If the target table already exists and includes an identity column, use one of these methods:

Target column is GENERATED BY DEFAULT

Create a Q subscription and map the source identity column to the target identity column. Even if the source column is defined as GENERATED ALWAYS, the target column can accept the generated values from the source.

Target column is GENERATED ALWAYS

Do not include the column in your Q subscription. The target DB2 populates the column automatically when replication inserts into or updates the target table. The behavior of the identity column is the same as for inserts and updates by any other application. If you replicate the same source table to multiple target tables that have identity columns, the identity values in those target tables are independent of each other.

If the source column is defined as GENERATED ALWAYS, you can also create the target table with a numeric column to hold the generated values.

For columns that are defined with AS IDENTITY GENERATED BY DEFAULT, the range of values must be distinct between the source and the target because DB2 does not guarantee uniqueness of identity columns between two different DB2 databases.

For example, the identity column at one site could be set to even numbers (START WITH 2, INCREMENT BY 2) and at the other site the identity column could be set to odd numbers (START WITH 1, INCREMENT BY 2). You could also assign ranges to sites (for example, 1 to 10,000 at one site and 20,000 to 40,000 at the other). The odd-even approach ensures that in a conflict situation, two different rows that accidentally have the same generated identity key do not overwrite one another when the conflict action is to force the change.

In a three-server configuration, you need to use three different starting points and increment by three, for example:

Server 1:  DEF_ID BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY
(START WITH 1 INCREMENT BY 3 NO CYCLE NO CACHE),
Server 2:  DEF_ID BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY
(START WITH 2 INCREMENT BY 3 NO CYCLE NO CACHE),
Server 3: DEF_ID BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY
(START WITH 3 INCREMENT BY 3 NO CYCLE NO CACHE),

The data type of the identity column (SMALLINT, INTEGER, or BIGINT) should be determined by application needs, for example the largest number that you expect in the column.

The identity columns should be NO CYCLE if numbers cannot be reused. Put a plan in place for what to do when the maximum value is reached (SQLSTATE 23522). If you use CYCLE, make sure that a new use of a number does not cause problems for any existing use of the number, including what happens during replication.