cancel
Showing results for 
Search instead for 
Did you mean: 

When was my table last modified?

Former Member
13,518

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?

View Entire Topic
MarkCulp
Participant

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
0 Kudos

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
Participant
0 Kudos

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
Contributor
0 Kudos

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

VolkerBarth
Contributor
0 Kudos

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...)