Table Modification times

Can anyone tell me how I can determine if/when a table has been updated. I'm trying to write a script that monitors certain tables in a database and don't need to know what changes have been made, simply if a table's data/schema has been modified. Is there any way to differentiate between a schema update and a data update? I'm looking for something comparable to the unix mtime and ctime stamps on a file's inode if that helps anyone to identify with my needs... I'm guessing that there may be something in the sysmaster, but I don't know for sure and I don't know what it is if it is... TIA Rob ______________________________________________________________________ This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email ______________________________________________________________________

Rob, there's no timestamp which keeps track of the last time (or timestamp) a table was modified. Think about that... It would single queue the entire engine. Every session that modified any pages in a given table would have to queue up to gain access to the table's modtime stamp location in memory which would have to be protected by a latch. The only thing you can do, which is not practical, is to go into the syspaghdr table by the tables partnum and search for the page belonging to some extent of the table and find the one with the greatest timestamp in its header. Laborious and slow and out of date before the query is complete. Hmm, you could get a ballpark from the sysptnhdr record's timestamp (look up the page's ROWID, which is the table's partnum, in syspaghdr). That would change anytime one of the table's vital stats needs to change, like nrows. It might not clue you into updates, but it should get modified after an insert or delete. Hmmmm, throws a wrench into my theory about such activity single queueing updates, eh? Perhaps an ADM VP thread maintains the row count using a message passing protocol, don't know, but clearly since multi-session inserts scale very well in IDS, there's something going on. As to ALTER time, that's tougher. There's a version number buried in the table's TABLESPACE TABLESPACE record somewhere which is reported by oncheck -pT, but I don't know how to find it in sysmaster. Art S. Kagel ----- Original Message ----- From: Rob Saunders <rob@saunders.yourideal.co.uk> At: 7/16 5:06 > Can anyone tell me how I can determine if/when a table has been updated. > I'm trying to write a script that monitors certain tables in a > database and don't need to know what changes have been made, simply if a > table's data/schema has been modified. Is there any way to differentiate > between a schema update and a data update? I'm looking for something > comparable to the unix mtime and ctime stamps on a file's inode if that > helps anyone to identify with my needs... > > I'm guessing that there may be something in the sysmaster, but I don't > know for sure and I don't know what it is if it is... > > TIA > > Rob > > ______________________________________________________________________ > This email has been scanned by the MessageLabs Email Security System. > For more information please visit http://www.messagelabs.com/email > ______________________________________________________________________

The last date when a table was altered is in systables.created -- no finer resolution than the whole day. You'd need to check whether all changes modify that date; if you drop a not null constraint, for example, it does not involve a structural change on disk and might not trigger a change. Changing data is much harder, in general. I haven't poked all through sysmaster, but I'm not aware of anywhere that we keep the information. If we do keep it, it might be somehow related to the archive system timestamps rather than wall clock timestamps. -- Jonathan Leffler (jleffler@us.ibm.com) STSM, Informix Database Engineering, IBM Data Management 4100 Bohannon Drive, Menlo Park, CA 94025 Tel: +1 650-926-6921 Tie-Line: 630-6921 "I don't suffer from insanity; I enjoy every minute of it!" forum.subscriber@iiug.org wrote on 07/16/2004 01:10:46 AM: > Can anyone tell me how I can determine if/when a table has been updated. > I'm trying to write a script that monitors certain tables in a > database and don't need to know what changes have been made, simply if a > table's data/schema has been modified. Is there any way to differentiate > between a schema update and a data update? I'm looking for something > comparable to the unix mtime and ctime stamps on a file's inode if that > helps anyone to identify with my needs... > > I'm guessing that there may be something in the sysmaster, but I don't > know for sure and I don't know what it is if it is... > > TIA > > Rob > > ______________________________________________________________________ > This email has been scanned by the MessageLabs Email Security System. > For more information please visit http://www.messagelabs.com/email > ______________________________________________________________________ >