A previous article (“DB2 UDB for z/OS Version 8: Performance Strategies,” z/Journal April/May 2006) described one common way DB2 DBAs and systems professionals tune DB2 for z/OS on a subsystem level. We called this resource constraint analysis because it’s based on an analysis of what resources are currently constrained and how that can be mitigated. This led to a DB2 systems tuning strategy to assist the DBA and systems programmer in developing the basic instincts necessary for supporting the IT enterprise.
This article will examine the other side of the equation. How can we design databases so they may be accessed in an efficient manner? Are there designs that permit the DBA more flexibility when tuning is being done?
We will pay specific attention to database design schemes and how they affect the way the DBA or systems professional tunes DB2 for z/OS subsystems. A follow-up article will take a look at another area: how application design affects DB2 system performance.
Resource Constraint Tuning
Consider your DB2 system (or data-sharing group) along with your applications as a single entity that consumes resources. Applications (including the DB2 subsystem code) use various combinations of resources during execution. These resources may include:
- CPU cycles
- Central storage (memory)
- Disk storage (I/O)
- Network (message travel time)
- Object access (locking, thread wait, enqueues)
- Application throughput (elapsed times, transactions per unit time).
A resource-based approach to DB2 system tuning concentrates on two aspects of the system:
- Resources as bottlenecks or constraints
- The way resource usage affects key system characteristics, including recoverability, performance, availability, security, and features and functions.
A resource-based approach uses the following procedure:
- Identify resource constraints
- Identify coincident resource availability
- Note critical system characteristics and issues
- Analyze 1, 2, and 3, looking for trade-offs.
By trade-offs we mean you’ll be looking to reduce or eliminate a resource constraint by re-directing the application to use a different resource. For example, you determine that you have a CPU constraint during your nightly batch window. Upon further analysis you note that many applications access a large partitioned table space using CPU parallelism. You can reduce the CPU constraint by inhibiting parallelism for those applications, although the applications may run longer.
Resource constraint analysis can occur at the object, application, or system levels. For example:
- Analyze critical tables and indexes. Consider them to be constraints. Develop ways of using excess CPU, DASD, or other resources to reduce contention.
- Analyze critical applications. Consider them to be constraints. Develop methods of using excess resources to increase application throughput.
- Analyze the DB2 subsystem. Determine the resource bottlenecks. Develop techniques for using excess resources to relieve the constraints.
The Impact of Database Design