DB2(R) High Performance Design and Tuning [Hardcover]

Richard A. Yevich (Author), Susan Lawson (Author)

Book Description

ISBN-10: 0132037955 | ISBN-13: 978-0132037952 | Publication Date: August 24, 2000 | Edition: 2

Now, there's a comprehensive core book on DB2, including coverage of the not-yet-released Version 7 of DB2: its key characteristics, and how to tune it for maximum performance. Written by world-renowned DB2 consultants Richard Yevich and Susan Lawson -- and endorsed by DB2 Chief Architect Roger Miller -- this book delivers the most thorough, real-world optimization information ever published, for both developers and administrators. Coverage includes: DB2 high-performance design for both OLTP and OLAP applications; practical techniques for improving the efficiency of SQL; working with the DB2 Optimizer; DB2 data sharing implementation and performance; triggers, stored procedures, UDFs, and other advanced DB2 programming techniques; and specific coverage of optimizing data warehouses and enterprise applications.

Editorial Reviews

From the Inside Flap

Preface

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

From the Back Cover

Improve the performance of any IBM DB2 database with expert Version 6.x and 7.0 tuning techniques!

• The only all-in-one, start-to-finish guide to maximizing DB2 performance!
• Top 20 DB2 Performance Myths-and what to do about them
• Includes exclusive, in-depth coverage of DB2 Versions 6.x and 7!
• Tuning for the DB2 designers, developers and administrators
• Design and tuning for robust e-commerce functionality

Foreword by Roger Miller, IBM Lead DB2 Strategist

This is the most comprehensive, practical DB2 optimization guide ever published! World-renowned DB2 consultants Richard Yevich and Susan Lawson cover all aspects of DB2 performance tuning: hardware, physical design, application coding, SQL, stored procedures, triggers, Java, and beyond. Review I/O, storage management, SMS usage, CPU tuning, memory tuning, and bufferpool design. Master every key DB2 database design optimization, including indexing, code and reference tables, and supporting the DB2 Catalog and Directory. Discover start-to-finish application development techniques proven to deliver outstanding performance, including commit strategies, application interfaces, and superior SQL coding techniques.

• Top 20 DB2 Performance Myths—and what to do about them!
• Based on hands-on experience at hundreds of DB2 installations
• Includes hands-on, in-depth coverage of data sharing and DB2 Version 7!
• For developers, designers, and DBAs

From OLAP to OLTP, e-business to high availability, troubleshooting to long-term monitoring, this book covers it all. You'll be amazed at how much more performance you can squeeze out of your DB2 system—with DB2 High Performance Development & Tuning!

See all Editorial Reviews

Product Details

• Hardcover: 736 pages
• Publisher: Prentice Hall PTR; 2 edition (August 24, 2000)
• Language: English
• ISBN-10: 0132037955
• ISBN-13: 978-0132037952
• Product Dimensions: 9.5 x 7.4 x 2.1 inches
• Shipping Weight: 3.5 pounds
• Average Customer Review: 3.5 out of 5 stars  See all reviews (6 customer reviews)
• Amazon Best Sellers Rank: #925,271 in Books (See Top 100 in Books)

More About the Author

Discover books, learn about writers, read author blogs, and more.

Customer Reviews

Most Helpful Customer Reviews

23 of 28 people found the following review helpful:

5.0 out of 5 stars A book that tells you how to do it, not just how it works!, September 2, 2000

By A Customer

This review is from: DB2(R) High Performance Design and Tuning (Hardcover)

Enough can't be said about the usefulness of this book. Rather than explain how things work in DB2, it gives great advice on making DB2 applications perform. We changed a few things that we were a little wrong about, and significantly changed how memory was being allocated (BP, EDM, etc.), and reduced I/O and achieved measureably more transaction throughtput. And we have only started going though all the information, about 680 pages of it. The chapters on Data Sharing really answered some questions that we didn't understand -- great info. Just wish this book had been around earlier.

7 of 7 people found the following review helpful:

3.0 out of 5 stars Falls Short, April 8, 2002

By 

Edster "Edster" (Nashua, NH United States) - See all my reviews

This review is from: DB2(R) High Performance Design and Tuning (Hardcover)

Book gives a great over view and lays out a common sense approach to performance. Each chapter opens up talking a good game but then falls short of the expectations that it has set.

Book falls short by not covering the SQL Optimizer, how it works, or how to change the pre-bind parameters to influence it. Covers DB2 version 5-6 and mentions some things coming along in version 7. Book lists "Hints" but does not give definitions for each one nor the behavior that it should invoke.

Performance tools, techniques, scripts, screenshots are all from the mainframe environment. The event monitor GUI on the windows 200 interface tells you how code is actually behaving instead of the SETEXPLAIN which makes estimates and guesses. The book never mentions the event monitor tool. (I like books that cover different implimentations.)

Considering that this is the only performance book available for DB2, it's the best. I have an older Oracle performance book that most of my SQL Tuning/modeling techniques are based on. This DB2 book doesn't compare in depth or breadth.

If your new to DB2 or to Tuning, this book could help you out.
If you already know Tuning or another database, read the DB2 RedBooks at ibm.com, they're free.

2 of 2 people found the following review helpful:

4.0 out of 5 stars DB2 High Performance Design and Tuning, December 9, 2001

By 

Claude Wright (Pasadena, Maryland United States) - See all my reviews

This review is from: DB2(R) High Performance Design and Tuning (Hardcover)

This text has more insight than any other text I have read on tuning a mainframe DB2 system. The text sometimes seems disjointed, but in trying to cram all of the authors combined knowledge and experience into one text and trying to give it some order, is daunting. I would have to say this text rates a bit higher on the scale than DB2 Developer's Guide by Craig Mullins because it is not trying to cover all of the bases that Mr. Mullins is in his book. This is strictly for giving pointers on tuning DB2 in a mainframe OS/390 environment. I hope the authors combine their efforts and put out a true "DB2 for OS/390 Certification Guide" so we DB2 DBAs can train newcomers to the field. Their latest effort falls short of that.