Check update statistics time in DB2®

Table and index statistics should be up to date to enable the database optimizer to choose optimal query plans. When in doubt, SQLs can be used to check the last update statistics collection time.

Symptoms

The column “stats_time” shows when statistics have been updated the last time. If this timestamp is not current (last week timeframe), then the statistics need to be updated. The following additional information is also retrieved:
  • in which tablespace is the table created (tbspace)
  • in which tablespace will indices be created (idx_tbspace)
  • cardinality (card)
respectively for indexes:
  • what are the columns which constitute the index (colnames)
  • cardinality (indcard)

For table statistics, you can use the following SQL:

SELECT substr(tabschema, 1, 10) AS schema_name
,substr(tabname, 1, 30) AS table_name
,substr(to_char(stats_time, 'YYYY-MM-DD HH24:Mi'), 1, 16) AS stats_time
,cast(card AS INT) AS no_of_rows
,statistics_profile AS profile_used
FROM syscat.tables
WHERE tabschema = <CURRENT_SCHEMA>
AND type = 'T'
ORDER BY no_of_rows DESC

For indexes, you can use the following SQL:

SELECT substr(indschema, 1, 18) AS schema_name
,substr(indname, 1, 25) AS index_name
,substr(to_char(stats_time, 'YYYY-MM-DD HH24:Mi'), 1, 16) AS STATS_TIME
,cast(numrids AS INT) AS no_of_rows
,rtrim(substr(colnames, 1, 140)) AS columns
FROM syscat.indexes
WHERE tabschema = <CURRENT_SCHEMA>
ORDER BY no_of_rows DESC;