The DB2Night Show #210: Db2 Performance Tuning- Indexes vs Prefetch

@mhubel

Special Guest: Martin Hubel Owner, MHC IBM Gold Consultant Db2 LUW Performance Tuning: Indexes versus Prefetch! 100% of our audience learned something! Famous for putting his kids through college by Dropping Db2 Indexes, Martin covers a wide variety of Db2 Performance Tuning topics with focus on indexes, prefetch I/O, bufferpools, Db2 MDC and ITC tables, and much more (detailed below). Watch and learn from a top Db2 performance pro!

Show Host Scott Hayes Commentary

This is definitely one the best, most helpful, most robust shows we've offered on the topics of Db2 LUW Performance Tuning. Martin and I have been friends since Db2 UDB V5, and, in fact, it was Martin who inspired me to become a Db2 consultant way back in 1998. We had a lot of fun producing this show. I drank eggnog and Martin enjoyed some specially flavored coffee. Santa Claus came to visit too - you'll hear his HO HO HO laughter. Enjoy our replays!

Speaking of replays, we are now publishing The Db2Night Show Replays to our YouTube Channel. Please subscribe!

A message from our sponsor:
DBI Software

Working with DBI is like making money magically appear! Typical customers enjoy 40-60% CPU utilization reductions in the first week, plus measurably faster transaction and query response times. More importantly, your time savings will be substantial! Read our newest SUCCESS STORY with HealthPlan Services, do your due diligence, and speak with as many references as you have time to call. We saved a health care company $250,000 in one afternoon, solved a performance problem in two hours that two senior DBAs were unable to solve in six months, and we have helped top brand name retailers achieve top ranked e-Commerce performance on Black Friday multiple years in a row. 8 of the Top 25 Retailers, plus others, plus top Banks, State Governments, Manufacturers, Insurers, and more rely on DBI. How can we help you? Contact DBI

Join The DB2Night Show LinkedIn Group

The DB2Night Show has a LinkedIn Group. The "closed " group is open to any LinkedIn member. Join to receive LinkedIn group updates includingfuture shows and replay information, and, if you like, start some discussions about the show! Join The DB2Night Show LinkedIn Group!

The Agenda for "Db2 Performance Tuning-Indexes versus Prefetch "

• Why didn't Db2 use my index? Why was a scan chosen?
• Tuning and Designing indexes: key things to remember
• What about prefetch? How can prefetch be improved?
• Additional Table Design Options for performance
• Prefetch versus non-clustered indexes: which wins?
•     And How Good does an index have to be for Db2 to choose it?

Topics Discussed Include:

• Matching Index Columns and Index Predicates
• Order of columns in indexes
• How the Db2 Optimizer Thinks about cost
• Removing Indexes - common fears
• Fixing Indexes
• Dropping Indexes
• Index Tune-up Guidelines
• Clustering Indexes and Data
• Multi-Dimensional Clustering Tables
• Tuning Vendor Indexes
• Pitfalls from Vendor Indexes
• The Db2 Design Advisor Tool
• Reference to Scott Hayes' Advance Index Benefit Analysis IDUG Presentation
• How Db2 Prefetch Works
• Random, Synchronous, Sequential, Asynchronous I/O Explained
• Pros and Cons of Db2 Prefetch
• Db2 Bufferpool Tuning Methods
• Animated Bufferpool Activity Illustrations
• Create and Alter Bufferpool
• NUMBLOCKPAGES and BLOCKSIZE
• Calculating BLOCK I/O performance
• MDC Multi-dimensional Clustering Tables
• ITC Insert Time Clustering Tables
• MDC and ITC Design Considerations
• Other Factors that Affect I/O Performance
• SSDs Solid State Disks
• Db2 Compression
• DB CFG Parameters
• SORTHEAP and other Memory Areas

Get the Download Replays

WMV Format - approximately 85MB

To download a recorded replay of Episode #210 in WMV format, right click on the link below and choose "Save As... "
Episode #210, 21 December 2018, Db2 LUW Performance Tuning-Indexes versus Prefetch

MP4 Format - standard resolution - 154MB

Have an iLife, iDevice, iPod, iPad, or iPhone? Right click and download the MP4 ( M4V ) format!

NEW! Watch on YouTube!

PDF Handout

Martin generously agreed to share a PDF of his presentation, so you can get your copy here: GET the PDF!

Sharing is Caring!