DB2 Version 9.7 for Linux, UNIX, and Windows

Catalog statistics for modeling and what-if planning

You can observe the effect on database performance of changes to certain statistical information in the system catalog for planning purposes.

The ability to update selected system catalog statistics enables you to:

Model query performance on a development system using production system statistics
Perform "what-if" query performance analysis

Do not manually update statistics on a production system. Otherwise, the optimizer might not choose the best access plan for production queries that contain dynamic SQL or XQuery statements.

To modify statistics for tables and indexes and their components, you must have explicit DBADM authority for the database. Users holding DATAACCESS authority can execute UPDATE statements against views that are defined in the SYSSTAT schema to change values in these statistical columns.

Users without DATAACCESS authority can see only rows that contain statistics for objects on which they have CONTROL privilege. If you do not have DATAACCESS authority, you can change statistics for individual database objects if you hold the following privileges on each object:

Explicit CONTROL privilege on tables. You can also update statistics for columns and indexes on these tables.
Explicit CONTROL privilege on nicknames in a federated database system. You can also update statistics for columns and indexes on these nicknames. Note that these updates only affect local metadata (datasource table statistics are not changed), and only affect the global access strategy that is generated by the DB2® optimizer.
Ownership of user-defined functions (UDFs)

The following code is an example of updating statistics for the EMPLOYEE table:

   update sysstat.tables
     set
       card = 10000,
       npages = 1000,
       fpages = 1000,
       overflow = 2
     where tabschema = 'MELNYK'
       and tabname = 'EMPLOYEE'

Care must be taken when manually updating catalog statistics. Arbitrary changes can seriously alter the performance of subsequent queries. You can use any of the following methods to return the statistics on your development system to a consistent state:

Roll back the unit of work in which your manual changes were made (assuming that the unit of work has not yet been committed).
Use the runstats utility to refresh the catalog statistics.
Update the catalog statistics to specify that statistics have not been collected; for example, setting the NPAGES column value to -1 indicates that this statistic has not been collected.
Undo the changes that you made. This method is possible only if you used the db2look command to capture the statistics before you made any changes.

If it determines that some value or combination of values is not valid, the optimizer will use default values and return a warning. This is quite rare, however, because most validation is performed when the statistics are updated.