Outsourcer boosts database performance by up to 85 percent

With advanced data management and administration tools from IBM

Published on 31-Oct-2013

"With IBM® DB2® Query Monitor for z/OS® and IBM InfoSphere® Optim™ Query Workload Tuner for DB2 on z/OS, we can proactively optimize performance and efficiency while saving significant man-hours in administration." - Spokesperson, a business process outsourcing company

Customer:
A business process outsourcing company

Industry:
Professional Services

Solution:
Data Warehouse, System z Software

Overview

This large provider of business process outsourcing (BPO) solutions offers services covering human resources, payroll, tax and benefits administration to businesses of all types and sizes. Naturally, the efficient, timely and secure processing of data is central to the company’s business success. For more than a decade, the company has relied on IBM DB2 for z/OS to handle its largest and most important databases.

Business need:
The amount of data this BPO company manages and processes on behalf of its clients is continuing to grow exponentially, driven by richer data sets, increased regulation and a growing appetite for analytics and reporting. To ensure both performance and efficiency in data processing and reporting, and to keep its small team of DBAs free to focus on strategic initiatives, the company needed to monitor and optimize queries.

Solution:
Deployed IBM® DB2® Query Monitor for z/OS® and IBM InfoSphere® Optim™ Query Workload Tuner for DB2 on z/OS. These tools run on an IBM zEnterprise® 196 server, alongside 30+ major databases containing 17 TB of data on IBM DB2 10, supporting 20+ production systems.

Benefits:
Accelerates processing of complex queries by as much as 85 percent. Enhances client satisfaction by enabling faster and richer reporting and analytics. Enables small team of DBAs to do more in less time, freeing them from routine tasks to focus on strategic initiatives.

Case Study

This large provider of business process outsourcing (BPO) solutions offers services covering human resources, payroll, tax and benefits administration to businesses of all types and sizes. Naturally, the efficient, timely and secure processing of data is central to the company’s business success. For more than a decade, the company has relied on IBM DB2 for z/OS to handle its largest and most important databases.


“Today, we have 30+ major databases running on DB2 10, supporting more than 20 production systems nationwide,” says a company spokesperson. “The upgrade from DB2 9 was seamless – we almost had to double-check that it had actually taken place.”

Staying ahead of the curve
As the company’s client base has grown, so has the amount of data it manages and processes. In recent years, the growth in data volume has become exponential, driven by richer data-sets, increased regulation and clients’ growing appetite for analytics and reporting. To stay ahead of the curve and ensure both performance and efficiency in data processing and reporting, the company needed to improve its ability to analyze and optimize database queries.

The spokesperson says, “We are seeing more requests from clients for information, including dynamic ad-hoc requests. Our internal budgets aren’t growing in line with these demands, so we needed a smarter approach that would enable us to achieve more using fewer resources. With IBM® DB2® Query Monitor for z/OS® and IBM InfoSphere® Optim™ Query Workload Tuner for DB2 on z/OS, we can proactively optimize performance and efficiency while saving significant man-hours in administration.”

The company runs up to 3,000 queries each day, and has recently seen a major upswing in demand for ad-hoc transactions. For example, a client will request a report showing employees that have a particular set of characteristics, and an employee of the business process outsourcing company will then build the corresponding query. The IT team must then attempt to debug and tune these queries.

“We were spending too much time on fire-fighting, and too little on proactively tuning and optimizing performance,” says the spokesperson. “Unless the user really knows what they are doing to code these queries, you can end up with jobs that run for hours and really destroy the online performance – so that a DBA has to manually intervene and shut the job down. Of course, clients need the data, so we still have to run these queries. The question then becomes, ‘How much time and effort is it going to take to make the query efficient?’ Any kind of tool that can make the process more efficient is beneficial.”

With InfoSphere Optim Query Workload Tuner for DB2 on z/OS, the company has improved the quality of queries, minimized the need for reactive database maintenance, and accelerated processing by 80 percent or more.

“Optim Query Workload Tuner not only helps with the fire-fighting but also helps us proactively tune queries before they go into production,” says the spokesperson. “The Optim solution gives us advice on how to tune our queries, and that has helped us cut query running times from hours to minutes. In less than a day from start to finish, we took one particular problem, found the solution, and saved 80 percent of the processing time - that’s unreal!”

Pinpointing issues
The company employs DB2 Query Monitor for z/OS to generate a daily report to identify poorly performing jobs with high CPU usage, and to flag up support calls for over-running jobs. This prioritizes the workload for the team of DBAs, who work through the list using DB2 Query Monitor and Optim Query Workload Tuner to evaluate the jobs.

“With DB2 Query Monitor, we can quickly drill down and find the problematic SQL code within each job,” says the spokesperson. “Optim Query Workload Tuner then provides advice to help us rapidly identify possible improvements.”

The company’s developers have formed an internal group that reviews all new queries using the Optim solution before they are released into production.

“Some of our queries are long and complex, and we can’t afford to spend hours or days analyzing each one – not when we have tens of thousands of programs to support,” says the spokesperson. “Previously, figuring out how to tune a five-page query could take a week or more for a DBA. With Optim Query Workload Tuner, it takes us less than a day. That’s a major saving, and means that each DBA can accomplish significantly more work in the same time as before. It also means that we are preventing fires and reducing our future troubleshooting workload.”

The man-hours of skilled IT time that the company is saving can be used for more strategic initiatives such as providing new mobile and business intelligence services to internal users and external clients.

Adding value to client relationships
By accelerating the delivery of information with IBM DB2 for z/OS, the company is helping its clients to run their businesses more efficiently and to make better decisions faster, driving up satisfaction rates and strengthening its relationships with them.

“Having worked on all the major platforms and database management systems, I’ve never seen more support, more stability and more scalability for a database than for DB2 10 for z/OS,” says the spokesperson. “And with DB2 Query Monitor and Optim Query Workload Tuner, we are able get the right information to our clients faster than before, and with more efficient use of our limited internal resources.”

Products and services used

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

Hardware:
System z, System z: System z running z/OS, System z: zEnterprise 196 (z196)

Software:
DB2 for z/OS, DB2 Query Monitor for z/OS, InfoSphere Optim Query Workload Tuner for DB2 for z/OS

Operating system:
z/OS and OS/390

Legal Information

© Copyright IBM Corporation 2013. IBM Corporation, Software Group, Route 100, Somers, NY 10589. Produced in the United States. October 2013. IBM, the IBM logo, ibm.com, DB2, InfoSphere, Optim, z/OS and zEnterprise 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 client examples cited are presented for illustrative purposes only. Actual performance results may vary depending on specific configurations and operating conditions. 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.