Article Archives » 2012 (Volume 3) » April (Issue 2)    

How McCormick Cut Its Database Size in Half with No Negative Effects

In addition to the two certainties in life — death and taxes — most IT professionals will say there’s a third: Your database will grow. And grow. And grow.

But database growth is actually a good thing because more data means that companies can make better and more informed decisions. For the IT organization, however, adding new data sources to an already bulging database evokes two important questions: Where does the business store this new data? And how long will it take to retrieve all of this data and back it up? 

The IT team at flavorings maker McCormick & Co., Inc. recently faced those very questions. The company, which markets spices and flavorings through a number of consumer and industrial brands, was expanding both organically and through acquisition for much of the past decade.

As part of a new strategic direction, McCormick implemented SAP ERP in 2002 on top of an IBM DB2 database. From there, the company took a phased approach to expanding the breadth and depth of its SAP environment — bringing more businesses and production sites around the world onto the system and using more of the SAP functionality to achieve company objectives.

“We were expanding the scope of business processes that we use SAP software to manage, while bringing on new plants in new regions,” says Charlie Hoppa, Senior Manager of Archiving and Data Integration at McCormick. “As we brought more production sites and warehouses onto the SAP applications, we drove more data into our systems.”

McCormick was collecting more information from more sources up and down its global value chain. On one end of its supply chain, McCormick’s SAP Customer Relationship Management (SAP CRM) application was collecting more data about customers and sales — while on the other end, its SAP Supply Chain Management (SAP SCM) application was generating data about suppliers, materials, and warehousing. And in between, streams of valuable data were coming in from manufacturing, production, and processing systems.

The IT team wasn’t only concerned about storage space. As McCormick’s IBM DB2 database grew, so did the time required to back up the database — it took as long as 24 hours to complete a full backup in some cases. As backup time continued to increase, the team began to run out of time in a day to complete the effort before the next backup cycle. To solve this immediate dilemma, the team started to manage database growth through row compression.

“There comes a time when you can’t just keep buying more storage,” says Hoppa. “You have to find another way to manage storage requirements driven by your data growth.”

To find this other way, McCormick’s IT team began exploring its options for managing the size and growth of its database.

Discussing Compression

The team identified data compression as a possible solution to McCormick’s data dilemma. The previous version of the company’s IBM DB2 database allowed for the offline compression of data rows only. But when the company researched and evaluated IBM DB2 version 9.7, it found that the updated solution offered the ability to compress indexes in the database as well, and do it online instead of having to take a table offline during compression.

Compressing data can bring rewards as well as risk. The rewards come from mitigating risk associated with not having a regular backup and reducing the amount of space required to hold the same amount of data. The risk, however, is a loss of quality or performance with the compressed database. Before deciding that database compression was the right answer for McCormick, the team wanted to know confidently that database performance would not be negatively affected.

As Kingsley Nwafor, Database Manager at McCormick explains, SAP provided the company with the tool, available to all SAP customers running IBM DB2, to perform that evaluation. “SAP gave us a script that we ran against all of the database tables to get an estimate of how much space we could get back from compression and how long it would take to perform a compression on each table,” he says. “So we identified the most suitable compression candidates using that script and got estimates on how much we could reduce the database size, how much space we could get back, and what the possible impact on performance might be.”

The analysis showed McCormick could get back significant space in its database through compression with improved performance — so the IT team chose to go forward and compress the IBM DB2 database.

Replicating Benefits

The result of the database compression was dramatic, according to Hoppa, as the database size reduced by almost 50% and did not affect database performance. And on top of the space it saved, it also shortened the amount of time required to back up each database instance.

“Before the compression, our backup was taking 24 hours,” says Nwafor. “After the compression, that process was cut down to about 10 hours.”

When the process took over 24 hours, even regular daily backups were not possible. But now the business can back up its data regularly, which provides more security and more timely and useful data across the enterprise.

As Hoppa explains, the impact of compression extends beyond the main production database because of the number of copies of the database the company maintains. “For every one terabyte (TB) of space we allocate to that SAP system, there might be another 4, 5, or 6TB behind the scenes to support it,” he says. “When you compress a database, you haven’t removed or reduced the quantity of data, you just reduced the space required to hold that data. So reducing the amount of space required for one database by 1TB, we’ve reduced our overall space required by 6TB. And that’s where a lot of the benefit is.”

Data compressions bring with them associated cost savings as well. For starters, McCormick will spend less on storage space and maintenance to house the multiple versions of the growing database. Also, the reduced backup time and dollars saved makes the IT organization more efficient, putting more resources to use into other areas.

“When you invest in your data storage, you’re taking that money out of somewhere else in your company,” says Hoppa. “Becoming more efficient in your technology allows the business to invest capital in growth areas — so this project is a good example of how IT can help the business grow.”

The SAP Ecosystem at Work

The benefits McCormick is realizing are due in part to the close partnership between SAP and IBM, a contributing factor to McCormick’s decision to pursue the data compression strategy. The database and the applications that sit on top are closely intertwined and must perform in lock step with each other. If the providers of each are not on the same page, it can complicate problem-solving for the end customer.

The relationship between SAP and IBM helped McCormick better understand its options and also addressed any technical questions or concerns with the compression process along the way.

“Sometimes we get both SAP and IBM on the phone together to ask for a solution to a specific question,” says Hoppa. “We’ve seen the relationship between SAP and IBM become even closer and have definitely benefited from that.”