Informix System Tables

[NeedHelp2004]

Is there a way to query the last time a table has been modified or load with in an Informix database (version 7.2)?

Thanks

 

[vpshriyan]

Hi,

The following SQL statement returns user defined tablenames and their created or modified date whichever is very recent:

select tabname, created from systables where tabid>99 order by created desc;

Regards,
Shriyan

 

[NeedHelp2004]

Thanks, but this looks only to be a date in which the table structure has changed, which is one of the things I was looking for. But do you know of a system table that keeps track of the last time the data with in a table has been update, deleted, or modified?

 

[vpshriyan]

Hi,

Informix database server does not store information with regard to when a table has been inserted, updated, or deleted using DML SQL statements, as far I know. If it is a requirement at your application, I would suggest you to introduce an additional date or datetime column to store this information. Alternatively you may think of creating a separate master summary table to hold these information. The possible structure of a table may look like:

1. tablename varchar(64)
2. operation char(1) (I)nsert,(U)pdate,(D)elete
3. user name varchar(64)
4. effect_date datetime
so on ...

Regards,
Shriyan