[DB2] Find tables that recently modified structure in DB2

Problems

• I want to find tables that recently modified structure in DB2

Solution

• Query for View SYSCAT.TABLES Column ALTER_TIME which the object was last altered.
• Full Query which
  - filter Type Table (T)
  - alter time in last 30 days
  - filter out DB2 System Table (SYS%)

SELECT TABSCHEMA   AS SCHEMA_NAME,
       TABNAME     AS TABLE_NAME,
       ALTER_TIME  AS MODIFY_DATE
FROM SYSCAT.TABLES
WHERE TYPE IN ('T')
      AND ALTER_TIME > CURRENT DATE - 15 DAYS
      AND TABSCHEMA NOT LIKE 'SYS%'
ORDER BY ALTER_TIME DESC;

Note: Other interested columns are

• CREATE_TIME - Time at which the object was created.
• INVALIDATE_TIME - Time at which the object was last invalidated.
• STATS_TIME - Time at which any change was last made to recorded statistics for this object. The null value if statistics are not collected.
• REFRESH_TIME - Time at which the data was last refreshed from REFRESH TABLE statement
• LAST_REGEN_TIME - Time at which any views or check constraints on the table were last regenerated.

Reference

• SYSCAT.TABLES catalog view - IBM Documentation