Update all database statistics with a single command

Posted by David Yahalom under DB2 LUW

As with all RDBMS implementation, DB2 UDB uses the statistics information in the catalog table to derive the best execution plan. We, as DBAs, should regularly run the RUNSTATS command to keep our database statistics updated for optimal query performance.

RUNSTATS ON TABLE SCHEMA_NAME.TABLE_NAME

Runstats works as advertised but what happen when you need to quickly update statistics for a large group of tables at once? Allot of DBAs (including myself) would write a script that creates another script, a “RUNSTATS ON TABLE_NAME” script, with the table name concated. This will, of course, work, but there is a simpler and better way to do this in DB2 using the reorgchk command.

To update stats for the entire database use:

REORGCHK UPDATE STATISTICS on TABLE ALL

To update all the tables of a particular schema use:

REORGCHK UPDATE STATISTICS on SCHEMA schema_name

You should note, however, that the RUNSTATS command generated by the REORGCHK UPDATES STATISTICS command collects statistic on the table only without distribution. To have distributed statistics on your tables you should use the RUNSTATS command instead.