CPU reduction in online transactions  Most of the system performance improvements are designed to use larger and relatively less  expensive real storage, which is now available in the latest System z processors. Db2 online  transactional workloads can expect CPU savings ranging from a few percent up to 10%,  depending on the workload characteristics and memory allocation. Simple online transactions  with large singleton selects through a unique index can see more CPU reduction because of  index memory optimizations and other new internal optimizations.  Db2 subsystems that are on z/OS LPARs with large amounts of available memory, or Db2  subsystems that are caching frequently accessed indexes or table spaces can use improved  in-memory buffer pool management techniques in Db2 12. With these new features, CPU  savings can result from avoiding I/O costs and reducing the overhead of managing buffer  pools if the entire Db2 user objects can fit within the buffer pools.  Db2 12 delivers significant CPU reductions for modern real-time reporting and analytics  queries.  Unlike traditional online transactional SQL where only one or a few records are retrieved by  using a direct index lookup, many modern applications are increasingly using more complex  SQL functions. They tend to involve more join processing through views or history tables, and  more sorting is needed to manipulate the data.  Db2 12 specifically targets improving the performance of these types of more complex query  workloads, which often require short response times (less than a few seconds) and  sometimes require higher concurrency than IDAA can provide.  After PREPARE/REBIND in Db2 12, we observed a wide range of CPU and elapsed time  savings. Depending on the workload, we saw a range of savings from a few percent for simple  traditional query workloads, all the way up to 50% or more for modern complex query  workloads compared to Db2 11. The workloads that benefited most include operations, such  as complex outer joins, use of User Defined Functions, and UNION ALL constructs within  views or with history tables. These operations have specific optimizations within Db2 12 to  improve their performance.  Db2 continues to emphasize the use of zIIPs to reduce the total cost of ownership. Db2 12  increases the zIIP eligibility of child tasks under query CPU parallelism from up to 80% to up  to 100%. This ability simplifies capacity management for zIIP processors when estimating  zIIP capacity. In addition, the reload phase of the REORG and LOAD utilities is now marked  as zIIP eligible.  One critical aspect of performance management is consistency. Unstable access path  selection for critical queries can have a significant effect on system level performance. Db2 10  provided static plan stability and reuse capability for access paths (APREUSE) for static  packages. Db2 11 provided further enhancements in APREUSE.  Db2 12 takes another major step in our access path stability functionality by providing  dynamic statement stability. Dynamic statement stability gives users control over when their  dynamic SQL applications are eligible for new access paths and when they are not, similar to  static plan stability for static SQL. Among other benefits, this new feature can be used to  ensure that consistent access paths after the REORG/RUNSTATS utilities are run by storing  the stable access path into Db2 catalog tables.  High resiliency and consistent performance  Cost reduction by using more zIIPs  CPU reduction in real-time analytics queries Chapter 1. Introduction 3