DB2 11 and 12 Performance and Availability: What Can I Take Advantage Of?
Baltimore/Washington DB2 Users Group
March 8, 2017 Education Session
DB2 11 and 12 Performance and Availability: What Can I Take Advantage Of?
Abstract
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.
Outline
DB2 11 - Database Enhancements
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
Statistics feedback
Work file usage thresholds
Physically delete empty partition-by-growth partitions
DB2 12
LOB compression
RI support on temporal tables
Inclusive periods on temporal tables
Range-partition tablespace size increase
Partition insert into middle
Basic row format deprecated
Profile support for stats feedback
DB2 11 – Subsystem and Data Sharing Enhancements
Reclassification of buffer pool pages
Buffer pool size simulation
MRU queuing support for utilities
Increased number of open datasets
Restart light with CASTOUT option
Group buffer pool write-around protocol
Improved castout processing
Log record sequence number spin avoidance
DB2 12
Overflow for PGSTEAL(NONE)
In-memory indexes
mproved log sequence number
Claim and drain improvements
Improved lock avoidance checking
DDF shared session data across group
DB2 11 - SQL and Optimization
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
DB2 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
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
DB2 11 - Database Availability
ELEASE(DEALLOCATE) thread interruption
Cancel DDF threads
Online drop column
Online alter limit key
Deferred definition DBD lock release
PIT recovery for deferred schema changes
Reorg without sorting
DB2 12
Option to skip PIT recovery for non-updated page sets
PIT recovery after materialization for UTS PBG
Pending column level alters
Pending level index compression
DB2 11 - Application Enhancements
Global variables
ARRAY data type performance
Transparent archiving
Cancel thread
Cancel SQL
Column processing above the bar
Data type processing improvements
Rollback to savepoint
DB2 12
Advanced triggers
LOB support in global variables
Global variables defined as array type
Fetch/open/execute with global variables
Profile based global variables
Views, UDFs intact on DDL for underlying tables
Increase max number of tables referenced in view, UDF, statements
New built in functions
Unicode columns in EBCDIC tables
SQL pagination
SQL PL improvements
SQL PL triggers
XML enhancements
DB2 11 - Distributed Performance
Package based continuous block fetch
Implicit commit for stored procedures
DDF synchronous receive
Multi-threaded Java stored procedures
JDBC/ODBC stored procedure optimizations
Cancel DDF thread improvement
DB2 12
JDBC/ODBC type 2 performance ODBC driver improvements
PrepareAttribute literal replacement BIND option
DRDA fast load
DBAT pooling
Speaker Bio – 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 books, articles and white papers, presenting at user group meetings, and teaching a variety of DB2 courses. She is an IBM Gold Consultant and an IBM Analytics Champion and is the author of the IBM DB2 11 for z/OS Database Certification Review Guide.