Blogs

Skipping Ahead to IBM DB2 10 for z/OS

How Turkish Social Security (SSI) reduced CPU costs up to 23 percent and quadrupled capacity by upgrading to DB2 10 for z/OS

zAnalytics Technical Leader - MEA, IBM

The Social Security Institution (SSI) for the Republic of Turkey was established to provide Turkish citizens with equitable, easily accessible social and universal health insurance. The organization is responsible for implementing social security policy in accordance with national development strategies, coordinating public agencies in the social security sector, and collaborating with the European Union to establish international policy agreements.

In the early 1990s, the SSI implemented IBM® DB2® 2.2 for z/OS® software to create a resilient, highly available IT environment that could deliver critical support services. The organization continued to use the software over the next two decades, performing regular version upgrades.

Following the release of IBM DB2 10 for z/OS data server software in 2010, the SSI wanted to upgrade its DB2 8 for z/OS software directly to the latest version by using the technology’s skip migration feature. DB2 10 for z/OS delivers a number of business and “out-of-the-box” benefits, including increased performance and CPU efficiency, scalability improvements, and productivity enhancements.

For the SSI, the CPU reductions would provide an important advantage. Two of the organization’s three DB2 production subsystems were struggling to keep up with increasing workload demands due to virtual storage and CPU constraints. Those subsystems were in the capacity limits of version 8 and more workload was coming.

The project

In August 2011, the SSI launched its upgrade project, focusing on all necessary preparation work. The organization brought its 16 DB2 for z/OS subsystems up to current maintenance levels and addressed additional technical prerequisites. Beginning in January 2012, the organization successfully upgraded all subsystems to conversion mode (CM) with DB2 10 for z/OS. The process took four months. In August 2012, the organization completed its smooth transition to new-function mode (NFM).  In CM, it is easy to fallback to V8 if a major issue occurs because V10 functions and features are not used in CM. After the move to NFM is completed, there is no way to fallback to V8 again and V10 is used in full function.

Benefits

By eliminating the need for two single-version upgrades, the skip migration saved time and money for the SSI. The upgrade also delivered performance benefits well beyond the organization’s expectations. The organization decreased the CPU utilization per transaction by up to 23 percent. In addition, the new environment can handle four times the previous workload. At the same time, the upgrade has helped reduce timeouts and deadlocks, and it improves the use of zIIP for utilities.

V8 NFM V10 CM8 V10 NFM
Average thread footprint (mb) 1.16 0.46 0.42
Max. number of possible threads 589 2094 2312
DDF address space CPU /Commit 0.000465 0.000443 0.000387
System address space CPU/commit 0.000016 0.000016 0.000016
Database Manager CPU/commit 0.000049 0.000003 0.000003
Lock Manager CPU/commit 0.000005 0.000003 0.000003
Figure 1. Comparative metrics for DB2 8 NFM, DB2 10 CM, and DB2 10 NFM from the SSI implementation

The values in the V10 NFM column highlight immediate “out-of-the-box” savings after migration. For example:

  • The average thread footprint storage decreased 63 percent. More threads can be executed concurrently without storage constraint concerns.
  • The maximum number of possible threads increased 74 percent.
  • CPU consumption decreased significantly for DDF and DBM1 address spaces. Enhanced INSERT performance, index usage efficiencies, and access path enhancements delivered immediate benefits.

High-performance DBATs

The next major DB2 10 feature SSI implemented was high-performance DBATs (database access threads). Prior to DB2 10, the RELEASE option of BIND PACKAGE was not honored in distributed applications. Packages were always de-allocated at commit, mainly to allow more availability for functions such as data definition language (DDL), utilities, and BIND packages, which can be affected if the packages were released only at the application end.

I conducted a performance analysis of inactive connection processing for SSI, and this analysis showed that a large CPU cost occurred in package allocation and de-allocation. A CPU reduction can occur when pooling DBATs and then associating a pooled DBAT with a connection. The SSI application was 99 percent Java, running outside of z/OS. All SQL workload was coming through the distributed data facility (DDF). It was an excellent candidate for high-performance DBATs.

High-performance DBAT enhancement decreased CPU cost significantly for distributed workloads (see Figure 2).

08:00 – 17:00 Total SQL (billion) SQL/second (x1000) Total CPU time/commit Lock-unlock/second (x1000) Total CPU time
After activation
April 5 1.407 43.4 0.000345 33.6 16:45:23.000000
March 29 1.736 54.1 0.000224 49.3 24:48:00.000000
March 25 1.515 46.8 0.000362 40.2 19:17:47.508479
March 22 1.271 39.3 0.000330 36.3 15:22:23.642018
The date when high-performance DBATs were activated
March 19 1.317 40 0.000328 27.2 16:30:00.000000
Before enabling the enhancement
March 15 1.215 37.5 0.000429 46.5 20:00:08.362063
March 8 1.576 36.5 0.000436 46.6 26:00:00.000000
March 4 1.300 40.1 0.000454 48.3 23:12:53.533808
Figure 2. Total CPU time

Key changes include:

  • CPU cost/commit was reduced around 27 percent
  • Unlock-lock numbers were reduced 21 percent
  • Total CPU cost of DB2 was reduced 21 percent on average

Conclusion

After migrating to DB2 10 for z/OS and implementing enhancements, the SSI experienced performance enhancements that translated into important benefits for the organization. Looking ahead, the SSI is investigating additional enhancements, including 1 MB page size exploration and other SQL enhancements. These modifications should deliver additional real and quantifiable business benefits.

Questions or feedback? Add a note in the comments section below.