October 17, 2012

Find large Maximo tables on DB2

I this article I have explained how to list the larger database tables and indexes on Oracle.
On DB2 you can use the following one.

SELECT name, card, npages, fpages, stats_time
FROM sysibm.systables
WHERE creator = 'MAXIMO' 
AND type='T'
ORDER BY card DESC;

The returned columns are:
  • NAME: Name of the table
  • CARD: Number of rows
  • NPAGES: Total number of pages on which the rows of the table exist
  • FPAGES: Total number of pages
  • STATS_TIME: Date when the statistics of the table were collected

The STATS_TIME is important both to understand if statistics are updated in your database (which is important for performances) and for check if the data retrieved is updatad. If timestamps here are too old you should regenerate database statistics. You can do this selecting Update Statistics action from the Database Configuration application menu.

1 comment:

  1. Hi Bruno, I am executing Update Statistics action from the Database Configuration application menu.
    Please let me know,
    1. Where can we check the logs or the process of update statistics.
    How can we know update statistic is finished.
    2. Normally how much tie it will take to update the statistics for a database size of 6 gb.
    3. Database we are using is db2 and maximo 7.5 version.

    ReplyDelete