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.