SAP deploys IBM DB2, saves costs and boosts performance

Published on 21-Dec-2011

Validated on 18 Jul 2013

Customer:
SAP AG

Industry:
Computer Services

Deployment country:
Germany

Solution:
Database Management, Enterprise Resource Planning, Information Infrastructure, Optimizing IT

IBM Business Partner:
SAP

Overview

SAP AG headquartered in Walldorf, Germany, is the market leader in enterprise application software. It wanted to make use of numerous functional enhancements available in the latest version of its applications to drive business efficiency. Furthermore it was essential to ensure that system performance continued to improve even as the workload and data volumes increased, meeting the company’s targets for return on investment and reduced total cost of ownership.

Business need:
Customer Objectives included: Project one:Introduce a scalable architecture capable of handling massive data growth and provide optimal performance. Further improve SAP® application performance and increase productivity by applying the DPF of DB2 for Linux, Unix and Windows. Project two: Reduce data storage needs and cut expenditure on storage devices and management. Improve overall system performance. Reduce maintenance windows for database administrative work

Solution:
Project one: Migrate from the existing non-IBM database onto IBM DB2. Implement DB2 Database Partitioning Feature to further improve performance and performance during long-term system growth. Introduce Unicode conversion for worldwide language support. Upgrade to the latest application level for new functionality and benefits. Project two: Exploit deep compression (row compression) to reduce data storage requirements and improve overall system performance. Reduce storage operations load and make more efficient use of database memory pools.

Benefits:
Project one: “Shared nothing architecture” of DB2 DPF helps ensure continuous performance over growth. Integrated DBA Cockpit provides ease-of-use, reduces complexity, and cuts the effort required for database administration. Combining non-IBM-to-DB2 database migration and Unicode conversion simultaneously reduces project time and costs. Project two: Overall performance increase of about 30 percent as a result of data compression. Cut total storage requirements by more than 50 percent on average, helping to avoid expensive hardware upgrades in the midterm.

Case Study

About this paper

SAP AG headquartered in Walldorf, Germany, is the market leader in enterprise application software. It wanted to make use of numerous functional enhancements available in the latest version of its applications to drive business efficiency. Furthermore it was essential to ensure that system performance continued to improve even as the workload and data volumes increased, meeting the company’s targets for return on investment and reduced total cost of ownership.

To accomplish these goals, the SAP IT team defined the corporate objectives to update mission-critical business systems to the latest software levels, and migrate from the existing non-IBM database to IBM DB2. As part of this initiative the internal use of the SAP NetWeaver® Business Warehouse (SAP NetWeaver BW) component of the SAP NetWeaver® technology platform was upgraded from version 3.1 to 7.0 and migrated from the non-IBM database to DB2. SAP decided to make use of the DB2 Database Partitioning Feature (DPF) and DB2 compression to help further ensure reliability and performance levels in the company’s fast-growing business-warehouse system.

This paper outlines the database migration, Unicode conversion, and subsequent introduction of data-compression technologies undertaken by SAP in 2007-2009. In practical terms, SAP split the work into two projects. The first project covered the migration from the non-IBM database to DB2 for Unix and Windows, the upgrade to SAP NetWeaver BW version 7.0, and the Unicode conversion. The second project comprised the activation of DB2 data compression, which implied a system copy.

Customer Objectives
Project one:

  • Introduce a scalable architecture capable of handling massive data growth and provide optimal performance
  • Further improve SAP® application performance and increase productivity by applying the DPF of DB2 for Linux, Unix and Windows

Project two:
  • Reduce data storage needs and cut expenditure on storage devices and management
  • Improve overall system performance
  • Reduce maintenance windows for database administrative work

Solution
Project one:
  • Migrate from the existing non-IBM database onto IBM DB2
  • Implement DB2 Database Partitioning Feature to further improve performance and performance during long-term system growth
  • Introduce Unicode conversion for worldwide language support
  • Upgrade to the latest application level for new functionality and benefits
Project two:
  • Exploit deep compression (row compression) to reduce data storage requirements and improve overall system performance
  • Reduce storage operations load and make more efficient use of database memory pools

Customer Benefits
Project one:
  • “Shared nothing architecture” of DB2 DPF helps ensure continuous performance over growth
  • Integrated DBA Cockpit provides ease-of-use, reduces complexity, and cuts the effort required for database administration
  • DB2 DPF enables SQL queries to be processed in parallel with near-linear scalability
  • Combining non-IBM-to-DB2 database migration and Unicode conversion simultaneously reduces project time and costs

Project two:
  • Overall performance increase of about 30 percent from the same IT systems infrastructure as a result of data compression
  • Reduced backup maintenance window by 60 percent as a result of data compression and subsequent smaller backup images
  • Cut total storage requirements by more than 50 percent on average, along with a slowdown in data growth, helping to avoid expensive hardware upgrades in the midterm


Background, starting point and objectives
SAP serves international businesses of every conceivable type. Naturally, SAP uses its own software products to manage its internal business processes, in Germany and in offices on every continent, which require local language support.

In this project example, one of the instances of SAP NetWeaver BW is accessed by approximately 700 concurrent users. SAP NetWeaver BW resided on 12 application servers, with about 500 connections to a database server running non-IBM software.

In common with many large enterprises, the SAP IT team was determined to find ways to drive down hardware and operational costs by reducing IT infrastructure complexity and cutting the associated administration and maintenance costs. At the same time, the team wanted to further improve SAP application performance and increase user productivity.

In parallel, increased use of analytic tools was driving data growth, and the SAP IT team looked for ways to reduce storage needs and cut expenditure on storage devices and management. By reducing complexity and shrinking storage volumes, the aim was to remove workload from database administrators and increase productivity.

Finally, knowing that transaction workload was likely to rise, the team wanted to introduce an IT architecture capable of handling massive data growth and continue to provide optimal performance without causing a re-growth in complexity or expense.

SAP IT decided to split these various efforts into two projects, principally to limit the volume of change required to the production systems. Project one was restricted to the introduction of DB2 and the DB2 DPF functionality in conjunction with an SAP technical upgrade and Unicode conversion. Project two introduced the DB2 storage optimization feature, known as DB2 Deep Compression.

Project one – SAP upgrade and Unicode on DB2 DPF
SAP is committed to using the latest versions of its own products, including SAP NetWeaver BW. In this project, SAP migrated to SAP NetWeaver BW 7.0 and SAP NetWeaver 2004, and included a Unicode conversion and migration from the non-IBM database to DB2. With the use of DB2 DPF for its business warehouse solution, SAP IT was able to improve query performance instantaneously and had the opportunity to scale out on the basis of a “shared nothing architecture,” achieving near-linear scalability by adding additional balanced resource building blocks of memory, CPU and storage.

Project two - Harnessing DB2 Deep Compression
SAP decided to make use of DB2 Deep Compression to manage data growth, improve overall system performance, and reduce storage requirements. Project two focused solely on the implementation of DB2 Deep Compression (row compression activated by exporting and importing SAP NetWeaver BW data). SAP IT was able to perform the migration of the 8 TB database within one weekend by using the standard SAP migration tools and applying additional functions of R3load, such as table and package splitter, to improve migration performance.

Project one:
Project one consists of two major tasks: the migration from the non-IBM database to DB2 with Unicode conversion in one step, and subsequently the technical upgrade from SAP NetWeaver BW 3.1 to 7.0.

IBM DB2 Database Partitioning Feature
IBM DB2 DPF allows large OLAP databases to be scaled out on one or multiple servers to improve performance, offering near-linear scalability, very efficient load times, and a predictable scalability curve for future growth.

The SAP NetWeaver BW workload, with its table scans over millions of rows per query, is a perfect fit for DB2's DPF scale-out approach. DPF is designed to boost query (read) performance by optimally using the pooled system resources from one or more servers. DPF is a unique feature of DB2, and is supported by SAP for SAP NetWeaver BW solutions.

With DPF, DB2 distributes the query to the database partitions to be processed in parallel. This parallel processing is achieved by splitting a database into partitions, each of which has its own set of computing resources, including CPU, memory and storage. This concept is also known as “shared nothing architecture.” The database data itself is striped across the multiple partitions of the DPF database for optimal parallel processing. There are several ways to implement DPF: on a single server with logical partitions or on multiple physical servers (physical partitioning).

DB2 DPF at SAP IT
SAP IT decided to implement the DB2 DPF capability for SAP NetWeaver BW on a single multiprocessor (MP) server using logical partitioning.

A DPF database consists of one database instance, including the database manager configuration and multiple data partitions. The individual partitions have their own dedicated resources including memory and data.

The following DPF configuration was used at SAP IT: 10 partitions with nine data and one catalog partition. The number of partitions where calculated based on the number of CPUs and the applied rule of thumb is outlined in table 1. Note: the CPUs aren’t dedicated to the partitions, whereas the memory and the storage are.

ResourcesCatalog PartitionData Partition
CPU4-81-4 (each partition)
RAM~25% of server memory~70% of server memory (for all partitions)
Table 1: Rule of thumb for CPU and RAM layout of DB2 DPF on MP servers

The catalog partition applies for more resources because it has a higher workload than the database partitions. On the one hand it holds the database catalog and the dimension tables, which are single resources, and on the other hand the SAP application servers log in to the catalog partition and issue their queries. The query itself will then be distributed for execution to the data partition candidates, and the results from the data partitions are sent back and merged on the catalog partition. The ODS, PSA and Fact tables are distributed across the data partitions with an algorithm using one hash function. When a query is processed, the request is distributed so each database partition processes the data rows for which it is responsible.

Essentially, DPF is a scalability feature that can maintain consistent query performance as the workload grows by adding processing power in the form of additional database partitions. Scalability means that a larger number of requests can be handled with more resources in the same time. This capability is often referred to as providing linear scalability using DB2's “shared nothing architecture.”

Leveraging synergies: SAP’s Unicode conversion and DB2 migration
The following sections outline the methodology used for the database migration and simultaneously Unicode conversion. The methods used helped SAP IT to shorten the migration project time and reduce the total downtime.

There are four migration approaches, and three include synergies that a combined Unicode conversion and database migration provide.

Migrating to IBM DB2 as a single project including both SAP software and Unicode conversion leverages an opportunity to eliminate redundant project tasks, such as database export and import. These synergies mean that the cost of the single migration project is considerably lower than three separate projects. This type of combined migration adds very little extra project time and no significant additional downtime. Since DB2 version 9.1, this approach can become even more beneficial, by activating DB2 compression in the corresponding phase. However SAP IT has decided for a more conservative approach and enabled DB2 compression in a subsequent step, which is also part of this paper and described in project two.

Combining the database migration with the Unicode conversion allows sharing the same downtime to complete the two work streams. This approach has been deployed at numerous customers, proving its feasibility and reliability.

Migrating SAP NetWeaver BW on non-IBM database to DB2 DPF with concurrent Unicode conversion
SAP IT used the standard SAP methodology for the migration process, known as the heterogeneous system copy (or OS/DB migration) method. SAP IT was able to perform the migration and conversion during a scheduled maintenance window, including the technical upgrade of SAP NetWeaver BW from 3.1 to 7.0. There was no need to make use of enhanced migration tools and services.

The downtime for the final migration of the 4.5 TB production database, including the Unicode conversion, was 48 hours. Because the migration was combined with an SAP upgrade, the total downtime was greater.

SAP IT used the following standard tools for heterogeneous system copies:

  • R3load, to export and import tables
  • Migration monitor, to control and run table exports and imports in parallel on one server
  • Distribution monitor, to control and run table exports and imports on more than one server in parallel

To improve the speed of the SAP system copy, multiple R3load processes were used to export and import the data in parallel. There are several options available to parallelize the data export and import, such as package and table splitter. SAP IT used the package splitter to export large tablespaces and tables in parallel by creating multiple packages where each package contains the data of either one tablespace or one table.

For very large tables, performance was improved by using the table splitter tool to divide a single table into several packages. Without this parallelization, exporting the largest single table could actually take longer than all the other table exports collectively, and, therefore, the overall export time would increase. For smaller tables, parallelization of the export and import processes usually does not offer any throughput advantage, and SAP IT decided to split only the largest tables.

SAP NetWeaver BW-specific migration tasks
Heterogeneous system copies of SAP NetWeaver BW differ from migrations of OLTP-based SAP systems. The reason is that SAP NetWeaver BW makes use of database platform-specific features to achieve maximum performance, such as DB2 DPF, which require special processing steps during the migration process.

Pre-migration step on the source system
To create the DB2 data structures for the SAP NetWeaver BW tables and indexes, a special report (SMIGR_CREATE_DDL) must be run on the system. This report ensures that indexes required in DB2 are created, and that the distribution algorithm for the PSA, ODS and fact tables on the DPF data partitions are defined.

Post-migration steps
After the migration, a specific report (RS_BW_POST_MIGRATION) needs to be executed on the target (DB2) system. This report adjusts the SAP data dictionary to the layout of the tables and indexes on the target system. It includes a number of additional mandatory repair operations that are necessary, because the SAP NetWeaver BW implementation on the source and target systems differs.


Project two:
The following chapter describes the implementation of the IBM DB2 storage optimization feature, and how SAP IT has benefited from this functionality.

IBM DB2 storage optimization feature
The nature of SAP environments requires a database that performs efficiently and cost-effectively. With the amount of data in SAP NetWeaver BW environments, the IBM DB2 Storage Optimization Feature not only contributes to storage cost savings, but also offers increased performance.

SAP IT has experienced storage reductions of around 54 percent and an average performance improvement of 30 percent in batch run times. Single jobs have shown performance improvements of up to 80 percent. As DB2 9.1 was implemented, only row (data) compression is supported, and the subsequent achievements therefore do not include index compression, available in DB2 version 9.7 onwards, which has provided additional storage savings for other customers in the range of 10 to 15 percent.

Overview of IBM DB2 row compression
In this paper, DB2 Deep Compression refers to row or records compression, which in general pays the greatest dividends on tables larger than 100 MB. The advantages are less storage space for the same data on disk, improved I/O throughput owing to reduced table sizes, more data per I/O block, and better memory usage as a result of it being possible to hold more data in the same amount of database memory.

DB2 Deep Compression is based on creating a dictionary of recurring strings or values in each table. Where the strings or values re-occur in the tables, the shorter dictionary key is stored in the row. No other compression algorithms are used. In the example given, the recurring row entries “opoulos” and “WhitbyONTL4N5R4” are replaced by the short dictionary keys 01 and 02.


IBM DB2 storage optimization feature fully supported by SAP software
DB2 row and index compression are fully supported by SAP software and are totally transparent to the application. For new SAP installations on DB2 LUW, the SAPInst tool offers the ability to apply compression at installation time if desired. Subsequently all tables and indexes are created with the compression flag.

There are different ways to create the compression dictionary.

One method is the Automatic Dictionary Creation (ADC), which is used during the SAP installation process. With ADC, DB2 starts to compress the data of the table after a certain threshold has been reached, usually around 2 MB. Once the dictionary is created, new data will be inserted in compressed format. This result is a compression dictionary, which may not be optimal because the patterns represent only a small subset of the data. However, for newly installed systems, this approach is sufficient, as most of the tables are still small.

As tables grow and the compression ratio decreases, it is possible to reorganize the tables, including the recreation of a more suitable dictionary. This can be done at any time after the installation, either via offline or online reorganization.

For migrations where the tables are already filled up with data, it is possible to use the “sampled load” to gain a better compression ratio than with ADC. This method is a two-step approach. In the first step, R3load imports only a sample of data into the tables, for example every hundredth row from the table, and the compression dictionary is created. In the second step, R3load is restarted and this time the complete set of data is loaded and compressed by applying the dictionary that was built in step one, which was based on representative data and thus produces an optimal compression ratio. SAP IT employed the “sampled load” approach during the migration process to achieve the best compression ratio.

(For more information, see RedBook DB2 Optimization Techniques for SAP Database Migration and Unicode Conversion. http://www.redbooks.ibm.com/abstracts/sg247774.html)

Enabling DB2 Deep Compression by using heterogeneous system copy
The available SAP IT downtime window, spanning over a weekend, was sufficient to utilize the standard heterogeneous system copy to enable DB2 row compression. The migration was completed within the weekend (source database size 8 TB uncompressed and target database size 3.6 TB compressed), running export and import concurrently, and several of the larger tables were imported and exported in parallel. This parallelization was completed using the table splitter, which split single tables into chunks that were exported and imported in parallel, accelerating the procedure. This migration followed the same approach as the non-IBM-to-DB2 migration in project one, described in the previous chapter.

Results of Deep Row Compression at SAP IT

  • Database size reduced by < 54 percent
  • Performance improvement by ~ 30 percent
  • Improved I/O throughput by ~ 30 percent
  • Backup space savings of ~ 52 percent
  • Backup time improved by 64 percent


Database size cut by over 50 percent
Row compression was enabled by R3load, and 1,600 of the largest tables were compressed with a downtime of less than 24 hours. Compression produced immediate savings in data storage requirements and delivered significant performance increases:

Compression ratio formula: (1 – 3.64:7.97)*100 = 54.32 percent

The space savings of individual tablespaces and tables have even been higher. The largest tablespace “IWP#ODSD” was compressed from over 2.5 TB to 500 GB, which is a compression rate of over 80 percent (a factor of five).

The below list is an excerpt of the largest tables, which showed compression rates up to 79 percent.

TABNAMESIZE before compression (GB)SIZE after compression (GB)STORAGE_SAVINGS (PERCENT)
/BIC/FPPROCSCOV572949
/BIC/EPPROC_LOC542945
/BIC/EPCRMSOPHI461469
/BIC/EPPROCCHKO402244
/BIC/FPPROC_TNE34779
/BIC/EPPROCST0N251540
/BIC/FPPROCST0N211338
/BIC/FPPSPAC06D20478
/BIC/EPPROC_TNE19667
/BIC/FPPROC_LOC181047
Table 2: Size of ten large tables at SAP IT before and after compression with compression rate

Boost backup times cut by more than 60 percent
Rather than taking 14 hours, SAP IT is now able to back up the system in five hours, a reduction of 64 percent, almost a factor of three. Not only has the backup time decreased, but also the size of the backup, which was cut by half. Another side aspect of compression is that the I/O per second decreased by 31 percent on average.

SAP IT also took the opportunity to optimize the database layout during the migration, which directly affected the backup performance. As DB2 backs up tablespaces in parallel, the largest tablespace often determines the run time of the backup. This behavior was mitigated by moving some of the largest tables to separate tablespaces, and enabling compression which resulted in smaller tablespaces and decreased backup time.

SAP IT saved around 132 TB of disk space in its backup infrastructure, as SAP IT is keeping 30 versions of the backup images of the SAP NetWeaver BW system.


Before CompressionAfter CompressionSavings
Backup image size
~ 8 TB
3.6 TB
~54 %
Backup time
14 hrs
5 hrs
~ 64 %
Overall backup space
~ 240 TB
108 TB
~ 132 TB
Table 3: Backup improvements in size and time after compression


Speed up batch jobs on average by 30 percent
Not only was the total backup requirement reduced by 64 percent, but the total run time of the nightly batch chains improved by 30 percent. Table 4 outlines the run time of a job chain of a single day (job chain for extract, transformation and load) before and after compression. This list shows that DB2 compression delivered tremendous improvements in run-time performance for both short and long jobs. The longest-running job in the chain, PSCA00_SCA_ALL_DATA, was cut by a factor of three from about 18 hours to 6 hours, a 66 percent saving. The reason for the performance improvement with DB2 compression is that the data stays compressed not only on disk but also in the buffer pool, and is only decompressed during execution. This results in a more effective usage of memory and more efficient I/O behavior. SAP IT discovered that the overall performance improvement for the complete job chain was 30 percent, or a saving of over 12 hours in run time.


Chain of batch jobs* - one dayRun times
w/o compression
Run times
with compression
Run-time savings
in %
BW_CORE_CRC_MITNIGHT
1 hr 7 min
41 min
38,7
BW_CORE_CRC_MORNING
5 min
2 min
52,7
YCRMATSAP_TRANSACT_D_NEW
13hrs 47min
14 hrs 51 min
-7,7
PCA_DATA_UPLOAD
20 min
8 min
59,4
COPA_DAILY_UPLOAD
53 min
44 min
15,4
PPROC_TESTREP_MA
6 hrs 7 min
4 hrs 9min
32,0
PPROC_TESTREP_GT_MA
9hrs 23 min
10 hrs 46min
-14,8
PSCA00_SCA_ALL_DELTA
17 hrs 53 min
6 hrs 7 min
65,8
CGFOD_TRANSACT_XHOURLY_N
4 hrs 48 min
53 min
81,4
Summary
54 hrs 27 min
38 hrs 26 min
29.4
* The single jobs of the chain can be parallelized, therefore the total run time in summary line exceeds 24 hours.
Table 4: Run times of batch chain before and after compression provided by SAP two weeks after migration

I/O throughput
Data compression improves I/O performance by reducing the MB read/write per second and the numbers of I/O requests per second. This is achieved by storing a larger amount of data (compressed) on fewer DB2 data pages. The result is that with one I/O request, more data can be read and written.

An analysis of data throughput in MB (read/write) per second with and without compression, measured over a time period of seven days demonstrates that the data transfer rate of the same data in compressed format was reduced by 44 percent compared with the uncompressed data. Peaks measured represent the times where the DB2 backups were taken and where the desired I/O behavior differs from the normal work profile. During a backup, as much data as possible is read in as short a time as possible, so that the complete bandwidth of the storage infrastructure will be used. Therefore seeing these peaks is expected and desirable behavior. With compression, these peaks are lower because the backup runs more quickly.

The I/O operations per second (IOPS) with and without compression over seven days follow the same pattern as the I/O throughput in MB (read/write), showing that using compression results in fewer I/Os per second and the peaks are fewer and lower. The remaining peaks represent the times of the database backups. Overall, SAP IT has seen that with compression, IOPS is reduced by 30 percent with the same workload and with shorter run times. With this savings in data rates as well as in IOPS, DB2 data compression releases headroom to allow growth in I/O performance, either to overcome a potential I/O bottleneck or to maintain existing I/O infrastructure, even as workload grows.

Project achievements and future plans:
The changeover of SAP NetWeaver BW was part of an overall strategic corporate goal to take advantage of new SAP application functionality, facilitate international business operations, reduce complexity and cut operational costs of the IT landscape. On the basis of the benefits gained by deploying DB2 for its SAP NetWeaver BW-based landscape, SAP successfully updated all its mission-critical systems to the latest application levels, converted to Unicode, and migrated to DB2.

The company’s core business intelligence solution, SAP NetWeaver BW, covering operational, financial, group and legal reporting, fulfilled the achievements of handling data growth and providing optimal performance with the DB2 DPF scale-out approach. By implementing DB2 row compression, SAP was able to cut planned expenditure on storage devices and management. By optimizing I/O performance by more than 30 percent, SAP improved overall system performance by as much as 30 percent and reduced maintenance windows for database backup by more than 60 percent.



SAP, SAP NetWeaver and all SAP logos are trademarks or registered trademarks of SAP AG in Germany and in several other countries.
All other product and service names mentioned are the trademarks of their respective companies.

Products and services used

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

Software:
DB2 for Linux, UNIX and Windows, DB2 Database Partitioning Feature

Service:
IBM-SAP Alliance

Legal Information

© Copyright IBM Corp. 2011. All rights reserved. IBM Deutschland GmbH D-70548 Stuttgart ibm.com Produced in Germany December 2011 IBM, the IBM logo, ibm.com, and DB2 are trademarks of International Business Machines Corporation in the United States, other countries, or both. If these and other IBM trademarked terms are marked on their first occurrence in this information with a trademark symbol (® or ™), these symbols indicate U.S. registered or common law trademarks owned by IBM at the time this information was published. Such trademarks may also be registered or common law trademarks in other countries. A current list of other IBM trademarks is available on the Web at: http://www.ibm.com/legal/copytrade.shtml UNIX is a registered trademark of The Open Group in the United States and other countries. Linux is a trademark of Linus Torvalds in the United States, other countries, or both. Microsoft, Windows, Windows NT, and the Windows logo are trademarks of Microsoft Corporation in the United States, other countries, or both. Other company, product or service names may be trademarks, or service marks of others. This brochure illustrates how IBM customers may be using IBM and/or IBM Business Partner technologies/services. Many factors have contributed to the results and benefits described. IBM does not guarantee comparable results. All information contained herein was provided by the featured customer/s and/or IBM Business Partner/s. IBM does not attest to its accuracy. All customer examples cited represent how some customers have used IBM products and the results they may have achieved. Actual environmental costs and performance characteristics will vary depending on individual customer configurations and conditions. This publication is for general guidance only. Photographs may show design models. SAP, SAP NetWeaver and all SAP logos are trademarks or registered trademarks of SAP AG in Germany and in several other countries. All other product and service names mentioned are the trademarks of their respective companies.