DB2 High Performance Design and Tuning - Hardcover

What's the one thing I can do to fix the performance problems
in my DB2 systems?

DB2 came, saw, and conquered on the mainframes or enterprise servers, better known today as the S/390. Just take a look at the some of the enormous database sizes and heavy workloads that DB2 is currently supporting in production. Here are a few examples: a single database table of six billion rows, an online system achieving over 1,200 transactions per second, batch systems exceeding 80 million update transactions per day and turning out approximately 1.3 million reports daily, and insert programs pushing 300+ inserts per day. These database systems are in existence today and are growing and pushing the envelope in new ways. These numbers may seem astonishing to some, but they show the capabilities of the DB2 engine and set the stage for larger databases and more complex applications. Take, for example, a major service firm that is in the planning stages of creating a 130-billion-row table, which we like to call a VLTB (very large table), to be implemented in the near future. These figures will probably be exceeded greatly by the time you read this material. Even with all these success stories and reports of the amazing accomplishments of these large-database and high-transaction-volume systems, many organizations are still failing to meet their service-level agreements. Why? It is not DB2 that has failed at these sites, but the design and implementation of the applications being implemented in DB2 and the care and feeding of the subsystem and its surroundings. To build a system of large magnitude such as those mentioned earlier, organizations are faced with several new challenges. Many of these challenges need to be met head on with knowledge and expertise that often is lacking, making this task virtually impossible.

Technical specialists such as database administrators, database designers, system programmers, and application programmers often are expected to perform miracles and are all too often limited in the resources available to accomplish the difficult task at hand. "Develop a large database with several large tables with no outages allowed, to support a complex application with several thousand SQL statements, running millions of transactions per day with excellent response time. By the way, we have no time for additional education, design reviews, or SQL performance tuning because we have a deadline to meet." Sound familiar? Many organizations have found that it is very easy to implement DB2 applications, which is one of the best features of the product, but without careful planning and attention to performance, these easy-to-implement applications can soon become performance nightmares. This fact could be best emphasized with a quote from IBM lead DB2 strategist Roger Miller: "The rule is not to bite the hand that you expect to feed you."

Many organizations have had their share of experience with poorly performing applications in the past, and the reason is probably that it is easy to implement a database application poorly when critical phases of system development are rushed or skipped altogether. No one goes into systems development with the goal of developing a poorly performing application. Often, however, there are design, development, and programming standards and practices in place that seem to force poor performance due to a lack of understanding about all the elements that affect performance in DB2. Even worse is the adherence to old performance myths that have been propagated through the years. This book is designed both to try to destroy the bad practices and to direct focus on more efficient and effective ways of viewing performance in DB2. DB2 works exactly the way it is directed, good or bad. The good news is that it is a proven fact that we can accomplish amazing feats with DB2 if we understand how it works and what is needed to make it perform. Purpose of this Book

The title includes the words "high performance." Today, there are many books, papers, and especially IBM manuals that contain mountains of material on performance. But when technical people are faced with the development of a high-performance system, who has time to climb those mountains? As a result, many systems achieve what is deemed as acceptable performance based upon the best efforts of the technical staff with the resources available to them. The problem of less-than-optimal performance has existed in the past, but something is happening today to change the perspective of what is acceptable: the "e" stuff and to a lesser degree the "BI" stuff. For example, one company was very happy with its data-sharing complex's achieving 1,000 transactions per second, but the movement to support web-based clients changed all that. In a traditional application, a terminal operator may place an order using one or more transactions. But when an order is placed from the web, it may spawn 10 of those traditional back-end transactions. Prior to the web phenomenon, there were a fixed number of terminal operators, but the web opens up an unlimited number of users issuing transactions to place orders. What was acceptable performance in the past is now simply inadequate because you are not just dealing with your internal company personnel (who may have not complained or were ignored about system response time) taking orders. The rules have changed; with e-business you open the doors of your company to the world. You might say that you expose yourself, and any performance problems you had in the past will now be magnified.

So what can be done to take an existing application to a level far above what is achieved today, and how do we develop a new application that can perform at or above expectations? Too often this question is answered by adding additional CPUs. Sure, that makes vendors happy, but is that really how to address performance problems? This is short-term, quick fix, but it often is not the answer because the dollar costs related to this solution are often too high, and in the long run the application may not be scalable because the underlying problems have not been addressed.

What needs to be done is to remove code length at all of the 50 or more pressure points within the DB2 environment and within the application. It is the task of the person responsible for performance to find these key points and determine the best way to improve performance, through either subsystem tuning, database tuning, or application tuning. It is truly amazing what can be done by identifying a few key points and tuning them, and the benefits gained by such efforts are definitely worth it. We have seen simple system changes increase the number of concurrent threads by a factor of 4. SQL tuning dropped an application from 400 hours a month to less than 7. While some areas may seem miniscule by themselves, it is the combined benefits that will provide your database systems with optimal performance. By performing several small changes, it is possible for a 100-transaction-persecond OLTP application to become a 400-transaction-per-second OLTP application on the same hardware and operating environment. The challenge lies with the "bloodhound" skills to find the performance problems and the proper knowledge and resources to fix them. There are also many problems that can be fixed quite easily, bringing large performance gains immediately.

This book focuses on identifying these key pressure points, determining what is necessary to improve them, and taking a little code length out of each one. Suppose that you could remove 0.10 CPU seconds from 40 different pressure points. That equates to 4 CPU seconds for allowing more transactions to complete. Since the average OLTP system handles between 8,000 and 12,000 SQL statements per second, it is easy to figure out just how significant a series of minor changes can be. Add this type of change to other tuning efforts, and the results of the overall combined effort can be truly amazing.

Keep in mind there is no "silver bullet" in DB2 performance. Optimal performance comes from a combination of proper design, subsystem implementation, application coding, and customizing DB2 for your organization's specific requirements.

Maximum performance cannot be achieved without a full understanding of how each component interrelates with the others, such as how a simple SQL statement can use or abuse all the resources of the system and take literally hours to run but, with a minor change, take fewer resources and complete in seconds. Every design issue in DB2 is a trade-off. DB2 must be viewed as a delicate balance of issues and techniques, where pushing down on any single point increases pressure on another point somewhere else. High-performance design and tuning require a delicate balance, and therefore each balance/counterbalance situation will be discussed.-It is important that all technical support personnel understand how and why these trade-offs occur and, more important, what they can do in their particular area to achieve high performance.

There are many rules of thumb and guidelines for DB2

RICHARD YEVICH is an internationally recognized consultant and teacher and principal of YL&A, Inc., a firm specializing in consulting, performance audits, performance publications, and Web database software development which assists businesses in exploiting e-commerce technologies. An IBM Gold Consultant for DB2 and S/390, he co-authored Data Warehouse Practical Advice from the Experts (Prentice Hall PTR) and DB2 Answers, and is widely published in trade journals and magazines.

SUSAN LAWSON, Principal Consultant with YL&A, has worked with DB2 for over a dozen years. She has served as an IBM Data Sharing advocate, supporting many large DB2 customers and providing technical expertise for migration to DB2 Data Sharing in Parallel Sysplex environments. Now a Director of the Midwest DB2 Users Group and IBM Gold Consultant for DB2 and S/390, she is Technical Editor of the IDUG Solutions Journal.