Replication of tables with identity columns

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

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, register and subscribe to the source table as usual. The CD and target tables are created with numeric columns to hold the values. For example, a source column that is defined as GENERATE ALWAYS might be replicated to a BIGINT column at the target. The columns in the CD and target table cannot be identity columns themselves, so you cannot replicate an identity column in a source table to an identity column in a target table.

Target table

If you have an identity column in a target table, do not include that column in your replication configuration when defining the subscription-set member. The column is populated 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 another.

DB2 does not allow inserts into columns that are defined with the AS IDENTITY GENERATED ALWAYS clause, and so this clause is not supported for SQL Replication target tables. However, options exist for replicating these columns:

• Create the target table without the IDENTITY clause.
• Create the target table with a column that is defined with AS IDENTITY GENERATED BY DEFAULT.

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.

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.