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

As IBM draws ever nearer to announcing an availability date for DB2 10 for z/OS,
the list of new features has solidified. This month’s article examines the top five
features aimed specifically at SAP applications running under DB2 for z/OS.

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.

Typical SAP Data Sharing Configuration

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).

Potential DB2 10 SAP Data Sharing Configuration

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

  • 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

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.


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 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


See All Articles by Columnist

Julian Stuhler

Julian Stuhler
Julian Stuhler
Julian is a Principal Consultant with Triton Consulting, and has over 22 years relational database experience working in a number of clients within the insurance, telecommunications, banking, financial services and manufacturing sectors. In that time he has gained a significant amount of practical knowledge in many aspects of the IBM Information Management portfolio, including experience in application programming, Database Administration, technical architecture, performance tuning and systems programming. Julian is an IBM Redbook author and IDUG Best Speaker, and has lectured widely on DB2 subjects, UK, Europe and US. This includes presentations for the International DB2 Users Group (IDUG), Candle Performance Seminars, , BMC Seminars, and European GUIDE meetings. He is also a regular teacher for IBM throughout Europe. In 1999 Julian was invited to join the IBM Gold Consultants programme, used to recognize the contributions and influence of the world's 100 leading database consultants. In May 2008, Julian was recognized as one of IBM's inaugural Data Champions - a program to recognize individuals for outstanding contributions to the data management community. Julian joined the IDUG Board of directors in 2003 and is currently serving as the organization's Immediate Past President.

Latest Articles