Use REORGCHK to update table statistics

Abstract

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.

Contents


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

Special Notices

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.

Profile

Publish Date
04 February 2004

Last Update
08 February 2007


Rating:
(based on 5 reviews)


Author(s)

IBM Form Number
TIPS0339