DB2 10.5 for Linux, UNIX, and Windows

Highlights of DB2 Version 10.5

IBM® DB2® Version 10.5 for Linux, UNIX, and Windows offers accelerated analytic processing by introducing a new processing paradigm and data format within the DB2 database product. Advantages include significant reductions in time-to-value and increased consumability, which can be achieved through minimal DBA design requirements and reduced query tuning and debugging efforts. Industry-leading compression, large performance gains for analytic queries, and large reductions in performance variation round out the benefits of deploying this technology.

DB2 Version 10.5 fix packs deliver additional enhancements and product changes. For a summary of all the new and changed functionality provided in each Version 10.5 fix pack, see DB2 Version 10.5 for Linux, UNIX, and Windows fix pack summary.

DB2 column-organized tables

DB2 Version 10.5 introduces compressed column-organized tables for DB2 databases. The release also includes broad support for data mart (analytic) workloads with complex queries that are commonly characterized by multi-table joins, grouping and aggregation, and table scans over a star schema.

Column-organized tables are tables whose data pages contain column data instead of row data. This new capability is in-memory optimized, CPU optimized, and I/O optimized.

The DB2 product continues to provide industry-leading performance across multiple workloads by combining unique ideas with several of the best technological developments of the past 10 years of database research. BLU Acceleration, a combination of innovations from IBM Research and the development labs that simplifies and speeds up reporting and analytics, is a new, fully integrated capability in DB2 Version 10.5. Easy to set up and self-optimizing, BLU Acceleration can typically eliminate the need for indexes, aggregates, or time-consuming database tuning to achieve top performance and storage efficiency. In most cases, no SQL or schema changes are required to take advantage of this breakthrough technology.

DB2 Version 10.5 provides the following enhancements:

The ANALYTICS option of the DB2_WORKLOAD registry variable, which provides a single setting to enable column organization, space reclamation, and automatic workload management, and to automatically configure memory, page size, and extent size (see System environment variables)
New syntax for the CREATE TABLE statement to specify table storage organization (see CREATE TABLE statement)
A new database configuration parameter (dft_table_org) to change the default table organization (see dft_table_org - Default table organization)
The new db2convert utility to convert row-organized tables into column-organized tables (see db2convert - Convert row-organized tables into column-organized tables)
The ability to use the REORG TABLE command to reclaim storage in column-organized tables (see Space reclamation for column-organized tables)
Automated workload management, which can significantly improve the performance of workloads with several queries running at the same time (see Default query concurrency management)
Dynamic list prefetching, a new prefetching type that is used in query execution plans that access column-organized tables (see Prefetching data from column-organized tables)
Support for NOT ENFORCED primary key and unique constraints, which you can use to reduce performance costs and space requirements when it is known that the data already conforms to the constraint (see Informational constraints), and for ENFORCED primary key and unique constraints on column-organized tables

The following additional enhancements pertain to column-organized tables specifically:

Semi-join support for queries to reduce the amount of memory that is consumed by large inner tables; in some cases, queries that use semi-joins might run faster. A semi-join is an optimization technique that uses one or more predicates on the outer table and other inner tables in the query. The combined filtering effect of all eligible predicates is pushed down to one or more large inner tables to reduce memory consumption. DB2 explain output is enhanced to indicate whether the inner table of a particular hash join operator is a large inner table for which the semi-join optimization technique is applied (see EXPLAIN_ARGUMENT table).
Late decompression, the ability to operate directly on compressed data for certain operations, thereby reducing memory usage.
Multiplied CPU power that uses single instruction, multiple data (SIMD) processing for many operations.
A vector processing engine for processing vectors of column data instead of individual values.
Improved system scaling across cores.
An enhanced caching strategy for buffer pools to substantially reduce I/O.
A smaller footprint for database storage than in DB2 Version 10.1.
In-memory optimization for maximal performance.
A system that is unconstrained by main memory size, in which the amount of space used by the data that is being processed can be considerably larger than the RAM.

For the most up-to-date installation requirements for BLU Acceleration , see DB2 Advanced Enterprise Server Edition 10.5 - Detailed system requirements for the BLU deployable component .

IBM DB2 pureScale Feature enhancements

The DB2 pureScale® Feature was first introduced in Version 9.8. V10.5 builds on DB2 pureScale Feature support.

The DB2 pureScale Feature provides extreme capacity and application transparency which exceed even the strictest industry standard. Continued improvements in continuous availability and high availability, the DB2 pureScale Feature tolerates both planned maintenance and component failure with ease. The DB2 Version 10.5 release provides:

Support for high availability disaster recovery (HADR)
Increased availability
Improved workload balancing
Restore mobility between DB2 pureScale Feature and DB2 Enterprise Server Edition

In addition, V10.5 introduces DB2 pureScale Feature enhancements in high availability, performance, workload management, and installation.

For more information, see DB2 pureScale Feature enhancements.

Performance improvements

Continuing to build on prior release performance improvements, V10.5 performance improvements focus on explain information for column-organized tables and extension to the CREATE INDEX statement to create an index containing an expression-based key.

For more information, see Performance enhancements.

SQL compatibility

If you work with relational database products other than DB2 products,V10.5 builds on existing functionality, interfaces, and compatibility features to provide additional enhancements that make DB2 products more familiar to you. These enhancements reduce the time and complexity of enabling applications that are written for other relational database products to run quickly in a DB2 environment.

For more information, see SQL compatibility enhancements.

Product packaging simplified

Changes to product packaging results in fewer packages, and greater value through more function and features that are included in the base DB2 editions such as warehouse functionality.

For more information, see Product packaging enhancements.