AIX > Administrator > DB2

Data Basics, Part 2

More tips for avoiding common DB2 performance mistakes


In October, we outlined five common mistakes that can impede DB2* performance. This article rounds out our top 10 by taking you through five additional errors that database administrators (DBAs) often make in DB2 installations.

 

Performance Problem 6:Missing or Out-of-Date Statistics
DB2 comes with an advanced optimizer. In fact, IBM invented and implemented cost-based query optimization in SystemR more than 25 years ago. DB2 can optimize an entire query and consider actions (e.g., query rewrite, predicate pushdown and branch-tree elimination) based on the current environment (such as CPU and memory) and defined business logic (e.g., constraints, triggers and referential integrity).

When optimizing SQL queries, calculations made by the SQL compiler are heavily influenced by the optimizer's model of the database contents. The optimizer uses this data model to estimate the costs of alternative access paths that it could use to resolve a particular query.

A key element in the data model is the set of statistics gathered about the data. If these statistics, which are stored in the system catalog tables, aren't kept current, long-running queries, "curious" access plans or other problems could ensue.

Statistics can be collected and updated during a load operation or by using the RUNSTATS utility. RUNSTATS can be executed for both tables and indexes, or solely for one or the other, such as in a case where you create a new index after running the utility and don't want to recollect statistics on the table data.

We recommend using RUNSTATS in these circumstances:

  • When a table is loaded with data, and the appropriate indexes are created
  • When a table is reorganized with the REORG utility (the REORGCHK utility can run RUNSTATS for all tables)
  • When there's a 10-20 percent change that affects a table and its indexes
  • Before binding application programs whose performance is critical
  • When the prefetch quantity is changed

 

 

While the DB2 optimizer is powerful, it can only start with what you give it. SQL is a powerful language, so use it. Queries can be fashioned in many ways, so you should follow up on them.

Berni Schiefer is a Distinguished Engineer and manager of the DB2 UDB Performance and Advanced Technology team. Berni can be reached at schiefer@ca.ibm.com.

Paul C. Zikopoulos is a database specialist IBM. Paul can be reached at paulz@ca.ibm.com.


comments powered by Disqus

Advertisement

Advertisement

2019 Solutions Edition

A Comprehensive Online Buyer's Guide to Solutions, Services and Education.

Data Basics, Part 2

More tips for avoiding common DB2 performance mistakes

IBM Systems Magazine Subscribe Box Read Now Link Subscribe Now Link iPad App Google Play Store
IBMi News Sign Up Today! Past News Letters