Team with the best
Db2® LUW Performance Tools
company in the World

The DB2Night Show #168: DB2 LUW Advanced Index Benefit Analysis

January 10, 2016, 9:39 pm
Posted by Scott in DB2 LUW
Scott Hayes, President DBI Software, IBM DB2 GOLD Consultant
@srhayes :: @db2performance

Special Guest: Scott Hayes, DBI Software

Sage DB2 LUW Performance Advice
Advanced Index Benefit Analysis
BLU Table Convert Row to Column Analysis

100% of our audience learned something! In Sage Advice Part 1 during Episode #142, Scott teaches audiences how to find the biggest problems in DB2 based on weights. In Sage Advice Part 2 during this Episode, Scott teaches audiences how to accurately determine the benefit of each index recommended by IBM program "db2advis". As a bonus, if you are using DB2 10.5, Scott provides a methodology and queries for quickly and easily determining if a Row based table should be converted to BLU Column based. Sage Advice Part 3 "Predictive Index Impact Analysis" will be offered at IDUG conferences in 2016 - hope to see you in AUSTIN Texas at IDUG! Watch and learn...

Show Host Scott Hayes Commentary

According to our polling questions, most DBAs have seen IBM program "db2advis" recommend multiple indexes for a solution. Also, when multiple indexes are recommended, most DBAs spend 30 to 240 minutes agonizing over which of the indexes are the most valuable or important.

As you'll see in this presentation, and as you've probably experienced, the "benefit" XML tag in the db2advis output is either misleading, inaccurate, or grossly wrong. During this presentation, you will learn the correct way to calculate the value of each recommended index via two methods: index addition and index subtraction.

After seeing this presentation at IDUG North America in Philadelphia 2015, my dear friend Ember Crooks wasted no time testing these index analysis methods and then blogging about them. Ember has saved me a lot of typing time, so be sure to check her blog post for more details in addition to the video replays and PDF materials provided below.


Everyone that Attended today's show won a $10 gift card! Of course, you had to join the webinar on time to hear Scott disclose the secret prize word "AUSTIN". It's true - Education does PAY - especially on The DB2Night Show!

Like learning about IBM DB2 LUW?

These upcoming events might interest you!

A message from our sponsor:
DBI Software

Let's just cut to the chase. DBAs spend hours and hours trying to figure out which indexes are the most beneficial, which to create, and which, if any, might be harmful. During this show's quick commercial, DBI demonstrated AUTOMATED Advanced Index Benefit Analysis. You can spend hours doing the benefit analysis and calculations by hand, or you can get an accurate index benefit analysis with DBI in under a minute. Ready to save time and tune confidently? Contact DBI Now

Magic Hat Money Working with DBI is like making money magically appear!

Typical customers enjoy 30-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. 7 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!

Get the Download Replays

WMV Format - approximately 65MB

To download a recorded replay of Episode #168 in WMV format, right click on the link below and choose "Save As..."
Episode #168, 8 January 2016, DB2 LUW Sage Advice Part 2: Advanced Index Benefit Analysis with host and guest Scott Hayes!

MP4 Format - standard resolution - 165MB

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

BONUS! PDF Handout!

A PDF handout is provided as reference material so that you can copy and paste many of the SQL commands. The PDF, alone, is not a substitute for what you will learn by watching the video replays. If you PROMISE to watch the video replays, then you are granted permission to CLICK HERE for the PDF.

Did you learn something from this show?

Share a great show with your friends and followers!

Printer friendly