Data Replication can replicate Insert and Update operations on source columns that contain database-generated values.
The following types of columns can contain database-generated values:
DB2 for Linux, UNIX, and Windows identity columns
Microsoft SQL Server identity columns
MySQL identity columns
Oracle columns that are the targets of numbers generated by sequence objects
Data Replication replicates the generated values from the source to the target.
If the target also contains identity columns or sequence objects, the following replication considerations apply:
For Microsoft SQL Server targets, Data Replication uses the IDENTITY_INSERT statement to explicitly insert values into the identity columns on the target.
For DB2 for Linux, UNIX, and Windows targets, you must use the GENERATED BY DEFAULT AS IDENTITY clause to define an identity column on the target. The Applier can then replicate generated values from the source to the identity column on the target. If you use the GENERATED ALWAYS AS IDENTITY clause, the Applier cannot replicate source generated values to the target identify column.
If a failover to the target database occurs, you must manually reset the current value of an identity column or sequence object on the target prior to performing Insert and Update operations that retrieve generated values from the target. Execute a SELECT statement to determine the maximum value for the target column that holds the identity column or sequence object values. Then set the identity column or sequence object value to that maximum value plus an "increment by" value that is used to generate the next value.
For example, a sequence object named GUID_SEQ, which has a last value of 100, is used to generate values for a column in an Oracle table. The maximum value for the table column is 224. To determine the next value using the "increment by" value of 124, execute the following SQL statements:
ALTER SEQUENCE GUID_SEQ INCREMENT BY 124;
SELECT GUID_SEQ.nextval FROM DUAL;
ALTER SEQUENCE GUID_SEQ INCREMENT BY 1;
Data Replication does not support bidirectional replication of database-generated values between columns that have a uniqueness constraint. To avoid uniqueness constraint violations, use conflict resolution rules.