The Shifting Sands of DB2 Performance Tuning

6 Pages

There’s a revolution coming—a major shift in the way we approach DB2 performance tuning. Industry forces are at work shaping the battleground and context surrounding performance tuning. Highly experienced DB2 technical support personnel need to be aware of this shift, as it will transform the way they approach system capacity planning and performance management.

Paradigm Shifts in IT Support

Among the many challenges facing the technical support professional today are fundamental changes in our standard practices. These involve performance management tools, autonomic software, and outsourcing.

Performance Management Tools

Many software vendors sell tools that provide a great deal of assistance in DB2 performance tuning. These include advice on query explains, index creation, query re-write, buffer pool sizing and threshold management, schema change management, and many others. As more tools arrive and as current tools mature and become easier to use, tool use shifts from highly experienced technical staff, such as DBAs and systems programmers, to the less experienced.

A good example of this is the new Optimization Service Center to become available with DB2 for z/OS V9. This is a DBA tool suite for tuning and managing SQL queries and includes the stats advisor, index advisor, query re-write advisor, query workload monitor, resource estimator, query formatter, visual explain, visual plan hint, IBM service doc generator, and partitioning/ clustering advisor. To learn more, visit the IBM DB2 product information Website at www-306.ibm.com/software/ data/db2/zos/.

The result: Management assigns basic DB2 performance tuning to staff they perceive to be more cost-effective. Junior staff can use tools effectively to perform basic DB2 performance tuning, and senior staff will be assigned less of this work.

Self-Healing, Self-Tuning Systems

Autonomics is the relatively new concept of a self-healing, self-tuning system. (To learn more, visit the DB2 DeveloperWorks Autonomic computing Website at www.ibm.com/developerworks/ autonomic/. Also, see A First Look at Solution Installation for Autonomic Computing, SG24-7099-00.) Various features and functions of DB2 can be considered autonomic. For example, DB2 will monitor a query’s access to physical data. If the accesses are mostly sequentially forward through the object (table or index), then DB2 may turn on a feature called dynamic prefetch. Another example is the ability of the reorg utility to execute based on data clustering and distribution statistics. As new versions of DB2 appear, the product contains more autonomic features. For example, there’s the DB2 Health Monitor that “provides out-ofthe- box monitoring of key DB2 performance and reliability metrics and offers expert advice on resolving any problems that may occur.” (To learn more, visit the DB2 UDB Autonomics Web Page at www.ibm.com/autonomic/db2.shtml.) There also is the DB2 configuration advisor that “allows even a novice user to achieve a configuration that previously would have taken an expert days to accomplish.”

One of the best examples of an autonomic feature is real-time statistics. This feature (introduced in DB2 for z/OS Version 7) lets DB2 gather necessary information users or automated task schedulers can use to determine which objects require reorg, runstats, or copy. These statistics are always generated real-time and in memory, and they can be externalized to DB2 tables when necessary. (See the DB2 Administration Guide for the latest information.) The result: Novice technical support staff can now support basic and intermediate DB2 application and system performance tuning projects.

6 Pages