Monitoring and Tuning Database Performance › Reducing I/O › By Using UPDATE STATISTICS (SQL-Accessed Databases)

By Using UPDATE STATISTICS (SQL-Accessed Databases)

When to Use UPDATE STATISTICS

Execute the UPDATE STATISTICS utility statement at the following times:

• Periodically (according to the needs of the application) to reflect shifts in the distribution of data in the database (for example, changes in owner/member ratios, area space utilization, index layout)
• After individual applications that alter the distribution of data; for example, monthly or year-end summary and offload processing

Use UPDATE STATISTICS on SQL-Defined Tables or Areas

Run UPDATE STATISTICS on individual tables or whole areas. The resulting statistics are stored in the SQL catalog and are used by the Access Module Compiler to generate optimal access strategies for SQL processing. Access modules that reference the tables whose statistics have been updated can then be recompiled to take advantage of the updated information. Table/access module cross-reference information on the catalog can be used to determine which access modules to recompile.

Use UPDATE STATISTICS on NON-SQL Schemas If They are Accessed by SQL

Run UPDATE STATISTICS on some or all areas defined in a non-SQL Schema. The resulting statistics are kept in the dictionary that defines the non-SQL schema. If the database is accessed by SQL the statistics will be used by the Access Module Compiler to generate optimal access strategies for SQL processing.

Restrictions on Statistics and Non-SQL Schemas

Non-SQL statistics are kept with the schema definition in the dictionary. This means statistics may be kept for only one physical database per schema. When processing an SQL command, only the current set of statistics will be used for that command regardless of the physical database being accessed by that command. The user must decide which physical database will provide the statistics that best meets their needs and run UPDATE STATISTICS against that database.