Db2 10 for z os update
1 / 37

DB2 10 for z/OS Update - PowerPoint PPT Presentation

  • Uploaded on

DB2 10 for z/OS Update. Curt Cotner IBM Fellow IBM Silicon Valley Lab [email protected] DB2 10 for z/OS. GA’ed October 2010 Completed Largest Beta Ever 23 WW customers +10 Extended Beta Over 80 vendors Fastest uptake out of the gate

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
Download Presentation

PowerPoint Slideshow about ' DB2 10 for z/OS Update' - garin

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
Db2 10 for z os update

DB2 10 for z/OS Update

Curt CotnerIBM FellowIBM Silicon Valley Lab

[email protected]

Db2 10 for z os
DB2 10 for z/OS

GA’ed October 2010

Completed Largest Beta Ever

  • 23 WW customers

  • +10 Extended Beta

  • Over 80 vendors

    Fastest uptake out of the gate

  • As of May, 2011 over 120 customer orders… 3x that of the prior release

  • More than 4x the number of licenses

  • About 25% are migrating from V8

  • Every core beta customer is continuing with migration plans

  • First customer already in production

    • Migrated from V8 to V10

    • Quality/stability looking good

Db2 10 for z os1
DB2 10 for z/OS

  • Fastest uptake

    • +2Xcustomers

    • +2.5Xlicenses

    • 25%coming from DB2 V8

  • Customers in Production

    • SAP, data warehouse and OLTP workloads

    • Skip-level and V2V

  • Quality / stability solid

  • Production references

DB2 V8 EOS: April 30, 2012

DB2 9 EOS: June 27, 2014

The latest db2 10 cpu performance feedback
The Latest DB2 10 CPU & Performance Feedback

North America Insurance


EMEA Insurance

I/O Parallel Inserts 30% CPU; 41% Elapsed Time

North America Banking

CICS/DB2 7% CPU; 5% Elapsed Time

North America Manufacturing


EMEA Insurance

Key Queries 3-15% CPU



Performance Boost with CPU Savings

Fully in Production

  • Client:One of the largest insurance companies in Germany, supporting almost 7 million customers and more than 12 million insurance policies

  • Challenge:Maintain 24x7 availability while efficiently handling 18 million transactions a day and supporting industry standards of 10+ years of historical data and photos

  • Solution: DB2 10 for z/OS, CICS Transaction Server for z/OS V4.1, and z196 hardware

  • Key Benefits:

    • Efficient, easy to scale solution

    • 10% performance improvement with sub-second response times

    • Cost savings with 10% CPU reduction

“Migrating to DB2 10 generated a 10 percent performance improvement and 10 percent CPU savings in our production system almost immediately.”

Matthias Jahns, Project Manager, HUK-COBURG

Production customer experiences
Production Customer Experiences

Fully In Production

Fully In Production

Fully In Production

“We went for an early upgrade. The software was ready for us and we were ready for it. Why not take the savings early? DB2 10 has been a pleasant surprise. It will be difficult to find an area inside DB2 that does not give great performance. That being for developers, operation staff, administrators, etc. All will get benefits that could justify the upgrade in itself”

“We are very pleased with the CPU savings we have seen with the out-of-the-box implementation during testing and early production phases. Our regression tests showed performance improvements just by running the workload on a DB2 10 CM member rather than a DB2 9 NFM member in the same data sharing group”

“Migrating to DB2 10 generated a 10% performance improvement and 10% CPU savings in our production system almost immediately.”

“The ‘overall performance’ in DB2 10 is better compared to DB2 9. The stability of DB2 10 is better compared to previous releases”

In Production

“Participation in the beta gave us confidence to upgrade from V8 to DB2 10. The benchmarks and analysis of functionality and performance have exceeded our expectations. So far our upgrades have gone smoothly and we are looking forward to completing our successful rollout of DB2 10”

Who has db2 10
Who has DB2 10?

… and many more

Sap system z continue to grow
SAP & System z Continue to Grow

SAP Banking Services 7 on DB2 10 and z196

  • Record-setting performance for SAP core banking workloads

  • Number of accounts processed larger than ever before: 150 million (Previously: 40 million)

4 of the 6 customers mentioned in this article are on System z

Large-scale project run in Poughkeepsie in 1H 2011


Other Triggers Driving Customers in 2012

  • DB2 V8

    • End-Of-Service effective: April 31, 2012

    • 50% off Extended Service if purchase DB2 10 before July 1, 2012

  • DB2 9

    • Withdraw-from-Marketing

      • announce: Dec. 6, 2011 effective: Dec. 10, 2012

    • End-Of-Service

      • announce: Feb. 7, 2012 effective: June 27, 2014

Db2 10 experiences
DB2 10 Experiences

Reduced cost

Improved scalability

Improved resilience

Why DB2 10 Now?

”Our DB2 10 experience has

given us confidence about the

virtual storage relief and CPU

savings. I am looking forward to continuing our rollout and

reaping the benefits.”

Niels Simanis

Senior Technology Manager

Danske Bank


  • 90%Virtual storage savings

  • 10%CPU savings on CICS transactions

  • 30%CPU savings on test batch workload

Migration Tips

  • Plan well, including good maintenance practices

  • Rebind can get you the highest CPU savings

  • Expect increase in real storage consumption to support and exploit DB2 10

Db2 10 experiences1
DB2 10 Experiences

Reduced cost

Improved performance

Improved scalability

Why DB2 10 Now?

“We are pleasantly surprised with the out-of-the-box CPU savings we have seen during testing and early production phases.”

Terry Glover –Director IT Infrastructure



  • 20-30% CPU savings out-of-the-box

  • 5-15% Performance improvements for batch, CICS, and DDF

    Actual results may vary for other customers

Migration Tips

  • Thorough preparation and planning

  • Good maintenance practices

Db2 10 customers seeing reduced costs simplified workloads through proven technology
DB2 10: Customers seeing reduced costs, simplified workloads through proven technology

For more customer references visit


Lowest cost platform per user
Lowest Cost Platform per User

Consolidation savings with DB2 10

Easier porting of applications to DB2 for z/OS

Extensive SQL & XML support added throughout

Processing Savings with DB2 10

  • 5 – 10% savings out of the box

  • Distributed application access can save up to 20%

  • Native SQL stored procedures can save up to 20%

  • Complex queries will now result in savings of up to 20%

  • Heavy insert activity can drive 10 – 40% savings

Ibm relational warehouse workload irww with data sharing
IBM Relational Warehouse Workload (IRWW) with data sharing


  • DB2 9 NFM  DB2 10 CM without REBIND measured 3.7% CPU reduction from DB2 9

  • DB2 10 CM REBIND getting same access path measured 7.4% CPU reduction from DB2 9

  • DB2 10 NFM measured same 7.4% CPU reduction from DB2 9

  • DB2 10 CM or NFM with RELEASE(DEALLOCATE) measured additional 10% CPU reduction from DB2 10 NFM RELEASE(COMMIT)

Db2 10 higher throughput better scaling sample sap erp measurement
DB2 10 Higher Throughput & Better Scaling - Sample SAP ERP Measurement

  • Improved SQL runtime efficiency

  • Boosted SQL INSERT performance

    • Parallel I/O for index updates

    • Reduced latch contention

  • More efficient large bufferpools

  • Inline LOBs to avoid LOB TS access

  • DFSORT performance improvement

    • APAR PM18196

  • Extended zIIP exploitation

    • Prefetching of pages into buffer pools

    • Large portion of Runstats

Simplicity extraordinary scalability
Simplicity - Extraordinary Scalability

DB2 10 for z/OS now enables applications 5-10 times the number of users and activity on a single system

Virtual storage improvements deliver 10 times more scalability

Fewer DB2 members required - reducing complexity and improving application performance

Direct Row Access

New Hash Access methodology eliminates multi-step indexing

Significantly improves applications requiring single row access

30% to 50% improvement with Hash Access over Indexed data access



We expect to reduce our data sharing requirements by 25%, which means less system, storage and resource expenses

Banco do Brasil



Running Many Active Threads

DB2 10


Coupling Technology

Coupling Technology








(500 thds)


(2500 thds)


(500 thds)


(500 thds)


(2500 thds)


(2500 thds)


(500 thds)


(500 thds)


(500 thds)

  • More threads per DB2 image

  • More efficient use of large n-ways

  • Easier growth, lower costs, easier management

  • Data sharing and Parallel Sysplex still required for very high availability and scale

  • Rule of thumb: save ½% CPU for each member reduced, more on memory

  • Data sharing and sysplex allows for efficient scale-out of DB2 images

  • Sometimes multiple DB2s per LPAR

Other system scaling improvements
Other System Scaling Improvements

  • Other bottlenecks can emerge in extremely heavy workloads

    • Reduced latch contention

      • Improved efficiency for latch suspend/resume

    • new option to for readers to avoid waiting for inserters

    • eliminate UTSERIAL lock contention for utilities

    • Use 64-bit common storage to avoid ECSA constraints

  • DB2 10 NFM catalog restructure improves BIND / Prepare concurrency

  • SPT01 64GB constraint

  • Improved accounting rollup, compress SMF option

    • Reduced SMF data volume

  • Lower overhead for very large buffer pools

Hash access
Hash Access

Select Balance From AccountsWHERE acctID = 17

  • Hash Access provides the ability to directly locate a row in a table without having to use an index

  • Single GETP/RELP in most cases

  • 1 Synch I/Os in common case

    • 0 If In Memory Table

  • Greatly reduced Search CPU expense

= Page in Bufferpool

Accounts Table

= Page Read from Disk


Time Travel Query

Integrated Bitemporal capabilities

Query over any current, prior or future period in time

System and User-maintained temporal tables

SQL standards work in progress




What is the answer now?

What was the answer in the past?

What will the answer be in the future?

“As much as 80% of our applications can use this, which will drastically save developer time and even more importantly make applications easier to understand to improve business efficiency and effectiveness”  

The new temporal functionality in DB2 10 for z/OS will allow us to drastically simplify our data-related queries and reduce our processing cost by having DB2 handle data movement more efficiently than our custom code

Major Insurance Company

Temporal data or versioned data



Temporal data or versioned data

Table-level specification to control data management based upon time

Two notions of time:

System time: notes the occurrence of a data base change

“row xyz was deleted at 10:05 pm”

Query at current or any prior period of time

Useful for auditing, compliance

Business time: notes the occurrence of a business event

“customer xyz’s service contract was modified on March 23”

Query at current or any prior/future period of time

Useful for tracking of business events over time, application logic greatly simplified

New syntax in FROM clause to specify a time criteria for selecting historical data

More improvements for applications



More Improvements for Applications

SQL compile time: Optimization techniques

Remove parallelism restrictions; more even parallel distribution

Scalability: memory and latching relief allow more parallel

Optimization validation with Real Time Statistics

In-memory techniques for faster query performance

Multiple IN-List matching

IN-List predicate transitive closure

SQL runtime:

RID overflow to workfile

Mitigate increased workfile usage by increasing RID pool size (default increased in DB2 10).

MAXTEMPS_RID zparm for maximum WF usage for each RID list

Sort performance enhancements

Avoid workfile usage for small sorts

Hash support for large sorts

And more application improvements



And More Application Improvements

pureXML enhancements

Large object improvements

Allow non-NULL default values for inline LOBs

Loading and unloading tables with LOBs

LOBs in input/output files with other non-LOB data

SQL Enhancements

Currently committed locking semantics

Implicit casting or loose typing

Timestamp with time zone

Variable timestamp precision – seconds to picoseconds

Moving Sum, Moving Average

SQLPL performance improvements

Extended indicator variables

Row and column access control



System Administrator


Security Administrator Tasks

Database Administrator





Business Security & Compliance

  • Protect sensitive data from privileged users & improve productivity

    • SECADM & DBADM without data access

    • Usability: DBADM for all DB

    • Revoke without cascade

  • Separate authorities to perform security related tasks, e.g. security administrator, EXPLAIN, performance monitoring and management

  • New audit policy controls, audit privileged users

  • Row and column access control

    • Allow masking of value

    • Restrict user access to individual cells

Continuous availability more online schema changes
Continuous Availability: More Online Schema Changes


    • Page size (not XML) (BUFFERPOOL)

    • DSSIZE


    • Table space type

      • Single table simple -> PBG (inherit MC)

      • Single table segmented -> PBG

      • Classic partitioned -> PBR (inherit MC)

      • PBG -> Hash



    • Page size (BUFFERPOOL)

      • In DB2 9 this was immediate with RBDP set

  • Other schema change enhancements

    • Table space no longer needs to be stopped to alter MAXROWS

    • Object no longer needs to be stopped to alter BPOOL in data sharing




Other availability improvements
Other Availability Improvements

  • Access currently committed data

  • Change DDF location alias names online

    • New MODIFY DDF ALIAS command

  • Online DDF CDB changes


  • Dynamic add of active logs

    • New –SET LOG NEWLOG option

  • Pre-emptable backout

Why is ibm focusing on idaa
Why is IBM focusing on IDAA?

  • Changing business requirements

    • BI/DW becoming mission critical and requires OLTP-like QoS

      • reliability, continuous availability, security, mixed workload management, …

      • orders of magnitude faster execution of complex, ad hoc queries

      • predictable query performance

    • Shift towards dynamic DW and operational BI

      • Combining OLTP and OLAP workloads

  • Traditional performance tuning tools of the trade such as indexing, pre-built aggregates and MQTs struggling to keep the pace

    • Require top DBA expertise and sophisticated tools

    • Even then not good enough due to ad-hoc, unpredictable nature of the workload

  • Technology trends

    • Very large number of processor sockets and cores

    • Specialized query processing engines

    • Appliances

Db2 components
DB2 Components


DBA Tools, z/OS Console, ...

Application Interfaces

(standard SQL dialects)

Operation Interfaces

(e.g. DB2 Commands)









. . .

Ibm db2 analytics accelerator as a virtual db2 component
IBM DB2 Analytics Accelerator as a Virtual DB2 Component


DBA Tools, z/OS Console, ...

Application Interfaces

(standard SQL dialects)

Operation Interfaces

(e.g. DB2 Commands)










. . .

Deep db2 integration
Deep DB2 Integration


DBA Tools, z/OS Console, ...

Application Interfaces

(standard SQL dialects)

Operation Interfaces

(e.g. DB2 Commands)










. . .

Industry leading

DW performance,

ease of use

Superior availability

reliability, security,

workload management,

OLTP performance ...


z/OS on System z

10‘s of processors

100‘s GB of memory

The best of both worlds
The Best of Both Worlds

Best in OLTP

Industry recognized leader in mission critical transaction systems

Data Mart

Data Mart

Data Mart

Data Mart

Data Mart Consolidation

Best in Data Warehouse

Proven appliance leader in high speed analytic systems

Transaction Systems (OLTP)

Best in Consolidation

Unprecedented mixed workload flexibility and virtualization providing the most options for cost effective consolidation

System z

Recognized leader in mixed workloads with security, availability

and recoverability for OLTP

Netezza Recognized leader in cost-effective high speed deep analytics

Data Warehouse Analytics

Field Experiences

Queries running up to 1908 times faster

For more information
For More Information

DB2 10

Customer Material www.ibm.com/software/data/db2/zos/db2-10/resources.html

DB2 10 e-kit www.ibm.com/marketing/edocument/swg/sp_db210_kit/document/


Benchmark: www.ibm.com/solutions/sap/us/en/news/zenterprise_system_scales_best.html

RFE (Request for Enhancement) database


  • VISIT the DB2 Best Practices

  • JOIN the World of DB2 for z/OS

  • JOIN the DB2 for z/OS group

Engaging web and mobile applications
Engaging Web and Mobile Applications

Modern User Interface and Form Factors

Implemented using JavaScript for Web 2.0 in desktop

Implemented using JavaScript in IBM Worklight for hybrid mobile application

But there are gaps in nosql datastores can enterprises live with these
But --- there are Gaps in NoSQL DataStores …. Can Enterprises Live with these?

Complex apps

Data integration

Multi-statement transactions

Data Management tool-set and Eco-system


Integration with Enterprise Data

Goals for an ibm nosql store
Goals for an IBM NoSQL Store

  • Embrace the flexibility and scalability of emerging NoSQL solutions

    • Add JSON capabilities

    • Ensure web scale application support

  • But don’t compromise traditional enterprise grade qualities of service and unique RDBMS capabilities

    • Complex transactions

    • Joins

    • Temporal Data

    • Geo-spatial data

    • Atomicity, Consistency, Integrity, Durability

    • Access control & security

    • Referential Integrity

    • Check constraints

    • Scalability

    • Robust monitoring

  • Keep it simple

    • Programming API

      • Simple API for simple JSON queries and updates

      • Extensions as needed for specialized functions

        • - temporal data, geo-spatial, granular access

      • Allow access to JSON data and relational tables in the same query

    • Administration

      • Autonomic storage management

      • Simple replication solutions

      • Little friction for model and schema changes

  • Keep it fast

    • Leverage the strengths of IBM technology

      • Indexing, buffering, scalability, access path optimization

      • Distributed caching