DB2 10.5 New Features
29 July 2013
DB2 v10.5 became available on 14 June 2013 and has many new
features and improvements, some of which you can begin taking advantage of
without doing anything other than upgrading your database. As with my past “features” articles, this is a summary of the new
features in v10.5 that I see as the most important. My hope is to
provide a summary that you can browse to quickly find the items that are the
most valuable to you with links you can follow for more in-depth reading.
A few features have also been discontinued, most notably
This article is by no means a complete list, nor does it provide a complete description of the features that it does list. However, I believe that it does provide a great way to quickly see the some of the most useful features now available. For a complete list of all new and changed features, please see the What’s New Info Center page or What’s New pdf guide. You can also see recorded webcasts that give more in depth information on Channel DB2. Note that not all features described here are available in all editions. Please see the “Functionality in DB2 features and DB2 Product Editions” page to see a chart of what major features are in which editions.
Before you even start planning your upgrade to DB2 v10.5 or other supported versions, you should review the materials on the DB2 Upgrade Portal. It contains information about everything from project planning to step-by-step instructions for performing the upgrade along with tips for avoiding problems.
BLU Analytic Query Acceleration (See Chapter 3)
This is a very useful new technology to accelerate many types of analytical queries without doing much engineering to design complex indexes, materialized query tables, partitioning, etc. Since it does not use these types of objects BLU makes it very fast and easy to create new tables within an existing database or a new warehouse, load data and start getting great query performance. It also provides utilities to enable you to quickly convert existing tables to BLU technology that are not giving you the performance that you want. This is only a physical implementation of a table, so there are no changes needed to queries or applications to use it. BLU is a combination of features that includes a column organized table or columnar technology. These features build on one another to make queries that scan a lot of data go very quickly.
Column organized means that instead of putting an entire row on a page, each column of a row is placed into a different page with the same columns within a table being placed on the same pages. This means that when you are scanning through a lot of values looking for matches, you do much less I/O because you are only reading pages that have the column(s) used by your predicate (where clause). Once DB2 determines which rows match the predicate conditions, only then are the rows reassembled and sent to the users. Because of this column organization, we can achieve much better compression. This is partially due to the type of compression that is being used that replaces more frequently used values with shorter symbols than less frequently used values. Because of this great compression, you can squeeze many more values on each page reducing I/O even more because you get more values to scan on each page. But wait, it gets even better! DB2 puts min and max values about every couple of thousand entries that show the range of values in the next several pages. So as DB2 is scanning the data it will skip large sections of pages, reducing I/O yet again. Another benefit of all of this compression and skipping is that it allows DB2 to implement better caching algorithms to keep the important pages in memory even if the database exceeds the size of the memory on the machine. Achieving an in-memory database this ways gives lightning performance, but even if even if a portion of your database does not fit into RAM you can still get great performance unlike most of our competitors with in-memory databases.
Another thing that DB2 does to make this faster is to compress the predicate value once rather than decompress every value in the column as it is being scanned. This speeds the scan while reducing the CPU overhead of decompressing a lot of values that don’t match your predicate anyway. Since DB2 compresses the values of related columns across column organized tables with the same symbols, you also do not need to decompress to do joins in many situations. It should be noted here that you can have a mix of row organized tables and column organized tables in the same database. This is great for mixed workload databases where you want to convert a subset of tables to columnar for particular reporting queries.
DB2 BLU is also engineered to take advantage of newer hardware capabilities such as SIMD that allows a predicated to be compared with several values in a column in one CPU tick rather than one comparison per tick. Further, BLU will use processor vectoring where available. It also takes better advantage of CPU parallelism than previous versions.
All of these factors combined make scanning of data so fast that indexes, materialized query tables and other objects are not needed, making the design of BLU tables much, much easier. This makes it very simple to use BLU because of the fact that you just create the tables and load them, which can be done with our new utilities without an outage. So in addition to the great compression, you save even more space with BLU because now you don’t need storage for those objects.
To see which of your tables to would benefit your analytical queries and to what extent, the editions of DB2 that contain BLU also come with tooling that allows you to see this. You point it at the database and give it the analytic queries that interest you and it shows how you will benefit. By default, DB2 can also manage the workload of queries to get the best throughput, by preventing too many queries from be executed at once. Once DB2 believes the threshold has been reached where further queries would cause thrashing, it queues the rest and submits them as there is room, preventing a lot of resources being wasted because query threads are fighting over resources instead of getting work done. New Monitoring Elements and administrative views for column organized tables have been added to allow you to monitor the performance of your BLU tables.
This DB2 BLU technology is part of both the Workgroup and Enterprise Advanced editions of DB2. To assist with your analytics even more, you also get five licenses of IBM Cognos. This has just bee a very high level overview of the new BLU technology. For more details, please read chapter 3 of the new DB2 flash book. As of the writing of this article, chapter 3 is the only chapter in the book.
DB2 pureScale Improvements
In DB2 v10.5 the development team has added a number of new features to improve performance and ease of use as well as making it even more highly available.
A number of really good improvements have been made to the DB2 High Availability Disaster Recovery feature. A summary of the features are below, but please see “Part 1” in the Data Recovery and High Availability Guide and Reference for a complete list.
Similar to what it does for XML, DB2 now allows you to store JSON objects, index them at various levels, and provides an API to use insert and query them. You just need to get the new free download.
The new IBM Mobile Database is a database that currently runs on Android devices that can easily be synched with DB2 and Informix databases. It can persist data on the device and keep that data synchronized with DB2 and Informix databases or sync only periodically. It is a full featured relational database while having a small foot print. Best of all it is a free download from IBM. This is not a feature of DB2 10.5, but was recently released so I thought that I would mention it here. Development is underway to allow it to work on other mobile devices as well.
Various Interesting Details
Various Interesting Summaries
The features below are some of the ones that are no longer available in v10.5. This only a partial list that I found to particularly interesting and you should read the complete list in the link above before upgrading.
Functionality gets marked as deprecated when a specific function or feature is supported in the current release, but is likely to be removed in a future release. Also it means that no further changes will be made to the feature. It is advisable to plan to discontinue the use of deprecated functionality. Here is a list of some of the deprecated features that struck me as important. The list below is only partial and you should review the complete list at the link above.
The DB2 upgrade portal is a great place to get all of the information that you will need to upgrade your database server to V10.5. It has all the instructions necessary plus tools to help you plan your upgrade. You should note that you can only directly upgrade DB2 from certain newer DB2 versions. If you are on DB2 v9.1 or earlier you will need to perform a two-step process where you first upgrade to DB2 v9.7 or 10.1 first.
If you would like to see my take on earlier versions please visit on my DB2 v9.1, DB2 v9.5, DB2 9.7 and DB2 10.1 articles. Also if you want to read more of a narrative about the newest features of DB2 v10, you can download the new DB2 10.5 eBook. If you notice any features you like that I did not include here, please add them to my Facebook Page or my db2Dean and Friends Community and share your thoughts about them.