How is updating a DEFAULT TIMESTAMP column to its current value logged for SQL Remote?

[VolkerBarth]

[Preface: I admit that this is a rather nebulous question title and a possibly uncommon problem. Still, it is a problem.]

AFAIK, updating a column to the same value is a No-Op in SQL Anywhere, i.e.

update Products
set Name = Name

will be silently ignored by the server - at least unless there are any particular actions that change other columns by means of a trigger or particular special values like DEFAULT TIMESTAMP or LAST USER.

It seems that such a statement is therefore not contained in the transaction log at all.

When using special values like DEFAULT TIMESTAMP, the situation changes. Say, table Products would contain an additional column dtLastChanged declared with DEFAULT TIMESTAMP.

Then the exact above statement (omitting the dtLastChanged column) would get logged basically as

update Products
set dtLastChanged = current timestamp

---

In our SQL Remote setup, we use such a DEFAULT TIMESTAMP column for each table to track changes. This basically works well. However, when doing maintenance tasks (like "correcting" data), we sometimes want to preserve the current dtLastChanged value (meaning: our update has not really "changed" the row logically).

To do so, one would usually just explicitly set the dtLastChanged column in the update like

update Products
set Name = upper(Name), dtLastChanged = dtLastChanged

That works fine in the current database. However, as the dtLastChanged column is unchanged, the statement will be logged in the transaction log as

update Products
set Name = upper(Name)

(possibly omitting those products whose Name is already in upper case).

Now, when these statements are replicated with SQL Remote, the receiving database will necessarily set dtLastChanged to a new value because the column is not explicitly set in the replicated form of the SQL statement (i.e. the logged one).

Resume:

In contrast to our intention, the row now has a different dtLastChanged value for the sending and the receiving database: the current timestamp in the receiving database, the previous timestamp in the sending database. In other words: Inconsistent data.

How can I prevent such unwanted changes of DEFAULT TIMESTAMP columns?

[VolkerBarth]

Lesson learned: Covering up one's tracks ain't that easy:)

[VolkerBarth · Accepted solution]

One simple solution is to change the dtLastChanged column slightly, say to add a fraction of a second. That would still hide the date of the change as intended, and would change the value of that column "enough" to get logged in the transaction log.

update Products
set Name = upper(Name), dtLastChanged = dateadd(ms, 1, dtLastChanged)

Therefore, it will be replicated with an explicit value for dtLastChanged and, as a consequence, prevent the receiving site from recalculating that value.

---

One caveat:

I have found out that this does not work as expected when particular database options have non-default values.

When both truncate_timestamp_values is set to "On" and default_timestamp_increment is set to a larger value than the above statement uses (say, to 10.000 microseconds, e.g. for MS SQL Server datetime compatibility), then the above statement will not change the value of dtLastChanged because the new value after truncation and the former value will be identical. That will again lead to an omission of that column in the replicated form of the statement.

So, one should make sure that the added fraction is bigger than the default_timestamp_increment.

Note that default_timestamp_increment uses microseconds whereas dateadd(ms, ...) uses milliseconds. SA 12 introduces the "mcs" microsecond datepart.