Top 5 New DB2 10 for z/OS Features Aimed at SAP Applications

September 21, 2010

As IBM draws ever nearer to announcing an availability date for DB2 10 for z/OS, the list of new features has solidified and it’s possible for specific types of user to begin the process of evaluating just what will be of benefit to them. SAP remains a vital strategic partner for IBM, and DB2 10 has a large number of features aimed specifically at SAP applications running under DB2 for z/OS. In this month’s piece, we’ll examine my top five.

64-Bit Exploitation

In DB2 Version 8, IBM embarked upon a major project to transform DB2 into a 64-bit RDBMS, removing many of the addressability issues inherent in the previous 31-bit memory model. DB2 Version 8 also moved several key storage areas above the 2GB “bar” into the newly-addressable memory space, relieving some of the storage constraints and allowing more workload per DB2 subsystem. DB2 9 increased scalability by a further 10 percent to 15 percent by moving another set of storage areas above the bar, but even with those enhancements, most customers have been limited to running a maximum of 400-500 concurrent active connections within each DB2 system. As a result, many DB2 data sharing customers were forced to use more DB2 members than otherwise necessary in order to support their workloads. Although DB2’s industry-leading data sharing architecture minimizes the processing overheads, each additional member will impact overall performance and resource usage.

Figure 1 below shows a typical scenario for a SAP environment. In this example, a data sharing group consisting of four DB2 members is used in order to support 1,600 concurrent threads from four SAP application servers.

Figure 1 – Typical SAP Data Sharing Configuration

In DB2 10, IBM has completed the bulk of the remaining work in the 64-bit migration effort, with 80 percent to 90 percent of the remaining DB2 storage structures moving above the bar. This has enabled a spectacular increase in the number of threads that can be supported by a single subsystem – most customers will be able to achieve 5-10 times the number of concurrent connections compared to DB2 9.

This will allow many customers to reduce the number of DB2 members needed to support their workloads, resulting in net CPU and memory savings and improving application performance.

Figure 2 below shows that the same 1,600 thread workload can be handled by just two DB2 subsystems, with significant scope for additional workload growth (initial SAP benchmarks show 2,500 threads per DB2 system is sustainable).

Figure 2 – Potential DB2 10 SAP Data Sharing Configuration

DB2 Catalog Restructure

The Catalog and Directory are some of the most vital components in any DB2 system. They contain a set of tables and internal structures that represent all of the metadata necessary for the subsystem to operate, and is used extensively by nearly every DB2 process from application programs to DBAs creating a new database.

DB2 10 includes some important enhancements to the Catalog, which will be very welcome to SAP users. These include:

• Standard UTS format. In previous releases, the catalog used a non-standard internal storage format and maintained links between the various tables using special internal pointers. DB2 10 converts some key Catalog structures to use the standard Universal Table Space Partition by Growth (UTS PBG) format introduced in DB2 9. This change allows standard online REORG processes to be used against the catalog, improving performance and availability compared to previous releases.

Contention between various processes needing to read/update the Catalog can cause significant operational disruption. As part of the same change, the converted Catalog tables have been spread across many more table spaces than before, and row level locking has been implemented to allow DB2 10 to support much higher levels of current access than was previously possible.

• Maximum Catalog Size. Many DB2 subsystems have to support hundreds of thousands of database objects. Each of these objects must be recorded in the DB2 Catalog and in some cases the 64GB limit for certain components such as the SPT01 table space can be a scalability limitation. DB2 10 addresses the 64GB limitation on the SPT01 table space by moving some large columns into LOB (Large Object) columns. This makes use of the inline LOB support added in DB2 10, making the Catalog tables more readable and allowing more packages to be supported within a single DB2 system.

Anyone that has ever worked with SAP on DB2 for z/OS knows that it requires a very large number of DB2 objects to be created (and changed during SAP upgrades), and there can be catalog contention issues if several streams of DDL are running at the same time. The enhancements in DB2 10 promise to significantly reduce the frequency and impact of these issues.

Performance

Any new release of DB2 is expected to deliver some performance benefits, but DB2 10 has the most comprehensive and easily accessible package of enhancements of any release in the past 20 years. Here are a few that are of especial interest to SAP users:

• Internal code optimization. Parts of critical DB2 internal code (including DDF, RDS, Data Manager and Index Manager) have been reworked in DB2 10 to reduce pathlength and improve efficiency.  In addition, work has been done to improve CPU cache performance and take advantage of the latest System z hardware instructions. Collectively, these enhancements will provide significant “out of the box” savings to SAP users as soon at the DB2 10 upgrade is complete.
• Parallel Index INSERT. Many SAP workloads (such as Banking or Retail) involve heavy INSERT workloads. In such cases, the overheads of maintaining multiple indexes can be significant, and this can be the major factor in determining the overall response time and throughput for some major SAP business processes.

DB2 previously updated indexes sequentially but in DB2 10, it is able to overlap the I/O operations for index updates, reducing the elapsed time for these processes. In common with other parallel activities, reduced elapsed time is usually achieved at the cost of an increase in overall CPU time, but in this case, the overhead is very small and eligible for offload to a zIIP engine if one is available.

• Safe Query Optimization. The DB2 optimizer often has to make educated guesses on the amount of data that will be filtered by a given predicate in an SQL statement. Enhancements to the optimizer in DB2 10 allow it to take into account the degree of confidence it has with these estimates, allowing it to choose a slightly more expensive access path if it has significantly lower risk associated with it. This is just one of a number of optimizer enhancements, which will benefit SAP queries in DB2 10.

MEMBER CLUSTER for UTS Tablespaces

SAP has aggressively adopted the new Universal Tablespace Partition by Growth (UTS BPG) format introduced in DB2 9. This led to many productivity benefits, with SAP DBAs having to spend less time manually managing space allocations for rapidly growing tables. However, UTS tablespaces did not previously support the MEMBER CLUSTER option, which can be very important in a data sharing environment to reduce space-map contention during high INSERT activity. This meant that the poor DBAs had to convert many tables back to traditional partitioned format (using unload/drop/ create/reload as there was no ALTER support to move from UTS to partitioned).

DB2 10 allows MEMBER CLUSTER to be used for UTS tablespaces, removing the need to move back to the traditional partitioned format and saving a lot of DBA time in the process.

SAP DSNZPARM Support

SAP can be a demanding application, and comes with some very specific requirements for the way in which the supporting DB2 for z/OS system is installed and configured. Previously, this required the DB2 systems programmer to work through a long list of DSNZPARM and other system settings to ensure that the system was configured according to SAP’s specifications.

DB2 10 introduces a new install CLIST input member (called DSNTIDXB) which contains the SAP-recommended settings for DSNZPARMs, buffer pools and other configuration information. This will allow new SAP DB2 systems to be installed and configured much more rapidly than before.

Useful Links

• IBM DB2 10 Beta Page
• IBM Sap on DB2 for z/OS Page
• SAP home Page

» See All Articles by Columnist Julian Stuhler