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.

Searching for IBM Global Education Offerings

Have you searched the Internet for DB2 for z/OS training but couldn't find the course you needed? Recently I found myself in this situation I was looking for a specific DB2 for z/OS performance monitoring and tuning course that was recommended by a colleague in the Czech Republic. My colleague in Prague found a local class called "DB2 for z/OS: Monitoring & Tuning for Systems’ Performance." But I couldn't find anything equivalent in the United States.

Then I stumbled on an IBM global education search engine. To help you pinpoint your search, you can filter down by country, language, technology area and task area. To get a list of DB2 for z/OS classes I selected "information management" for the technology area and "performance" for the task area. Once I removed the country filter, I got back a list of classes in several countries. Turns out the course I was looking at isn't offered in the U.S., but at least now I know for certain.

 

May Mainframe Madness

For all the posts I've had of late about free educational and technical materials, I've been remiss to mention what my own company has been up to.

This month CA is running an event it describes as the world's largest virtual mainframe trade show: CA May Mainframe Madness 2012. By registering here, you can access webcasts, live chats and "on demand" presentations hosted by technical experts from across the mainframe world.

Here are some upcoming webcasts:

• Thursday, May 17, 10 a.m. EDT: Sheryl Larsen
        - DB2 9 and 10 SQL: Index and Optimization Enhancements
• Wednesday, May 23, 10 a.m. EDT: Craig Mullins and Steve Crocker
         - Getting to DB2 10 for z/OS

Here are some presentations:

• On Demand – Craig Mullins
         - DB2 Top 10 Lists Part 1
         - DB2 Top 10 Lists Part 2
• On Demand – Steen Rasmussen
       - Tips for Better DB2 for z/OS Performance
       - Do You Really Want NOT to LOG?
       - The DB2 10 catalog – A Revolution

And on Tuesday, May 29 at 1 p.m. EDT, there's a live chat roundtable with Sheryl Larsen, Steen Rasmussen, Scott Jessee and Dave Schipper. The moderator is none other than yours truly.

Once you've registered, you'll get a better idea of the virtual trade show format (you can also view these photos: Image 1, Image 2, Image 3, Image 4, Image 5). Stop by the "Database Management booth" in the "exhibit hall" to ask questions, or check out the "House Of Mainframe lounge" to network with peers, check for messages, join a group or sign up for a chat.

Updates to Redbooks on DB2 10 for z/OS

I've written quite a bit about both IBM DB2 for z/OS manuals and IBM DB2 Redbooks. I generally use the manuals to educate myself about DB2 for z/OS features. When I'm looking for more in-depth details on different areas of practice within DB2, I often turn to Redbooks.

As with manuals, Redbooks are updated on a regular basis. Here's a quick list of DB2 Redbooks that I've read and used. All have been published or updated within the past year. The first two listings include links to previous blog entries I've written about these publications.

• "DB2 10 for z/OS: Configuring SSL for Secure Client-Server Communications" (published: Oct. 11, 2011; last update: Dec. 5, 2011; previous post: Getting Started with SSL)
• "Security Functions of IBM DB2 10 for z/OS" (published: Sept. 2, 2011; last update: Sept. 14, 2011; previous post: Roles and Trusted Context)
• "DB2 10 for z/OS Performance Topics" (published: June 21, 2011; last update: Dec. 6, 2011)
• "DB2 10 for z/OS Technical Overview" (published: Dec. 30, 2010; last update: Dec. 6, 2011)
• "Managing IBM DB2 10 for z/OS Using the IBM DB2 Administration Tool for z/OS Version 10" (published: April 22, 2011)
• "Extremely pureXML in DB2 10 for z/OS" (published: Jan. 28, 2011)

New Manual Covers DB2 10 for z/OS Security

Every few months, I visit IBM's DB2 product library and refresh all of my PDF manuals.

IBM was busy during the latter part of 2011. The majority of manuals have been updated since September, meaning I refreshed almost my entire DB2 10 manual library. Even if you're still on DB2 9 or DB2 8, you should check your own list, because these manuals have been refreshed as well.

In the course of my library upkeeping, I also found a new manual that you should be aware of: "DB2 10 for z/OS: Managing Security."

Since I haven't yet read this manual, I'll give you the roughest of outlines by citing some chapter titles:

Chapter 2. Managing access through authorization IDs and roles

Chapter 3. Managing access though RACF

Chapter 4. Managing access through row permissions and column masks

Chapter 5. Managing access through trusted context

Chapter 6. Managing access through data definition control

Chapter 7. Managing access through exit routines

Chapter 8. Protecting data through encryption and RACF

Chapter 9. Auditing access to DB2

 With the obvious interest customers have security and compliance issues, and given the many security-related enhancements in DB2 10, it's easy to understand why IBM came out with this new document. Diving into this information is one of my top priorities for the early new year. I'm really looking forward to reading this manual and learning more about DB2 10 for z/OS security.

Getting Started with SSL

If you're a DB2 systems programmer who must configure DB2 to support secure client communication through SSL, or if you're a developer who needs to configure your application to use SSL, be sure to check out IBM's recently published Redpaper: "DB2 10 for z/OS: Configuring SSL for Secure-Client Communications."

I frequently recommend IBM Redbooks publications on this blog. I love them. I've worked in many small shops over the years, and when you're in that role, having to wear as many hats as you do, it's tough to become an expert in any one area. That's why I've always appreciated Redbooks. They've helped me with install DB2 for z/OS and configure many different DB2 components. I find the publications valuable because they explain the subject and provide sample JCL and scripts.

For this particular Redpaper (a Redpaper is a shorter technical document that's published on the Web only), you'll appreciate the overview with pictures in the first chapter. It's a good starting point for anyone who's unfamilar with SSL and TCP/IP AT-TLS. The next few chapters discuss how to configure SSL for the DB2 10 server as well as the DB2 10 requester.

Then it's time to examine what it takes to get your application to work with SSL. One technique involves using the IBM Data Server Drive for JDBC and SQLJ to enable a Java application to connect to DB2 using SSL. You don't use Java? You want to use SSL to connect your DB2 for LUW (Linux, UNIX, Windows) application to DB2 for z/OS? Read on. The next few chapters introduce the IBM Global Security Kit (GSKit) and DB2 Connect server for LUW (Linux, UNIX, Windows). The final chapter covers providing client access to DB2 using digital certificates.

By the way, SSL is a relatively new capability, having been introduced with DB2 9 for z/OS. So plenty of folks have little or no experience with it. If you've configured DB2 or your application to take advantage of SSL, please share your experience in Comments.

DB2university.com

I recently learned from Facebook group, DB2 on Campus, that IBM is starting a new website that's dedicated to helping people get educated and certified in DB2.

DB2 University has just completed beta testing and launched on Dec. 2. Initially, only one free course, called DB2 Essential Training I, is available. These additional courses are expected to be added soon:

• DB2 Essential Training II
• DB2 Essential Training III
• IBM Data Studio Essential training
• SQL Fundamentals I

Each of these offerings will be delivered as "instructor-led online" (ILO) courses. Instructors will monitor forums and grade assignments and a final exam. Courses are planned for 4-6 weeks, with deadlines for each assignment. Courses may be taken simultaneously. Though some courses do have pre-requisites, they will not be enforced at this point.

DB2 Essential Training I isn't considered an ILO course. The final exam will be worth 100 percent, and it will be more difficult than what was given during the beta testing.

ALL courses will offer "prizes," including:

• FREE IBM certification vouchers
• 50-percent discount certification vouchers
• "Understanding DB2" book (worth $60 U.S.)

Register now at db2university.com. And if you're on Facebook, check out the DB2 on Campus group.