At the bottom of the transform Properties form is a section called "Data Change Detection Method". Normally, the radio button for the option "Standard CDC Method" should be selected; however, under special circumstances, "Timestamp Field Method" can be chosen. This method requires two special conditions to operate correctly. First, there must be a column that contains timestamp information within the table that is updated with the current timestamp every time the record is updated. Second, there must not be any deletes performed against the data (unless you do not care if the deletes get propagated to the target). The "Timestamp Field Method" only operates against inserts and updates of the data, and deletes are never performed. You can think of this method as an archival of any inserts or updates to the data. When these two special conditions are present, the "Timestamp Field Method" is the preferred method of synchronization.
Put another way, the source table must contain a timestamp field that will show the date and time that each record is updated. Also, any records deleted from the source table will not be deleted from the target table.
When the "Timestamp Field Method" radio button is chosen, a drop-down list will show all available timestamp fields. Choose from this list the field updated with a new timestamp every time the record is modified.
The edit field called "Drift Seconds" is used to handle time drift for the source data database machine. For example, if the source database machine is synchronized on a daily basis using an NTP service, the apparent time of the machine may suddenly change by a small amount. To avoid the possibility of missing a crucial data update, a "drift seconds" field is supplied that allows for time corrections to the machine's internal clock. Due to the existence of leap seconds, this field should always be set to at least one second, even if the clock on the source database machine is perfectly accurate.
So let's examine how we might use "Timestamp Field Method" on a table that contains a field we can use for this purpose. We will create a transform called GeneralLedgerTransactions against a MySQL table called gltrans:
Because this table has a field that contains date/time information called transdate, we will use this field to perform our synchronization.
First we perform a full synchronization to get a perfect copy of the source data defined on the target table.
Next we will change a record in the source table, updating the transaction date along with the period.
Having modified a record in the source table, we perform an automatic synchronization and get this result:
It may seem surprising that we see five records updated instead of one record. This oddity is due to the fact that we synchronize all changes up to and including the last moment in the data from our previous synchronization (with an error window of "drift seconds").
Since our oldest transaction date from the previous cycle was 5/17/2009, we see that the records with counterindex values of 105, 106, 107, and 108 have also been transferred to the target table. (These are known as "time window" updates.)
The result is that our target table now contains the same information as the original source data. Remember that deletes performed against the source data using this technique will not be reflected on the target table. Hence, if five records had been removed from the source table, we would still see them on the target table.
Also, if we synchronize the table again without making any changes, we will (in this case) get one record updated. The reason that we see an apparent update is that the most recent date from the data in our last synchronization is within the window of: (newest timestamp transferred +/- drift seconds). Thus, we will always see at least one record updated every time we synchronize using this method.
