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