Re: Find last date updated using SQL

[CRPence <CRPbottle@xxxxxxxxx>]

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