Using what tools: For object tuning, there are few no-cost tools. Some software vendors provide “advisors” that make recommendations on objects, and some data modeling tools can be configured to generate a Data Definition Language (DDL) for objects that follow certain standards. After that, the DBA is probably reduced to using whatever tools (or raw SQL statements) are available that can query the DB2 catalog tables to get object definitions.
Where to look for the largest productivity gains: Few specific tactics apply to all IT environments. Much depends on your standards and how consistently they’ve been applied over time. Many object-related tuning issues can be traced to one or more of the following:
• Poor choice of clustering index. Often, the data architect must create a database design without knowledge of the SQL to be used against the tables. The result is indexes that support only the primary key and uniqueness constraints, without any consideration for query performance. Tables with a single index are considered to be clustered on the keys of that index, with some exceptions. Clustering by primary key isn’t always the best design for performance. To learn more, see the “Performance Monitoring and Tuning” chapters in the DB2 Administration Guide.
• Poor choice of table space partitioning scheme. Many data architects don’t consider physical partitioning schemes because they’re physical, not logical. However, physical partitioning can be used in many instances to increase performance.
A few examples:
1) Partition by date can make purging of old or unneeded data a simple matter of emptying a partition rather than executing a long row-byrow purge process; 2) Utility processing, where reorgs and recovery can occur on a partition basis; and 3) Partition by one key, cluster by another; this is table-based partitioning, introduced in DB2 V8 (see the manuals for specifics).
• Poor choice of indexes to support queries. Often, the data architect or DBA will create indexes they feel will provide good performance. However, without specific measurements of what indexes are used and how often, it’s common to end up with either too many indexes unrelated to specific performance requirements or unused indexes.
• Lack of resource consumption and resource cost metrics. DBAs sometimes spend time and resources tuning something that ends up “better” but with no way to show that they tuned the right thing.
First steps: Along with establishing clear standards for object creation and consistently applying them, the most important steps are to research and generate a cross-reference of objects accessed by plan and package, including type of access. This is commonly called a CRUD (Create, Read, Update, and Delete) matrix or, more properly, a data-to-process CRUD matrix. The CRUD matrix allows a multi-dimensional analysis of object access. One can analyze the set of processes that access one or more objects, or the objects accessed by one or more processes. This allows the analyst to understand possible points of contention and pinpoint objects whose allocation and configuration should be considered in more detail.
Systems tuning is more enterprise-based than application-based. It involves coordinating installation, configuration, and tuning of DB2 subsystems, hardware and devices, and the interface with z/OS. It also requires knowledge of some relatively complex reports, including RMF reports. A new DBA would be unlikely to be productive in this area because the work requires considerable training and experience.
What you analyze: TCP/IP, virtual pools, logging, recovery, DB2 maintenance, DB2 configuration parameters (ZParms), the Internal Resource Lock Manager (IRLM), and the z/OS interface. Additional information is available from traces that can be turned on in the DB2 environment.
Using what tools: System-related performance reports from SMF records, RMF reports, and System Modification Program/Extended (SMP/E) reports.
First steps: Disaster recovery planning, recovery standards and jobs, software maintenance strategy.
Where to Begin
No matter what areas of tuning you decide to begin with, one thing is certain— you’ll have a lot of work to do! To reduce your workload, concentrate on automating any reporting or analysis processes. A good place to begin is to set up, review, and automate your “Top-n” reports.
Choose the report or reports relevant to your tuning area and implement a regularly scheduled process to produce the reports. Schedule regular meetings with the appropriate people to review the results
For example, if you’re concentrating on SQL tuning, set up regular reports of your Top-n SQL statements. Choose your criteria for reporting: Total elapsed time, total CPU time, and total synchronous I/O wait time are common items. Run these reports daily or weekly and review them with your peers or with leaders in application areas.
Regularly run and review available health checks or develop your own. Develop plans to address issues when they arise.
Even the most frugal shops have helpful tools. Discover what IBM and third-party tools exist and determine their utility for your needs. Pick what looks like the most useful tool and learn it.