The Compare Fields option is enabled but unchanged records are updated in the target when the DTS includes links to virtual fields.

Issue:  When using a DTS with an Update object step where linked fields for this step include a few virtual fields (for example, "vfPrimaryCity") on the target, the Compare fields on updates option is enabled, but seems to have little effect.  The Integration job is updating many more records than expected. 

 

How The Compare Fields Feature Works
 

The Compare Fields feature does a field-by-field comparison of the source record to a matching target record before an update is performed.  The compare is only performed on columns that have the Overwrite option checked.  The target record is only updated if there is a difference between the two records.  If all of the fields are the same, the update does not occur and it is considered a skip.
 
The Compare Fields feature does an "old value to new value" comparison and updates the target database if these values are different.  If virtual fields are linked in the Update step, the compare shows the old value as NULL since the field is virtual and an actual value cannot be determined.  In this scenario, the DTS processes the record as an "update" because it determines that the value as changed.
 

Using Virtual Fields
 

Virtual fields are added to tables in the TIBCO Scribe® Workbench to simplify data loading and direct processing activity.  Virtual fields are shown as fields in the TIBCO Scribe® Workbench, even though the fields do not actually exist as fields in the adapter objects.  A virtual field always begins with the characters vf (for "virtual field").  For example, a virtual address field might be named vfPrimaryAddress.
 
You see little change if your job updates an object through the virtual fields exposed by the adapter.  Consider a job that updates Account addresses through vfPrimary address fields.  TIBCO Scribe® does not lookup virtual field values in the target since doing so would have a significant adverse effect on performance.  Since the target data value cannot be returned, any rows where all virtual field data links are not NULL apply an update to the database.
 

Preventing  Unnecessary Updates
 

Instead of using the Compare Fields feature, identify a field in the target system that contains a MODIFIEDON value, and use the :LASTRUNDATETIME function to identify changed records.

For more information, see the following in the TIBCO Scribe® Insight Help Library:
 

• System Variables

The Compare Fields option is enabled but unchanged records are updated in the target when the DTS includes links to virtual fields.