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.
- 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.
- 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)
update sysstat.tables
set
card = 10000,
npages = 1000,
fpages = 1000,
overflow = 2
where tabschema = 'MELNYK'
and tabname = 'EMPLOYEE'
- 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.