When was my table last modified?

[Former Member]

I have a table containing millions of rows. I need to know when that table was last modified.

We have tried using a trigger which updates an entry in a separate table, but some of our transactions take time and the resulting deadlocks killed us.

I thought I had read somewhere that SQL Anywhere 11 introduced a mechanism for reading a "last modified" timestamp for a given table, but I have googled my little eyes out and found nothing.

(We are using SQL Anywhere 12 now though)

Any elegant solutions? Or do I need to put a timestamp on every row?

[MarkCulp · Accepted solution]

You could use the last_modified_at column in the SYSTAB table or if you don't actually care about knowing the actual time use the last_modified_tsn column. The drawback is that these values only get updated (in the table) when a checkpoint occurs.

Alternatively you could add a "last_modified timestamp default timestamp" to your table, index the column, and then do a "select top 1 last_modified from T order by last_modified desc" to get the time of the last modified row.

[Former Member · Accepted solution]

The docs doesn't explicitly say that last_modified_tsn is only updated when a checkpoint occurs. (but it would make sense)

Is it the same type of checkpoint that are mentioned in the log: "Starting checkpoint of "mm3tom" (mikromarc.db) at Tue Sep 27 2011 13:54" ?

I need a bit more accuracy than once every hour. Adding an entire timestamp column just to get at a single value seems as a bit of an overkill.

Thanks for the help though.

[MarkCulp · Accepted solution]

Ths page (SYSTAB table) in the docs that I refered to mentions that the column is only updated at a checkpoint: "last_modified_at - TIMESTAMP - The time at which the data in the table was last modified. This column is only updated at checkpoint time.".

Re. "Is it the same type of checkpoint that are mentioned in the log ...": Yes.

Depending on how often you want to get the last modified value you could choose to do an explicit checkpoint (by issuing the "CHECKPOINT" statement)... but I would not do this often since (manual) checkpoints are expensive.

[VolkerBarth · Accepted solution]

@Mark: The docs don't state that the "last_modified_tsn" column is only updated on checkpoints as well - is it?

[VolkerBarth · Accepted solution]

You can explicitly influence the maximum time between checkpoint intervals by setting the dbsrv12 -gc server option - just in case an hour is too long but a shorter frequency like 10 minutes might be affordable (both for accuracy and database performance...)

[VolkerBarth]

Are you interested in schema or data modifications on that table?

EDIT: Based on Mark's response, I correct my statement - I totally missed those SYSTAB columns...

AFAIK, there's only the creation time for all database objects available, cf. the SYSOBJECT system view. But that does only refer to the creation of the table itself, not its data.

A common approach to monitor changed data would be to add a column with "DEFAULT TIMESTAMP" to your table. Columns with this special value get updated automatically if you insert/update the according row (unless you set the value explicitly when inserting/updating).