Introduction
Thank you IDUG for the invitation to contribute an article about DBI Software’s pureFeat™ Performance Management Suite for IBM DB2 LUW. For the past four consecutive years, Database Trends and Applications Magazine (DBTA) has honored DBI pureFeat as one of its Top 100 Trend Setting Products. Further, DBI Software has also been recognized by DBTA as one of the “Top 100 Companies That Matter Most in Data” four years in a row. DBI Software consistently maintains a 98%+ year-over-year customer maintenance retention rate, and customer support surveys indicate that 100% of DBI’s customers are either “Very Likely” or “Likely” to recommend DBI pureFeat to their friends and peers. As publicly documented by DBI’s many Customer Success Stories, several top retailers, banks, insurers, manufacturers, health organizations, large government organizations, and others, rely on DBI pureFeat to consistently achieve outstanding DB2 LUW performance results at minimized operational costs. And, because DBI pureFeat has been and is so successful, this has enabled DBI Software to generously give back to the DB2 community via IDUG education and sponsorships, and edutainment via The DB2Night Show™ --- which has provided nearly $30,000,000 worth of free education to the worldwide DB2 community since 2009.
The Secret Ingredients
So, what makes DBI pureFeat so successful? We believe there are several contributing factors, but first and foremost is DBI’s culture of being helpful which drives our product innovations, inspires our passion for exceptional service, and champions our contributions to the DB2 community. Combine these factors with simplicity, the lowest overhead in the market, and consistently achieved great results in minutes, and DBI’s pureFeat easily wins the hearts of DBAs who use it.
Innovation
When we do a root cause analysis, the most fundamental contributing factor is innovation. DBI’s founder, Scott Hayes, invented SQL statement concentration in the year 2000 as documented by U.S. Patent #6,772,411. Scott was first to talk about automating DB2 UDB tuning in his 1997 IDUG presentation “Tune DB2 Universal Server while you Fish, Golf...”, and in DB2 V9.1 the Self Tuning Memory Manager (STMM) was introduced in 2006. The Statement Concentrator (STMT_CONC) was introduced to DB2 in V9.7, and a unique statement identifier was added to DB2 V10.5 FP3. DBI pureFeat began automatically tracking configuration and index changes way back in 2008, and in 2012 IBM added a Change History Event Monitor to DB2 V10.1. Having data is one thing, and presenting it in meaningful ways is yet another.
DBI pureFeat excels in performance information presentation, and DBI is widely appreciated for its ease of use. Now since most tool vendors will readily claim that their toolset is easy to use, we need to take a closer look and you can decide for yourself during your own evaluations.
From Problem to Trusted Solution in Five Mouse Clicks, or Less
What are the problems?
Is a performance issue a database problem, or not? If it is, what is the bottleneck? Where is time being spent? What are the SQL statements causing the time spent? Which SQL are the heaviest CPU and I/O consumers that are weighing down the database and degrading performance? A well stated problem is a half solved problem. You can’t fix something until you know what needs to be fixed! Sometimes problems are small, latent, or hidden, yet quietly waiting to cause a performance disaster – we politely call these “opportunities for improvement.” DBI can show you the real root cause problems, based on aggregated relative weight analysis, or opportunities, in just one or two mouse clicks. We’ll illustrate in the short video clip below.
Scott Hayes talked about root cause problem isolation at IDUG in 2014 during his tech session “DB2 LUW Sage Performance Advice.” If you missed this and would like to learn more, watch a replay of The DB2Night Show Episode #142.
What are the solutions?
Once root cause problems, or opportunities for improvement, are accurately identified, next you hopefully want solutions that will mitigate the problems! Many DBAs will try to interpret Explain plans to arrive at a solution, which DBI can help you easily do, but we assume you are in a hurry and we’ve only got five mouse clicks or fewer to work with, so we’ll cut to the chase in our next short video clip illustration.
What are the best, most valuable solutions?
Not all solutions are created equal. Presuming you’ve used the IBM Design Advisor (db2advis) before, you’ve probably noticed that sometimes multiple indexes are recommended as part of the solution set. You probably also intuitively know that some of the recommended indexes are more valuable or important than others, yet, if you look at the XML output, the stated BENEFIT of each index will likely be represented as the same for all of the indexes. If the BENEFIT values are different, they’re usually inaccurate. This leads us to another DBI innovation --- Advanced Index Benefit Analysis which actually determines the accurate value of each recommended index through processes of Index Addition and Subtraction.
Index Addition answers the question “How much value, or what percentage of savings, is obtained if this index, and only this index, is created?” Index Subtraction presumes that some indexes might be working together in a team effort, so Index Subtraction answers the question “How much value is lost by creating all of the recommended indexes except this one?” Index Addition and Subtraction tell you instantly which indexes are the most important to the solution set while helping you avoid the creation of those that offer minimal value --- no more guessing!
Scott Hayes presented about this Advanced Index Benefit Analysis at IDUG in 2015 during tech sessions “Sage Advice Part 2: Advanced Index Benefit Analysis.” If you missed this presentation and would like to learn more, watch a replay of The DB2Night Show Episode #168.
Can the solutions be trusted?
Most DBAs have probably experienced making a change that subsequently caused performance to unexpectedly degrade. On the flip side, sometimes changes result in much greater performance improvement than anticipated. Rampant fear of bad changes, at all levels of management, is why Change Control procedures exist. Sarbanes-Oxley (SOX) requires that all changes are carefully considered and appropriately tracked. Change Control approvers want to know why a change is being considered, what the anticipated benefits are, and are there any risks.
DBI pureFeat makes this analysis incredibly easy and accurate. With just one mouse click, DBI will show you the predicted consequences of a contemplated change by discovering the impacted workload and showing you before and after costs for each impacted SQL statement. You will see how many statements improve, how many remain unchanged, and how many, if any, will degrade. Users will also see a summary of before and after estimated total costs, CPU utilization forecasts, plus elapsed time estimates. One more mouse click will show users all of the details for each SQL statement in the impacting workload, so you can see which statements improve, remain unchanged, or are at risk of likely degradation. In short, DBI shows you everything you need to present to Change Control management so that you can get performance boosting changes approved with confidence.
Please enjoy a short video clip of this industry breakthrough capability:
Scott Hayes presented about Predictive Index Impact Analysis at IDUG in 2016 during tech sessions “Sage Advice Part 3: Predictive Index Impact Analysis – Know Before You Create!” If you missed this presentation and would like to learn more, watch a replay of The DB2Night Show Episode #188.
Were the solutions successful?
President Ronald Reagan famously said “Trust but Verify.” Measuring the performance consequences of any changes (hardware, software, applications, configuration, or indexes) is critically important. In the wake of changes, management will want to know if database systems are running faster or slower, plus impacts to system resource utilizations. As a DBA or DBA manager, you will want to document your performance tuning successes – this is the path to getting bonuses, raises, achieving objectives, or even just keeping your job.
DBI pureFeat provides two very valuable methods for measuring the performance impacts of change. First, DBI provides trend charts with change events plotted on the charts. The plotted change events are important because they give you context information about the graphed results. Second, DBI provides robust, side-by-side, before and after, performance comparisons at both the database and SQL statement level. After change occurs, users can see changes to transaction response times, CPU utilization, SLA (Service Level Agreement) attainments, bufferpool hit ratios, and dozens more database KPIs (Key Performance Indicators).
From the database change summary display, users can, with one mouse click, get a PDF report of all changes that occurred between the two compared timeframes or drill down to a SQL statement performance comparison. If performance degraded, the PDF report is invaluable for determining what changes were made, and which should perhaps be reversed. The SQL statement performance comparison is absolutely priceless for determining the details of which statements improved and which degraded. This cannot be described by words alone, so please enjoy a short video clip that illustrates DBI’s comparison capabilities:
A complete, automated, performance methodology
Discover Issues – Develop Quality Solutions – Implement with Trust - Verify
In this article, we’ve showed how DBI pureFeat will help you:
- Quickly find the most harmful problems
- Help you determine quality solutions
- Build trust with the solutions via Impact Analysis
- Quantify and verify your successes via trend charts and comparisons
Rinse, Lather, and Repeat. Follow this automated methodology through a few cycles and you will likely achieve remarkable, measurable performance improvements with reductions in system resource utilization. It is not uncommon for DBI customers to double performance while cutting CPU utilization by 25-50% in an afternoon.
Summary
In this article, we’ve showcased only a small fraction of DBI pureFeat’s capabilities. Further analysis and automation can be achieved by using pureFeat’s robust lights-out alerting functions that can send notification emails, send SNMP trap alerts, automatically run DB2 commands, or automatically invoke DBA authored scripts. Like db2top or dsmtop, DBI provides information about what’s happening “Right Now” with automated refreshes. DBI’s lock contention analysis makes it easy to visually see blocked applications and lock holders – with one click to next steps such as force application or Explain the SQL.
The best way to discover what DBI pureFeat can do for your organization is to try it. Our proof of concept process is free and takes only a few hours. What’s more, performance improvement results are guaranteed by our $10,000 promise to help you make improvements; if we do not, we’ll make a donation to the charity of your choice. See DBI’s DOWNLOADS page for details. Unfortunately for charities, we haven’t paid yet since we started this results guarantee years ago!
Suggested Next Steps:
- Contact DBI to get started on a free Proof of Concept evaluation right away!
- Visit DBI’s EVENTS page to sign up for educational webinars that demonstrate pureFeat’s features. We value your time so we’ll buy the coffee!
- Visit DBI’s pureFeat solutions
- Visit with DBI in the IDUG Exhibit Hall, and attend DBI’s educational Vendor Solution Presentations.
- Tune into The DB2Night Show™ for free DB2 education!
#hot-topics