Table Updates
Is there any info within Informix which documents table updates, as in the last time a table was changed, date time etc. John
John Harman wrote: > Is there any info within Informix which documents table updates, > as in the last time a table was changed, date time etc. Not table data updates; there is a column which records the date (but not the time) of the last change to the metadata (ALTER TABLE or similar), which is 'created' in the SysTables. It would be expensive to record the per-row information. -- Yours, Jonathan Leffler (Jonathan.Leffler@Informix.com) #include <disclaimer.h> Guardian of DBD::Informix v1.00.PC1 -- http://www.perl.com/CPAN "I don't suffer from insanity; I enjoy every minute of it!"
John Harman wrote:
> Is there any info within Informix which documents table updates,
> as in the last time a table was changed, date time etc.
>
> John
sysmaster:sysptntab has information of some sort. For example,
select b.tabname, a.skstamp
from sysmaster:sysptntab a, systables b
where skstamp > 0
and a.partnum = b.partnum
order by 2 desc;
will return you the "stamp" indicating when a table was last updated and,
will list tables with the most recently updated at the top. However, this
is an internal Informix stamp which can not be converted to GMT (at least,
I haven't been able to).
Compare the values to information in sysmaster:sysshmvals (sh_bootstamp,
sh_stamp, sh_curtime).
Also examine $INFORMIXDIR/etc/sysmaster.sql
Rudy
BTW, if you figure out way to convert skstamp or altstmp (when a table was
last altered) to GMT, please let us know.
With regards to converting Informix internal timestamps to GMT, no
direct conversion is possible as it seems that the increment of sh_stamp
is tied to database operations. So for a inactive database, sh_curtime
will increment a great deal faster (each second) than sh_stamp, vice
versa for an heavily active database.
sh_curtime is of course in localtime form (seconds since 1970) and so
can easily be converted to GMT, it is the first conversion that is
difficult.
The way I tackled the problem :-
1) decided the accuracy required for my conversion of sh_stamp to
sh_curtime (cannot do better than 1 second!)
2)
database mydb;
create table myshmvals (
sh_curtime integer not null,
sh_stamp integer not null);
create index a_1 on myshmvals(sh_stamp);
3)
vi insert_myshmvals.sql
database mydb;
insert into mydb:myshmvals
select sh_curtime, sh_stamp
from sysmaster:sysshmvals
4)
while true
do
dbaccess < insert_myshmvals
done
(leave this running forever)
5) use myshmvals, find the closest match (min value of myshmvals.sh_time
that is greater than the sought sh_time) for the sh_stamp value you are
after.
Caveats :-
* increases database activity by one insert per second;
* storage required for myshmvals - this table may need to be emptied
periodically;
* most likely problem is that sh_stamp will eventually "roll-over"
causing problems
Regards
Brett Randall
Rudy Fernandes wrote:
>
> John Harman wrote:
>
> > Is there any info within Informix which documents table updates,
> > as in the last time a table was changed, date time etc.
> >
> > John
>
> sysmaster:sysptntab has information of some sort. For example,
>
> select b.tabname, a.skstamp
> from sysmaster:sysptntab a, systables b
> where skstamp > 0
> and a.partnum = b.partnum
> order by 2 desc;>
> will return you the "stamp" indicating when a table was last updated and,
> will list tables with the most recently updated at the top. However, this
> is an internal Informix stamp which can not be converted to GMT (at least,
> I haven't been able to).
>
> Compare the values to information in sysmaster:sysshmvals (sh_bootstamp,
> sh_stamp, sh_curtime).
>
> Also examine $INFORMIXDIR/etc/sysmaster.sql
>
> Rudy
>
> BTW, if you figure out way to convert skstamp or altstmp (when a table was
> last altered) to GMT, please let us know.
Good thinking! Rudy Brett Randall wrote:...The way I tackled the problem :- > > 1) decided the accuracy required for my conversion of sh_stamp to > sh_curtime (cannot do better than 1 second!) > > ... > > 5) use myshmvals, find the closest match (min value of myshmvals.sh_time > that is greater than the sought sh_time) for the sh_stamp value you are > after. > > Caveats :- > > * increases database activity by one insert per second; > * storage required for myshmvals - this table may need to be emptied > periodically; > * most likely problem is that sh_stamp will eventually "roll-over" > causing problems > > Regards > > Brett Randall >