Re: Find last date updated using SQL

Thanks for all the responses. As Jim has said all counties in Wisconsin at least can use these same tables and, yes, it is a case of what did the state do this time. Their DBA never seems to tell anyone else we deal with when DB changes are made.
To describe what we have done, since we originally created a schema for these tables, they are automatically journaled. That becomes the tip-off that the state changed something again. I run a scheduled job each day that uses the DSPFD method and extracts the information on whether the table is journaled. If it is, I know that the table was re-created by the nightly replication and I end journaling because of another comment that the receivers get very large. I can also then print a cross-reference list of all of our programs that use that table to check them if the database change made a difference to us.
The situation that prompted my inquiry the other day is that sometimes the replication seems to fail and the PC programmers that use the tables notice that their reports aren't changing. They call me and we usually have to drop and re-create the whole schema. I thought there should be a way of getting the last update information using the catalogs that SQL uses.
Chuck gave me a statement that he said used a table function. His e-mail doesn't seem to have the same thread and I can't even find it again as a possible follow-up. This is that statement that finally worked out:
SELECT SYSTEM_TABLE_NAME /* or SYS_TNAME */ , cast(TABLE_NAME as
char(25)) as tablename /* or TABNAME */ , LAST_CHANGE_TIMESTAMP /*
or LASTCHG */ FROM SYSTSTAT /* or SYSPSTAT for by-mbr */ WHERE
TABLE_SCHEMA='WISACWIS'
Chuck also said there were SYSTSTAT, SYSTABLESTAT, SYSPSTAT and SYSPARTITIONSTAT table definitions somewhere. I haven't looked them up yet, or implemented this statement in an automatic process.
It does make it easier to check on updates than manually looking at 108 DSFPD *mbrlist when it comes up. When I get a chance, I intend to automate it.
Thank you all for your help.

Thanks,

Dave B

Two rules to eliminate stress:
1. Don't sweat the small stuff. 2. It's all small stuff.


________________________________
The information in this e-mail message (including any information contained in attachments hereto) is intended only for use of the addressee. This e-mail message may contain confidential or privileged information. If you receive this e-mail message unintentionally, please notify the sender immediately and then delete this message. E-mail transmission is not guaranteed to be secured or error free. The sender is not liable for any errors or omissions in the content of this e-mail message, which may arise as a result of e-mail transmission. E-mails, text messages, and other electronic communications made or received in connection with the transaction of public business, the expenditure of public funds, or the administration of public property are subject to the Wisconsin Public Records Law.