DB2 Stats vs Actual

Ever have a DB2 query that all of a sudden went from performing just fine to being dog slow? If so, there may be an issue with the stats on one or more of the tables being used in the query. To check, run the SQL below replacing XX and TABLE_NM with your appropriate schema and table in 2 places…

-- What DB2 thinks the table count is
SELECT  'Stats' AS TYPE_X,
        CARD AS COUNT_NB,
        STATS_TIME AS UPDATED_TS
FROM    SYSCAT.TABLES 
WHERE   TABSCHEMA = 'XX' 
        AND TABNAME ='TABLE_NM'
    UNION
-- Actual table count
SELECT  'Actual' AS TYPE_X,
        COUNT(*) AS COUNT_NB,
        CURRENT_TIMESTAMP AS UPDATED_TS
FROM    XX.TABLE_NM
FOR READ ONLY WITH UR

… if the record counts are way off, have your DBA do a runstats on the table in question followed by a rebind of any stored procs that use the table.

Leave a Reply