Sheryl M. Larsen, Inc.

IBM DB2 tuning tools, expertise help client run 20 percent more queries with 25 percent less CPU load

Published on 25-Mar-2013

"There are tremendous benefits—both in terms of improved performance and cost savings—by upgrading to the latest version of DB2." - Sheryl M. Larsen, President, Sheryl M. Larsen, Inc.

Customer:
Sheryl M. Larsen, Inc.

Industry:
Computer Services

Deployment country:
United States

Solution:
System z Software

Overview

For more than a decade, Sheryl M. Larsen, Inc. (SMLI) has delivered advanced IBM® DB2® education and consulting services to help companies optimize their data-driven services. The company’s president, Sheryl Larsen, is an internationally recognized researcher, consultant and lecturer, known for her extensive expertise in optimizing SQL.

Business need:
Sheryl Larsen advises companies that ongoing database maintenance isn’t a luxury—it’s a requirement. As new applications come on line and data volumes grow, database maintenance can help organizations keep costs low and maintain high levels of performance.

Solution:
As one of Larsen’s clients was preparing to launch four new major applications, Larsen used IBM Data Studio software to help accelerate query tuning. Also by moving power analytic users to their own stand-alone IBM® Netezza® data warehouse appliance, the company reduced the load on its mainframe by 500 million instructions per second (MIPS), even as four new applications went live.

Benefits:
● Runs 20 percent more queries while incurring 25 percent less CPU load ● Saves USD5 million by avoiding an upgrade to a mainframe server ● Accelerates application performance, reducing some queries from 4,000 to .0007 CPU-seconds

Case Study

For more than a decade, Sheryl M. Larsen, Inc. (SMLI) has delivered advanced IBM® DB2® education and consulting services to help companies optimize their data-driven services. The company’s president, Sheryl Larsen, is an internationally recognized researcher, consultant and lecturer, known for her extensive expertise in optimizing SQL.

The need

Larsen advises companies that ongoing database maintenance isn’t a luxury—it’s a requirement. As new applications come on line and data volumes grow, database maintenance can help organizations keep costs low and maintain high levels of performance.

“Few things do well without proper care,” Larsen says. “Houses, cars, pets, friends, and family all need our attention to stay happy and healthy, and database management systems are no different.”

The solution

Larsen helps companies analyze CPU consumption and optimize SQL queries as application requirements change. For example, as one of Larsen’s clients was preparing to launch four new applications, Larsen evaluated database performance, using IBM Data Studio software to help accelerate query tuning.

“IBM Data Studio enables me to be a magician in query-workload tuning,” Larsen says. “This company has 20,000 tables, with hundreds of millions of rows and IBM Data Studio helped me make quick decisions about the SQL code and the index design.”

In reviewing resource consumption, she made dozens of index design changes and SQL re-writes, and recommended SQL optimization techniques to be implemented in the first four weeks.

These tuning recommendations were implemented and, Larsen says, the company reduced query response times from 4,000 CPU-seconds to .0007 CPU-seconds—a nearly 100 percent reduction.

“This company needs to analyze years of data, in many different ways, on an hourly basis,” she says. “There are billions of ‘getpage’ requests per day, per query, and we found that these requests were number one in resource consumption and took resources away from transactional processes.”

Also by moving the power analytic users to their own stand-alone IBM Netezza® data warehouse appliance, the company reduced the load on its mainframe by 500 million instructions per second (MIPS), even as four new applications went live.

“The client increased the volume of DB2 database transactions by 20 percent, while reducing CPU load by 25 percent,” Larsen says. “Business users get their questions answered quickly, accelerating organizational decision-making, and the company saved USD5 million by avoiding having to upgrade the mainframe to support these new business applications.”

Part of Larsen’s work is also showing her clients the benefits of upgrading to new releases of DB2 for z/OS® software.

“I have a list of new DB2 10 capabilities that companies should implement,” says Larsen. “There are tremendous benefits—both in terms of improved performance and cost savings—by upgrading to the latest version of DB2 software.”

The benefit

  • Runs 20 percent more queries while incurring 25 percent less CPU load
  • Saves USD5 million by avoiding an upgrade to a mainframe server
  • Accelerates application performance, reducing some queries from 4,000 to .0007 CPU-seconds

For more information

To learn more about IBM DB2 for z/OS or IBM Data Studio software, please contact your IBM representative or IBM Business Partner, or visit the following websites:

Products and services used

IBM products and services that were used in this case study.

Hardware:
System z: System z10 Enterprise Class (z10 EC)

Software:
DB2 for z/OS, IBM Data Studio

Legal Information

© Copyright IBM Corporation 2013 IBM Corporation Software Group Route 100 Somers, NY 10589 Produced in the United States of America March 2013 IBM, the IBM logo, ibm.com, DB2, Netezza, System z10, and z/OS are trademarks of International Business Machines Corp., registered in many jurisdictions worldwide. Other product and service names might be trademarks of IBM or other companies. A current list of IBM trademarks is available on the web at “Copyright and trademark information” at ibm.com/legal/copytrade.shtml This document is current as of the initial date of publication and may be changed by IBM at any time. Not all offerings are available in every country in which IBM operates. The performance data and client examples cited are presented for illustrative purposes only. Actual performance results may vary depending on specific configurations and operating conditions. It is the user’s responsibility to evaluate and verify the operation of any other products or programs with IBM products and programs. THE INFORMATION IN THIS DOCUMENT IS PROVIDED “AS IS” WITHOUT ANY WARRANTY, EXPRESS OR IMPLIED, INCLUDING WITHOUT ANY WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND ANY WARRANTY OR CONDITION OF NON-INFRINGEMENT. IBM products are warranted according to the terms and conditions of the agreements under which they are provided.