DB2 10.5 New Features

Dean Compher

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 Control Center and its related GUI tools like Command Center.  I summarize these discontinued and deprecated functions in this article as well.


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. 

  • You can now use HADR to keep a second pureScale cluster updated from a primary cluster.
  • New DB2 members can be added while the cluster is up and running with no down time.
  • Adding new members off line does not require an off line backup to be taken any more.
  • You can now restore DB2 Enterprise Edition backups between pureScale and non-pureScale environments with off line backups as long as both instances are at v10.5.
  • You can restore a backup to a pureScale cluster with more members than the cluster on which the backup was taken. 
  • Explicit Hierarchical Locking improves pureScale performance in cases where one member updates a lot of rows that are not used by other members during some window. 
  • Self Tuning Memory Manager can manage each pureScale member individually.  This is especially useful when different members tend to run different workloads. 
  • You can now define Member Subsets that allow you direct work from a particular client to a group of database members in a pureScale cluster.  For example, you can direct application A to database members 1-2, application B clients to members 3-5, and application C to member 6. 
  • In-place (online) reorgs can now be done in DB2 pureScale environments.
  • DB2 10.5 and above pureScale instances can now have fix packs applied without out taking down the entire cluster with the new online fix pack updates.  With this feature, you can take one member down at a time, upgrade it to the new fix pack, put it back into the cluster and repeat this for all other members. 



HADR Improvements

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.

  • There are new HADR Monitoring Elements.
  • Starting in v10.5 it is recommended that you change the initialization of HADR to use the HADR_TARGET_LIST configuration parameter even where you only have one standby.  In some future release this will be required. 
  • New HADR implementations on 10.5 databases will have log spooling on by default with the default value of HADR_SPOOL_LIMIT set to AUTOMATIC.  For non pureScale HADR implementations upgraded from previous versions, log spooling behavior will remain the same.


JSON Preview

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. 


IBM Mobile Database

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

  • NOT ENFORCED primary key and unique constraints added to the list of Informational Constraints.  Informational Constraints are a way to tell the DB2 Optimizer about the nature of data in the table while not incurring the overhead of enforcing the constraints or space for indexes. 
  • The row length of a table can now exceed the page size using Large Row Support.  So you can now create a table in a 4K page size tablespace that has two CHAR(3000) columns and fill them both. Existing tables can be altered to take advantage of large row support. 
  • Prior to v10.5 you could only have one row with a null value in a column with a unique index on that column.  In v10.5 you can say that since NULL means “unknown value” you can have multiple rows with a NULL value in that column using the EXCLUDE NULL KEYS parameter on the CREATE INDEX command.
  • The db2iupdt command now automatically runs the db2ckupgrade command before it does anything to your instances.  If you have a lot of databases in the instance or very large databases this can make the db2iupdt command take a long time.  Execution times of over an hour have been reported. This behavior actually started in v10.1 but I only recently found out about it. 
  • The db2pd and db2support commands have some new parameters to enhance troubleshooting
  • In-place (online) reorgs are now supported for tables that use adaptive compression.
  • Reclaiming extents on Insert Time Cluster (ICT) tables now consolidates sparsely allocated blocks into a smaller number of blocks. 
  • Use the new RANDOM option on the CREAT INDEX command to create random ordering for indexes where you get a lot of contention on leaf pages.  When you do lots of inserts on a table with an index on a sequentially numbered column such as an order number, certain leaf pages can get a lot of locking activity.  pureScale can make this problem worse.


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. 


Upgrading to DB2 V10.5

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.



HOME | Search