The DB2 Performance Advisor – What is Performance

Assuring optimal application and system performance is one of the most important goals of IT professionals. And for DB2 developers and DBAs, tuning and monitoring are often daily tasks. But there are many factors involved in DB2 optimization and performance monitoring and tuning … too many to do justice to in a single article. And that is why we are introducing this regular column: The DB2 Performance Advisor.  This column will appear regularly on the Planet Mainframe blog, and will cover all facets of performance management regarding DB2 for z/OS.

Even in today’s modern IT environment, performance management is often performed reactively instead of proactively. You know the drill. A user calls with a response time problem. A table space maxes out on extents. A program is running without taking commits causing all kinds of locking problems. Somebody rebound an application package without checking on the new access paths and transactions are piling up.  And then somebody submitted that “query from hell” again that just won’t stop running. Sound familiar? All too often DBAs are being asked to respond reactively to problems that could have been prevented, if only we implemented more proactive performance management steps. So we must routinely and reactively put out the performance fires.

Yet management and consultants continue to promote the benefits of proactive management. And, of course, they are correct. Being proactive can reduce, or even eliminate many of the problems that DBAs deal with on a daily basis.

However, many of the supposedly proactive steps taken against completed applications in production are mostly reactive. Let’s face it, DBAs are often too busy taking care of the day-to-day tactical database administration tasks to proactively monitor and tune their systems to the degree they wish they could.  Setting up a proactive performance infrastructure takes time and effort, and time is not something that DBAs have in abundance as they tackle the reactive tasks that are required to keep things up and running.

Of course, performance tools and monitors are available that can make performance management easier by automatically taking predefined actions when specified alerts are triggered. Additional tools exist that can analyze problems and suggest solutions. But before you tackle performance problems you’d be wise to set up service level agreements (SLAs) that define agreed-upon performance criteria. To be effective, a service level agreement must specify what is being serviced, the response time or availability required, who the service is being delivered to, who is responsible for assuring the service level, and the budget being allocated to enable the service. All too often, SLAs are either incomplete or worse, not created at all.

We’ll tackle all of these issues and more in future installments of The DB2 Performance Advisor. But for today, first let’s define what we mean by “DB2 performance.”

Defining DB2 Performance

Before we can talk about assuring performance we first need a good definition of the term. Think, for a moment, of DB2 performance using the familiar concepts of supply and demand. End users demand information from DB2. DB2 supplies information to those requesting it. The rate at which DB2 supplies the demand for information can be termed DB2 performance.

Five factors influence DB2’s performance: workload, throughput, resources, optimization, and contention.

1. The workload that is requested of DB2 defines the demand. It is a combination of online transactions, web requests, batch jobs, ad hoc queries, data warehousing analysis, utility jobs, and DB2 commands directed through the system at any given time. Workload can fluctuate drastically from day to day, hour to hour, and even minute to minute. Sometimes workload is predictable such as heavy month-end processing of payroll, or very light access after 5:30 p.m. and before the nightly batch cycle when most users have left for the day, but at other times it can be unpredictable. And for web-based applications it can fluctuate based on any number of factors. When applications rely on dynamic SQL, as most modern DB2 applications do, the access path can change each time the SQL is run. This too, impacts predictability. So keep in mind that the overall workload has a major impact on DB2 performance.
2. Throughput defines the overall capability of the computer to process data. It is a composite of the mainframe model, CPU speed, I/O speed, specialty processor involvement, any additional coprocessors involved, parallel capabilities of the hardware and software, and the efficiency of the operating system and system software.
3. The hardware and software tools at the disposal of the system are known as the resources of the system. Examples include memory such as that allocated to buffer pools, sorting or address spaces, disk subsystems, cache controllers, microcode, and so on.
4. The fourth defining element of DB2 performance is optimization. All types of systems can be optimized, but relational database systems such as DB2 are unique in that query optimization is primarily accomplished internal to the DBMS. Nevertheless, there are many other factors that need to be optimized that can include:
   • The formulation of your SQL statements
   • Host language application code (e.g. Java, COBOL, etc.)
   • DDL parameters for your database objects
   • System parameters (DSNZPARMs)
   • JCL parameters
   • Storage subsystem configuration and setup
   • Network settings and traffic
   • Other system software parameters/integration (CICS, IMS, DFSMS, MQ, etc.)
   • DB2 optimizer hints
5. When the demand (workload) for a particular resource is high, contention can result. Contention is the condition in which two or more components of the workload are attempting to use a single resource in a conflicting way; for example, dual updates to the same piece of data. DB2’s lock manager ensures that data is not modified inappropriately, but contention will cause portions of the workload to be paused until the competing workload components have completed. Simply stated, as contention increases, throughput decreases.

So, with all of that in mind, let’s put the pieces together and define what we mean by the term “DB2 performance.”

Definition: DB2 performance can be defined as the optimization of resource use to increase throughput and minimize contention, enabling the largest possible workload to be processed.

Even so, we must always remember that DB2 applications regularly communicate with other System z subsystems, which must also be factored into performance planning. An inefficient z/OS setting, increased network traffic, or even an out-of-date CICS parameter can influence DB2 performance.

Of course, we cannot all become experts in every facet of mainframe administration and operations. Nevertheless, it is a wise course of action to work towards increasing your understanding of any resource that is used by, or works with, DB2. So, at times, we will examine how other mainframe components can impact DB2 performance in this column.

A Few General Themes

Now that we have a definition of DB2 performance, we will introduce a few general themes that will permeate your career as you work to improve and manage the performance of your DB2 systems and applications. These themes will not be in-depth technical tuning tips, but rather guiding principles to keep in mind as you work with DB2.

1. The first basic guideline is to understand the 80/20 rule, sometimes known as the Pareto Principle. The 80/20 rule can be stated in several different ways. Perhaps the most common one is that 80% of your performance tuning results will come from 20% of your effort. Or stated another way, 20% of your DB2 applications will cause 80% of your problems. The general idea here is that you should focus your tuning efforts on the applications and processes that will provide the biggest return on investment.
2. The second basic guideline is that you should approach tuning as an iterative process. In other words, tune one thing at a time, not several. And after each tuning step measure the success or failure of the attempt. If you do not approach tuning in this manner then you may be introducing sub-optimal settings and code because you failed to measure each change. So always change only one thing at a time and gauge the results before doing anything else.
3. Thirdly, you should try to avoid using the words “always” and “never.” There are rarely any rules that always apply. And likewise, it is just as rare that there is something that can be done, that should never be done. Keep an open mind.

Finally, it is better to design performance into your application programs and system from the start, instead of trying to retrofit performance tweaks later. The further into the development process you are, the more painful it becomes to makes changes.

Summary

Now that we have a definition of DB2 performance—and some guiding performance management principles—we can use them to dive into related issues and topics in subsequent editions of The DB2 Performance Advisor. I hope you’ll join me here on Planet Mainframe for the discussion.