Special Guest: Martin Hubel
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:
In the interest of time, we didn't do a commerical today! You're welcome. However, we did point out that Database Trends and Applications Magazine (DBTA) has now recognized DBI Software as a "Top 100 Trend Setting Products for 2019" solution provider - this is SIX years in a row!!
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 including future 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!
Martin generously agreed to share a PDF of his presentation, so you can get your copy here: GET the PDF!
Sharing is Caring!Share a great show with your friends and followers!