The SQL Anywhere server does not automatically keep information on when a table was last accessed or when a procedure was last executed.
You could try to keep this statistic yourself but it would not be easy nor cheap and you would want to be very careful about not adding deadlocks or points of contention.
To keep track of when a procedure was last executed you could add a line at the beginning of all of your procedures (or at least the ones your cared about) that fired an event and passed the name (and owner) of the procedure and the current time. The event would update a table (that has columns to record the "name" of the procedure and last executed "time") with the passed in time if the new time was after the currently recorded time for the given named procedure.
Note you would not want to try recording the time in the table from directly within the procedure since (a) that would cause a point of contention with all connections trying to update the table and could cause a deadlock, and (b) the update could get rolled back if the connection did a rollback operation.
Firing an event for every procedure call could add a considerable amount of work for the server to perform if there are a lot of procedure calls / events firing and thus would slow down everything eventually. (i.e. nothing comes for free).
It would be much harder to track last accessed of a table. You could only do it if all access to the table was through executing a procedure.... and then a similar method to the one above could be used.
You could try hiding all tables behind a view and only let access to the table data through the view. You could then craft the view to execute a UDF that would record the access time (by firing an event... since like above you would not want to do it on the same connection that is accessing the table/view)... but this would be VERY expensive and I would not recommend doing it (and perhaps I should not have mentioned it... but someone out there may have thought 'hey, what about using a view?' and I wanted to make the point that I would not recommend doing it!)
Note it would be relatively easy (but still take some effort and would not be cheap) to know when a table was last updated (by inserts or updates) by adding a column to each table (or the ones you care about) that is has DEFAULT TIMESTAMP default value... and then select max from this column when you wanted to know when the table was last updated. As mentioned, this would not be cheap to compute if the table is large - you would try adding an index on this last updated time column in an attempt to making finding the max value a bit cheaper but causing extra expense to maintain the index.
Another way, and perhaps a better method, of tracking updates (including inserts, updates, and deletes) to a table would be to add a statement level insert, update, and delete trigger and have the trigger record that the table was updated (by firing an event... since like above you would not want to do this on the same connection in case the connection rolled back the transaction and to not cause a single point of contention). Like the other solutions, this method would have a cost in terms of added workload on the server.
I.e. this class of problem is hard to solve. It would be cheaper for the database server to track this information but this would add extra contention internally and thus slow down everyone. 😞
So if you really need to know when a table was last accessed then I'd recommend keeping track of this information at the application level. Not a great solution, but likely the best you could do.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.