Use REORGCHK to update table statistics
Published 04 February 2004, updated 08 February 2007
Authors: Whei-Jen Chen
When you update table statistics using the RUNSTATS command, it requires that you run the command against each table, one by one. REORGCHK gives you the capability to update the statistics of a group of tables or all the tables in the database, with only one command.
DB2 UDB for LUW uses the statistics information in the catalog table to derive the best access plan. The DBA should regularly run the RUNSTATS command to keep the database statistics updated. This procedure will ensure the best performance on queries.
If you want to update statistics on all the tables or a group of tables in the database, you can use the REORGCHK command with the UPDATE STATISTICS option. This would be run instead of running the RUNSTATS command against each table. The UPDATE STATISTICS option will first call the RUNSTATS routine to update the table statistic. You can then use these new statistics to determine if a reorg is needed. Note that RUNSTATS generated by the REORGCHK UPDATES STATISTICS ... command collects statistic on the table only without distribution statistics. To have distributed statistics collected, you have to use RUNSTATS command.
The REORGCHK command allows you to update statistics on a group of tables with one command.
To update all the user and system tables use:
REORGCHK UPDATE STATISTICS ON TABLE ALL
To update all the tables of a particular schema use:
REORGCHK UPDATE STATISTICS on SCHEMA schema_name
To update all the tables of a particular table system use:
REORGCHK UPDATE STATISTICS on TABLE SYSTEM
This material has not been submitted to any formal IBM test and is published AS IS. It has not been the subject of rigorous review. IBM assumes no responsibility for its accuracy or completeness. The use of this information or the implementation of any of these techniques is a client responsibility and depends upon the client's ability to evaluate and integrate them into the client's operational environment.
Follow IBM Redbooks
Follow IBM Redbooks