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.

Dynamic Statement Cache: Sharing Prepared Statements

An SQL statement can be executed by an application with either static or dynamic text. Each type of statement execution has its own benefits. The biggest reason to use static SQL is to avoid the overhead with preparing a statement for execution.

Over the past decade, the use of dynamic SQL has grown dramatically, as IBM continues to improve its performance. Much of this improvement has come through providing DBAs with alternatives to having to prepare a dynamic statement each time one is executed. The first innovation in this area was the introduction of the dynamic statement cache, a pool of memory in which DB2 saves the control structures for prepared SQL statements that can be shared among different threads (execution of a program). To enable dynamic statement cache, YES must be specified for the CACHEDYN subsystem parameter.

When a statement is prepared, DB2 will search for the statement in the dynamic statement cache and reuse the control structures. This works great for applications that code predicates with host variable rather than literals. The problem with literals is that potentially every execution of a statement is unique, so in this case the cache will fill up with many statements, none of which will ever be reused. To help in this circumstance, IBM has introduced a new PREPARE parameter: CONCENTRATE STATEMENTS WITH LITERALS.

When using CONCENTRATE STATEMENT WITH LITERALS, the statement is stored in the cache and each literal is replaced with "&."  For example:

SELECT COL1, COL2 from T1 WHERE COL2 = ‘ABC’ is stored in cache as SELECT COL1, COL2 from T1 WHERE COL2 = &

When the statement is prepared, DB2 will search for the statement in the dynamic statement cache searching for the literal such as "ABC." When not found, DB2 searches the cache for the statement containing the replaced character, &. Once it's found, the control structures are reused. This process doesn't occur if the statement is coded with the "?" parameter marker; for example, this statement cannot take advantage of CONCENRATE STATEMENT WITH LITERALS:

            SELECT COL1, COL2 from T1 WHERE COL2 = ‘ABC’ and COL1 > ?

The avoidance of preparing a statement can provide a tremendous CPU savings. See the DB2 10 Performance Management guide for details about dynamic statement cache, including possible reasons why DB2 won't share certain identical statements in the statement cache.

 

DB2 Subsystem Tuning Tips

Many, many years ago, I was a DB2 systems programmer. As part of my training, I was sent to an IBM DB2 systems tuning course. That type of education cost several thousand dollars back then, but with the knowledge I'd gained, my employer easily recouped its investment in the form of reduced CPU consumption on our system.

One basic thing I learned was what dispatching priority the different DB2 address spaces needed to be. I find it interesting that the current DB2 installation guide doesn't address these settings. (Hint: IRLM should always be the highest.)

Fortunately, the recently released IBM Redbook, "Subsystem and Transaction Monitoring and Tuning with DB2 11 for z/OS," covers many of the same topics I learned in the classroom back when -- even DB2 subsystem address spaces. In section 3.2 IBM recommends that IRLM run at SYSSTC to ensure it's at a higher priority than the other address spaces (MSTR, DBM1 and DIST).

If you haven't worked with traces, Section 2 ("DB2 traces") lists different trace types. Something to keep in mind about traces that I didn't find in this Redbook is that starting some traces creates more overhead than starting others. The most costly trace to start is Class 7 Lock Details, so only use this trace in an extreme situation. (And yes, this is another thing I learned in my old systems tuning course.)

But back to the Redbook. Section 2.3.5 (“Loading IFCID field descriptions into a table”) covers the steps involved in creating tables that contain IFCID records as well as field descriptions. (Note: For more, see what I wrote about this topic some years back.)

Here are just a few of the chapters that focus on monitoring the subsystem:

            Chapter 4  : System address space CPU time

            Chapter 5  : EDM Pools

            Chapter 6  : Data Set Open and Close

            Chapter 7  : Log Activity

            Chapter 8  : IRLM, Locking, and Latching

            Chapter 9  : The zIIP engine and DB2

            Chapter 10: Buffer Pools and Group Buffer Pools

            Chapter 11: DDF Activity

            Chapter 12: Workfiles, RID, and Sort Pools

            Chapter 13: Virtual and Real Storage

This Redbook is worth your time even if you aren't a DB2 systems programmer. For instance, DB2 developers could use this information to help analyze and improve application performance. I also appreciate that the publication notes DB2 version numbers and offers version-specific performance tips. This material gives readers an idea of how various changes will impact their systems regardless of the release they're on.

If you've read this Redbook, please offer your impressions in comments.

DB2 and Hadoop an Unlikely Combo

I recently came across a great SHARE presentation that examines the relationship between DB2 for z/OS. "Big Data Strategies with DB2 for z/OS" provides a nice overview of Big Data with Hadoop before going into some technical benefits of using built-in DB2 user-defined functions (UDF) delivered with Infosphere BigInsights to process Hadoop data from DB2 for z/OS applications.

Here's a sample of the SQL to read Hadoop data:

            SELECT TX.*

            FROM TABLE ( hdfs_Read( ‗http://172.16.134.134:8080/data/sample.csv ‘)

                         AS TX (Seller varchar(20)
                                   , Year INT
                                   , Total decimal(15,2));

 

The capability to enrich operational data with big data sources such as Twitter and Facebook provides powerful benefits to existing applications running DB2 for z/OS, and it will be interesting to see where IBM takes this relationship over the next year or two. I can see some customers bringing the data from Hadoop into DB2 for z/OS and accessing it from DB2 accelerators, while others will choose to store DB2 for z/OS data in Hadoop and conduct local analysis on existing big data. Providing a facility to manage the movement of data and controls around the governance of data being accessed on these different systems will, I believe, be key in the ongoing development of Hadoop support on DB2 for z/OS.