DB2: When were index stats last updated on a table?

To find out when stats were updated for an index, run the following SQL substituting your schema and table names…

SELECT  IND.TABSCHEMA,
        IND.TABNAME,
        IND.INDNAME,
        IND.COLNAMES,
        IND.STATS_TIME AS IDX_STATS_TIME, 
        T.STATS_TIME TBL_STATS_TIME, 
        CASE T.VOLATILE
            WHEN 'C' THEN 'YES'
            ELSE T.VOLATILE
        END AS TBL_VOLATILE,
        T.OWNERTYPE AS TBL_OWNERTYPE,
        T.TYPE AS TBL_TYPE
FROM    SYSCAT.INDEXES IND 
            LEFT OUTER JOIN SYSCAT.TABLES T
                ON IND.TABSCHEMA = T.TABSCHEMA
                    AND IND.TABNAME = T.TABNAME
WHERE   T.OWNERTYPE != 'S'
        AND IND.TABNAME = 'MY_TABLE' 
        AND IND.TABSCHEMA = 'XX' 
ORDER BY 
        IND.TABSCHEMA,
        IND.TABNAME,
        IND.INDNAME 
FOR READ ONLY WITH UR;
Please remember to subscribe to the newsletter to stay up to date!

You or someone you know looking to buy or sell?
Disclaimer: Thoughts and opinions are my own, and do not reflect the views of any employer, family member, friend, or anyone else. Some links may be affiliate links, but I don't link to anything I don't use myself. You would think this should be self evident these days, but apparently not...

Leave a Reply