DB2 database maintenance in WebSphere Commerce | Reorgchk and reorg


Runstats


+

Search Tips   |   Advanced Search

The RUNSTATS command will update the statistics that are used by the optimizer when determining access paths to the data. If the statistics are not up to date, the system will suffer performance degradation.

Most WebSphere Commerce sites update statistics on a daily or weekly basis (generally over the weekend). You should also consider updating the statistics after schema changes or massive update or load (such as refreshing the catalog). Remember to run db2rbind after RUNSTATS so that the static packages can take advantage of the new statistics.

The following SQL statements can be used to identify when the statistics were last updated for all the tables and indexes on the database: 

db2 "select tabschema, tabname, stats_time from syscat.tables order by stats_time asc"
db2 "select indschema, indname, tabschema, tabname, colnames, stats_time from syscat.indexes order by stats_time asc"

You might be able to get some output

Example 25-1 Sample database statistics output 

DB2INST1 CATENTSHIP 2007-07-11-19.27.18.034540
DB2INST1 CATENTTYPE 2007-07-11-19.27.18.120666
DB2INST1 CATGPCALCD 2007-07-11-19.27.18.185215
DB2INST1 CATGPENREL 2007-07-11-19.27.19.254761
DB2INST1 CATGROUP 2007-07-11-19.27.19.359756
DB2INST1 CATGRPATTR 2007-07-11-19.27.19.554302
DB2INST1 CATGRPDESC 2007-07-11-19.27.19.631565

Depending on the size of your database, you might need to use a finer- grained method for updating statistics, such as profiling or sampling. For more information, guidelines, and examples, see the topic on the RUNSTATS command in the DB2 Information Center

Or you can refer to the article titled "RUNSTATS in DB2 Universal Database, Version 8.2" on the developerWorks Web site:

In general, you should perform RUNSTATS on tables and indexes in the following situations: