April 22, 2014

The DB2 for z/OS Performance Handbook

I’m very excited to announce that CA Technologies (my employer) commissioned Dan Luksetich to author a document on DB2 for z/OS performance. Dan is an amazing person who has tremendous knowledge and real-world experience tuning applications that access DB2 for z/OS.

Rather than try to explain what is unique about this document, I'll instead refer you to Dan's introduction:

About The DB2 Performance Content in This Book

There is a DB2 for z/OS Managing Performance Guide, and there are DB2 for z/OS performance redbooks. These valuable and often under utilized references are extremely important tools in your day to day performance tuning efforts. This book is not a repeat or condensation of that material, nor is it a replacement for that material. The intention of this book is to complement the printed performance information that is part of the DB2 documentation and to give the reader some insight on particular situations where certain features are of benefit or detriment. Therefore, it is the reader’s responsibility to cross-reference and research each feature and technique discussed in this book. Also, remember that nothing is free. With any deployment of any technique or feature there is going to be a cost to pay. I’ll attempt to balance those choices in this book.

This book is heavily focused on application, database, and SQL design and tuning for performance. Systems tuning and performance will be secondary to application and database tuning. It’s not that system tuning is not important, and you should take system tuning seriously. I will address some of the big buttons in this book.

This is a performance book and not a reference guide. What this means is that this book should be read from start to finish, and not used to look up various features to learn something about performance. There are many features that are addressed in multiple sections of this book, as well as settings, techniques, and features that have a relationship to each other and are not necessarily addressed in one place in the book. You can use this book as a reference, but it is strongly recommended that you read it entirely first!

This book is very much slanted toward transaction processing as opposed to data warehousing and business analytics. DB2 for z/OS is an excellent data warehouse engine. Recent advancements both in query performance and the introduction of the IBM DB2 Analytics Accelerator (IDAA) make it a great and economically feasible database for data warehouses. I’m especially an advocate of running both transaction processing and decision support against the same production database. However, in my personal experience most enterprises are utilizing DB2 for z/OS for transaction processing and not data warehousing. So, the majority of design and tuning techniques presented here are specifically in support of transaction processing systems. Many of the concepts can be applied to a data warehouse, especially the information included in sections 2, 3, and 4.

There is a balance to everything. This is a performance book. It is much more about performance than flexibility and availability. The concepts of performance, flexibility, and availability are generally opposed to each other, especially when implementation and operation costs are of concern. It is your responsibility to strike a balance in your implementation that is appropriate for your enterprise and situation.

The information provided in this book is relevant through DB2 11 for z/OS. In most feature descriptions I make no distinction between versions when discussing various features. Be sure to check the DB2 manuals for the version you are working with to confirm the availability of specific features

I'll also include the table of contents:


            Section 1: The Basics of Performance and Performance Tuning

            Section 2: SQL Access Paths

            Section 3: Coding SQL for Performance

            Section 4: Utilizing DB2 EXPLAIN

            Section 5: Controlling SQL Access Paths

            Section 6: Table and Index Design for High Performance

            Section 7: Memory, Subsystem Parameters and Performance

            Section 8: Monitoring Subsystem and Application Performance

            Section 9: Testing Performance Designs and Decisions

            Section 10: Top Tuning Tips

            Section 11: About the Author


The DB2 for z/OS Performance Handbook can be downloaded from CA Technologies. Registration is required.