Re: Find last date updated using SQL

On 4/7/11 9:02 AM, Dave Boettcher wrote:

On a somewhat regular basis, we need to determine whether or not the
data in tables in a schema have been updated. There are about 100
tables in this schema and an update is run daily. Not all tables are
necessarily updated each day. I have been just doing a DSPFD and
scrolling down to the member list and looking at the last change
date and time for the member. This morning I thought there must be a
way to find this using the SYS* tables created when you create a
schema so that I could automate this checking process. I have looked
at QADBXREF and QADBIFLD but have only found when the table itself
was last changed, not when the data changed. So I ran DSPFD *MBRLIST
to an out file and of course it's there as last change date. But
isn't there a place on the system provided tables where it would be
also?

All help is appreciated.

As I recall there is a TABLE function which provides that detail in the "partition" [and a "table"] statistics catalog information, where that catalog information is retrieved by the table-function rather than stored; see SYSTSTAT, SYSTABLESTAT, SYSPSTAT, and SYSPARTITIONSTAT definitions. Encapsulating the change date\time retrieval in a UDF [retrieve member description (QUSRMBRD) API or RTVMBRD] or [both that UDF and a] UDTF might be an option if faster results [per limited-data] are desirable; the latter could be via DSPFD *MBR.

Using the catalogs versus a RYO user-defined [table] function, some variation on the following should suffice:

SELECT
SYSTEM_TABLE_NAME /* or SYS_TNAME */
, TABLE_NAME /* or TABNAME */
, SYSTEM_TABLE_MEMBER /* or SYS_MNAME */
, LAST_CHANGE_TIMESTAMP /* or LASTCHG */
FROM SYSTSTAT /* or SYSPSTAT for by-mbr */
WHERE TABLE_SCHEMA=:The_Library

http://publib.boulder.ibm.com/infocenter/iseries/v6r1m0/topic/db2/rbafzcatsyststat.htm
http://publib.boulder.ibm.com/infocenter/iseries/v6r1m0/topic/db2/rbafzcatsyspstat.htm

Regards, Chuck