Maximizing Performance and Availability Utilizing DB2 11 and 12 for z/OS

Susan Lawson, YL&A Inc. - Wednesday 30 May - Session 1

With every new release of DB2 we look to see what features will allow us to improve the capabilities and performance of our existing applications as well as the availability of our data. We also have to plan to utilize new features in our development efforts. This presentation takes a look at the features in DB2 11 and 12, that will improve our performance and provide us with maximum data availability as well as advanced application development. We will be focusing on features that can be utilized by DBAs and application programmers.


  • Database Design and Performance - 11
    • Suppress null indexes
    • Pseudo-deleted index entry cleanup
    • Help for indirect references
    • Large number of partitions access improvement
    • No log declared temporary tables
    • Incremental bind avoidance for DGTTs
    • Parallelism support for DPSIs
    • Partition elimination on join predicates
    • Selective decompression
    • RTS immediate externalization
    • Access command parallelism
    • DSNACCOX improvements
    • Work file usage thresholds
  • Database Design and Performance - 12
    • Range-partition tablespace size increase
    • Partition insert into middle
    • Basic row format deprecated
    • LOB compression
    • Overflow for in-memory objects
    • In-memory indexes
    • Improved log sequence number
    • Claim and drain improvements
    • Improved lock avoidance checking
  • Subsystem Performance - 11
    • Reclassification of buffer pool pages
    • 2G framesize support
    • MRU queuing support for utilities
    • New accounting for buffer pool processing
    • Increased number of open datasets
    • New RBA/LRSN format
    • Subsystem parameters for performance
  • Subsystem Performance - 12
    • In-memory contiguous buffer pools
    • In-memory indexes
    • More usage of 1M page frames
    • EDM pool storage allocation
    • Improved log sequence number
    • Claim and drain improvement
  • SQL and Optimization - 11
    • Predicate selectivity overrides
    • APREUSE - WARN option
    • Explain table enhancements
    • Virtual index enhancements
    • Statistics feedback
    • RID overflow processing
    • Stage 2 predicate pushdown
    • Correlated subquery conversion
    • In memory sorts
    • Pruning of always false predicates
    • Duplicate removal during sort avoidance
    • GROUP BY/DISTINCT early out for joins
    • Sparse indexes/hash joins
    • Correlated subquery cache
    • Multiple index access for subqueries
    • Optimization of invariant expressions
    • Optimizer cost adjustments
  • SQL and Optimization - 12
    • Fast insert algorithm
    • DELETEs allowing interim commits
    • Static SQL resource limit
    • Reduced overhead on declared temps
    • Improved caching of generated values
    • Dynamic plan stability
    • Plan stability improvements
    • Profile support for stats feedback
    • Optional cache invalidation
    • Enhanced MERGE statement
    • SQL pagination syntax support
    • FREQVAL automated counts
    • UNION ALL and outer join improvements
    • Table UDF performance enhancements
    • Predicate evaluation improvements
    • Sort processing enhancements

Platform: z/OS


← Back to all sessions

About: Susan Lawson


Susan Lawson is an internationally recognized consultant and lecturer with a background in system and database administration. She currently works with several large clients to help develop, implement and tune some of the worlds’ largest and most complex DB2 databases and applications. She also performs Performance and Availability Audits for many clients to help reduce costs through proper performance tuning and to help ensure availability. Her other activities include authoring articles and white papers, presenting at user group meetings, and teaching a variety of DB2 courses. She is an IBM GOLD Consultant for DB2 and z/Series, and an IBM Information Champion and has authored the IBM ‘DB2 for z/OS V8 DBA Certification Guide’ and the IBM ‘DB2 for z/OS V7 Application Programming Certification Guide’. She is also the co-author of several DB2 books including the IBM ‘DB2 10 for z/OS DBA Certification Guide’, IBM ‘DB2 9 for z/OS DBA Certification Guide’, ‘DB2 High Performance Design and Tuning’ and ‘DB2 Answers’. She is also a SME(Subject Matter Expert) for the IBM Certification exams for DB2 z/OS Database and System Administration assisting with writing and verifying questions for the exams.