Coalesce columns in Instance Data Replication
Summarize
Summary of Coalesce Columns in Instance Data Replication
The coalesce columns feature in Instance Data Replication (IDR) allows you to manage records during data inserts, updates, and deletions across different instances. By default, the system uses the sysid field to identify records, but starting with the San Diego release, you have the option to select a different field for coalescing records, particularly useful when records have varying sysids across instances, such as those created by Discovery.Key FeaturesCoalesce Field Selection:Choose a field in the Replication Entry form on the producer instance to coalesce records.Field Verification:Ensure the selected field is included in the replication entry, exists with the same name, length, and type on the consumer instance, and has its Unique column value set to true in the Dictionary Entry table.Transformations:If transforms are enabled, the Coalesce field on the target table must match in name, type, and length to avoid errors.Key OutcomesWhen properly configured, the coalesce columns feature allows for seamless data synchronization between instances, reducing the risk of errors during data replication. If the configurations are incorrect, error messages will guide you on necessary adjustments, ensuring that you maintain effective data integrity across your ServiceNow instances.
Show less
The coalesce columns feature is used to identify records when you perform data inserts, updates, and deletions across instances using Instance Data Replication (IDR).
By default, the sys_id field is used to coalesce records in IDR. Beginning with the San Diego release, you can choose to use a different field.
Use the coalesce columns feature when records on the consumer instance have a different sys_id than the same record on the producer instance. For example, when the record is created by Discovery and loaded into multiple instances. You select the Coalesce field in the Replication Entry form on the producer.
When you select the Coalesce field, you must verify the following:
- The field that you selected is in the Included Fields on the Replication Entry form on the producer instance.
- The field exists with the same name, length, and type on the consumer instance. Note:The Coalesce field can be different on the consumer instance if you create a valid transform for the field.
- The Unique column value for the field that you selected is set to true in the Dictionary Entry [sys_dictionary] table.
Don’t change the value of the field after it’s set as the Coalesce field.
To change the Coalesce field after it has been set, you must delete the Replication Entry on the producer instance and create a new Replication Entry.
If the Coalesce field doesn’t exist on the consumer instance, the following error is displayed on the Consumer Replication Set form. The consumer instance does not recognize the <column_name> coalesce field as a valid member of the <table_name> table. Create this field or enable a transformation with this table to receive updates from the producer table to the consumer instance.
Transforms
If transform is enabled on the producer instance, the Coalesce field on the target table must have the same name, type, and length. If any of those parameters don’t match, an error is displayed.
If transform is enabled on the producer instance, the mapping is invalid for the Coalesce field, and the consumer syncs entries, the system displays an error on the Consumer Replication Set form.
If transform is enabled for a replication entry with a valid mapping of the Coalesce field and then the table schema for the target table is updated so that the mapping is no longer valid, the system displays an error on the Replication Entry form.
If a replication entry attempts to enable transform and a valid mapping for the Coalesce field doesn’t exist from the source table to the target table, the system displays an error on the Replication Entry form.