The following article is contributed by Martin Hubel, an IBM DB2 GOLD Consultant, IBM Champion, and one of the world's top DB2 performance consultants for both DB2 z/OS and LUW. He can be easily reached via LinkedIn or via Twitter at @mhubel. Martin's direct email address is also readily available from his many IDUG presentations.
When Oracle bought Peoplesoft, Siebel, and JD Edwards, an opportunity arose for IBM and SAP to work together to provide a superior ERP offering. SAP sales representatives received compensation for selling DB2, and many large companies migrated from Oracle to DB2.
SAP and IBM have held joint development discussions on how DB2 may better meet the needs of SAP and its customers. As part of development discussions, customers are invited to make presentations to both development teams. This is known as the SAP DB2 Technical Leadership Exchange (TLE).
Through this partnership, IBM has used the information gained to simplify DB2’s configuration, management and its ability to handle large data structures and data for SAP. To date, IBM has taken an “application agnostic” view and has worked to support SAP and other application packages by providing world class infrastructure capability. DB2’s BLU technology has provided cost-effective column-based technology to the analytics community.
On SAP’s side, DB2 is easily managed via the DBACockpit, SAP’s built-in DBA tool. The DBACockpit allows for the display and management of the DB2 system side of SAP, including configuration parameters, utility schedules, system performance, history, and SQL performance. All system management is typically coordinated by the Basis Team, a group within customer organizations that manage the SAP environment.
Of particular importance are the SQL tuning tools built into the DBACockpit. SAP is a complex suite of applications with the expectation during implementation that customization is performed to match the requirements of each organization. Implementation usually costs several times the cost of the SAP software. These customizations, called z code, involve the addition and modification of programming code, in SAP’s own ABAP programming language, to support the organization’s needs. The DBACockpit supplies an interface to DB2’s Explain and Index Advisor so that the generated SQL can be tuned.
I became involved with an SAP customer in July 2010 when they went into production. This company, a medium sized B to B company with 1,200 employees and tight profit margins, chose to buy just half of the hardware recommended by SAP. When more hardware is purchased, it is also necessary to buy additional software licences, so the rationale was to start smaller and add hardware only when it was truly needed. DB2 performance tuning was critical.
Initially, there were many performance challenges, and I was engaged to help solve the issues. During the first six weeks, we enlarged memory areas, and added many indexes to support this customer’s z code. To achieve our successful tuning, we made use of the SAP tools, the DB2 performance toolset from DBI Software, and other free industry tools.
Occasionally, we found that certain search fields had not been implemented at this company. Indexes on these search fields showed a single, constant key value, and, while DB2 would dutifully maintain them, they would never help with information retrieval. We found significant performance benefit by dropping these unneeded indexes. All index changes, both additions and deletions, were done via the standard SAP interfaces to keep us within SAP compliance.
Over the past six years, we have continued to tune DB2 for SAP using the SAP Cockpit and DBI Software tools. We have increased the system memory available to DB2 as we continue to monitor activity. We have made approximately 200 index changes as we continue to enhance and maintain the SAP application. Performance has been acceptable, and we continue run on about half of the hardware initially recommended by SAP!
Tuning has paid off and has been very cost effective. As my experience grows at more SAP installations, tuning DB2, including index changes, continues to be important as we work to ensure service levels, user productivity, and cost savings.
In 2010, SAP acquired Sybase Inc. and its database products. By 2013, SAP introduced HANA as a database product for its business warehouse, and in the past two or three years, SAP now recommends HANA for its core ERP systems. While SAP and DB2 continue to hold joint marketing events, there have been some subtle changes, and some not-so-subtle changes in SAP’s approach with DB2.
In 2013, SAP raised the price they charged customers for DB2. This was not a huge increase, but it was done without consulting IBM. IBM encouraged customers to buy DB2 from them at the old price. This changes the value proposition for DB2 to become more expensive simply by SAP’s unilateral pricing decision.
I have been presenting regularly at the TLE, and pushback from SAP started about two years ago. SAP technical people, both in the field and through support, are now recommending that no index changes be made! Their rationale is that these indexes might not be needed today, but they could be needed in the future where you may have a big problem if they are missing.
Further to this, SAP alleges that their application is well designed and their queries only return single rows. This may be true in the SAP development lab, but at my first opportunity, I checked our SAP systems and found that our worst performing SQL returned 350 rows! This query was added to our tuning list, and it is typical of the queries we tune to save money and improve response times.
In my experienced opinion, SAP’s arguments are fallacies! Indexes can help with data retrieval, and yet poorly designed indexes can take considerable resources to maintain. Indexes are both easy to drop and to add back when they are needed, so throwing computer resources away is a waste of money and a waste of time in slower computer responses. With SAP supplying tools to tune indexes in the DBACockpit, why has their message changed? What is their motivation for this change? (Italics emphasis added by Scott Hayes)
If this still isn’t clear, consider the following analogy: Why not leave your car idling in the driveway in case you need to go somewhere in a hurry? Don’t worry about the cost of gas, or the wear on your car; you want to be ready to go. You can continue this analogy when you consider how simple it is to start the car; it is just as simple to drop and create DB2 indexes. This analogy may seem simplistic, or even stupid, but SAP’s argument about touching their indexes is seriously flawed.
Related to this, our Basis administrator contacted SAP support regarding an application problem. One of the first recommendations from support was to remove all of our index modifications! They stated that we needed to undo these changes before they could “fix” our problem, even though we had used the SAP facilities properly to make our changes and our problem was not related to indexes. This SAP request essentially required us to remove six years of tuning and throw it down the toilet! We did get the problem solved, and we didn’t remove our index changes, but we had to convince our Basis team not to believe everything SAP says.
On one hand, SAP appears to be very interested in having their customers succeed with DB2. Based on my SAP and DB2 experience, I know of the mutual respect that the development teams have for each other, and the fine technical results that have benefitted their mutual customers.
On the other hand, SAP is tilting the playing field against DB2, by raising DB2’s price (where they can), and telling customers not to tune their systems. Perhaps this has been done to make a better business case for HANA.
More importantly, if SAP does manage to capture the database component from IBM by replacing DB2 with HANA, where does that leave you as an SAP customer? You have spent a lot of money on building your IT infrastructure, and you are facing the situation of having everything from a single source. This could easily mean that you have no recourse as prices increase, as support problems arise, or as performance becomes intolerable --- facts often overlooked by industry analysts that advocate “one neck to choke.” It is often better to have more smart minds work to solve problems, and IBM has shown its willingness to adapt DB2 to SAP’s needs. Is HANA in your company’s best interest? For those who have made an investment in DB2, or those considering DB2, I think not.
IBM is currently doing a road show in select cities themed "IBM DB2 for SAP - Poised for Growth". CLICK HERE to learn more about this event, or check out these event locations:
And while we're on the subject of DB2 SAP, IBM DB2 GOLD Consultant Martin Hubel has a few thoughts that he'd like to share with your about SAP tuning and the evolving relationship between IBM DB2 and SAP. Martin will be our guest in Episode #189 of The DB2Night Show on 10 February 2017. Get details and Register for IBM DB2 LUW Optimized for SAP - The Rest of the Story by CLICKING HERE.
A few thoughts, in no particular order...