DB2 10.1 New Features

Dean Compher

31 May 2011



DB2 v10.1 became available on 30 April 2012 and has TON of new features and improvements, some of which you can begin taking advantage of without doing anything other than upgrading your database.  As with the past “features” articles, this is a summary of the new features in v10.1 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 and to provide links you can click to do more in depth reading on them.  A number of features have been discontinued in v10.1, most notably Control Center and many of the GUI tools that you access from it like Command Center.  I summarize these discontinued and deprecated functions in this article. 


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 for DB2 Version 10.1 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 and that some features are separately purchased for some editions.  Please see the “Functionality in DB2 features and DB2 Product Editions” to see a chart of what major features are in which editions.



Time Travel Query (TTQ)

The Time Travel Query (TTQ) allows you to tell DB2 to track changes to tables and then issue queries as if they were being submitted at some time in the past, providing results as they would have appeared at that past time.  This feature is available for all tables upgraded from earlier versions by merely altering the tables that you want to have the TTQ capability.  Of course you can also create tables with this feature with the CREATE TABLE statement. 


Adaptive Compression (a.k.a Storage Optimization Feature)

DB2 already allows you to compress tables, indexes, backups, in line LOBs and other objects, and in v10.1 the compression capability has been enhanced even more with a new compression feature called adaptive compression.  The current compression is quite efficient both from a storage saving standpoint and a server resource standpoint and in some cases can improve performance of a system.  In DB2 v9.x, there was a single compression dictionary for the entire table.  With the new adaptive compression a dictionary is also created on individual pages that can benefit from being compressed, allowing additional compression that would not be done using the table level dictionary either because duplication is only seen on a page or new patterns emerge that were not observed at the time the table dictionary was created.  It should be noted that for efficiency’s sake, that page compression is only performed as a page becomes full and if significant space savings can be achieved.  As you have probably read, Moore’s Law continues to be in effect with processor speeds doubling about every 18 months.  However, the speed at which data can be transferred from disk into RAM continues to significantly lag with the gap growing wider all the time.  It is my humble opinion that DB2 compression is a great way to use some of the increased processing power of your server to increase I/O speed by getting more rows with every physical read. 


You don’t have to take the time to reorganize tables to begin getting the benefit of compression.  You can just turn it on for an existing table.  As pages are updated with new or changed rows, the adaptive compression feature will compression the page if that makes sense.  Also as was done in DB2 V9.7, after enough new data has been added to an existing table, a dictionary will be created for the table and new rows will be compressed using traditional compression as they are inserted or updated. 


Multi-Temperature Storage (MTS)

This feature allows you to easily segregate your data onto different classes of storage with your most important data being on your fastest storage and your less important data on cheaper storage.  It even allows you to move data between different storage classes as the data ages and becomes less important to the overall performance of your system.  I see this new feature being most useful for systems with solid state disk (SSD) devices.  Many SSD systems will have two storage groups - one for directories on SSD and the other for directories built on disk file systems.  This allows you to keep the data that impacts performance the most on the more expensive SSD devices and moving the older data to your disk based media.  You need to use a few DB2 components to implement an aging scheme.  Before v10.1 when you used automatic storage in your database you assigned all containers (directories) to the database in one big group.  Now you can create different groups of storage in your automatic storage database, assigning containers (directories) to each group, with the containers in each group residing in different storage media.  As you create tablespaces you can assign them to different storage groups depending on the importance of the data.  To allow easy aging of data in a table, you need to use table (range) partitioning and put different partitions into different tablespaces.  Data will be partitioned by date, for example, partitions can be created for rows for a particular month, quarter, or year.  As a partition ages, its tablespace can be easily moved to a different storage group using the ALTER TABLESPACE … CHANGE STOGROUP command.  When this command is executed, DB2 automatically moves the data to the new storage group with all data available during the entire movement of data.  The commands that implement this process can be scripted and run automatically using your favorite scheduler such as the Data Studio Web Console.  The behind the scenes the data movement process is similar to the data redistribution done when you add a new container and has low overhead on the system.  Don’t forget that you can also reassign the tablespace to a different buffer pool when you move it to a new storage group, helping performance even more. 


You should note that tablespaces need to be automatic storage tablespaces to participate in an MTS configuration, but existing tablespaces can be easily converted using the ALTER TABLESPACE … MANAGED BY AUTOMATIC STORAGE command.  You can also see which paths are assigned to which storage groups using the ADMIN_GET_STORAGE_PATHS table function.  Finally some of the commands that you will use for storage groups are CREATE STOGROUP  and ALTER STOGROUP. 


Cluster Tables by Insert Time

If you have queries that tend to search through sets of rows in a table based on time inserted, then you may be interested in this type of table.  It is similar to a Multi-Dimensional table in that rows inserted near the same time will be placed in the same physical extents.  This makes it very easy for DB2 to only scan extents of data in a table that meet your time based query and that can greatly improve the performance of those queries.  You just create the table as normal, but use the ORGANIZE BY INSERT TIME clause.     Since Insert by Time tables are just a special case of the Multi-Dimensional Cluster, they are described together in the MDC Information Center page.  


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.


High-Speed Data Ingest

This new utility allows you to add data to your tables at a very high rate without suffering availability issues that the LOAD utility can cause.  It is great for databases or warehouses that need to continuously add large amounts of data to the database, but can not afford any availability impacts including problems with locked rows.  It can be run on a client or on the database server and ingest data from files or pipes, and allows complex queries to ingest data from other databases. 


Various Interesting Details


Various Interesting Summaries


Row and Column Access Control

This new security feature allows you to very easily control who accesses what data within a table.  This is also known as fine grained access control.  The Label Based Access Control (LBAC) feature introduced in DB2 v9 is still available and provides security features not available in RCAC.  However, RCAC tends to be easier to use. 


DB2 pureScale Enhancements

The biggest change for DB2 pureScale is that it is now just an option in the regular DB2 install process rather than a separate version as it was in v9.8.  Of course you have to have the license for the pureScale feature to make it work.   Another of other enhancements have been made including giving you the option to use Ethernet RoCE for the high speed interconnect on AIX systems in addition to Infiniband.  This feature was already available for Linux.  Other enhancements include support of Redhat Enterprise 6.1 and the support of range partitioned tables.  There are several others and you can read about at the above link.


DB2 Performance Enhancements

One of the major improvements in the v10.1 release is making DB2 consume fewer CPU resources thereby making the DB2 engine more efficient.  For the most part this improvement is transparent and just starts happening when you upgrade. There are also several changes to DB2 and the optimizer that make queries faster and more efficiently such as improved RUNSTATS and better use of indexes.  There are also enhancements to query parallelism and enhancements to take better advantage of IBM Power 7 hardware.  The list is quite extensive and I recommend that you review the complete list on the performance enhancements index in the information center. 


SQL Compatibility

As with all DB2 9.x releases, v10.1 makes DB2 even more compatible with Oracle.  This means that if you are used to developing applications that run on Oracle databases, then you can write applications to run on both DB2 and Oracle without learning a new interface.  DB2 supports nearly all of the Oracle dialect and objects, and does it very efficiently because the interpreter has been expanded and no translation is done.  This feature also makes it very easy to move from an Oracle database to the best database on the planet, namely DB2!


Event Monitor Enhancements

·         There is a new event monitor object that tracks changes to the DBM and DB configurations as well as DDL changes such as DROP INDEX or ALTER TABLE.  This can be quite useful to keep a history of what changed and when. 

·         The unit of work event monitor can now collect an “Executable ID” to see which process or thread executed the statement.

·         All event monitors now support the WRITE TO TABLE target which means that you can skip post-processing on all event monitor output. 

·         Existing event monitors that write to tables can be altered to capture additional data groups.

·         Event monitor tables created in previous DB2 versions can be upgraded to the new format using the EVMON_UPGRADE_TABLES procedure.

·         You can now prune data from unformatted event tables.



The features below are some of the ones that are no longer available in v10.1.  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.1

The DB2 10.1 upgrade portal is a great place to get all of the information that you will need to upgrade your database server to V10.1.  It has all the instructions necessary plus tools to help you plan your upgrade.  You should note that you can only directly upgrade DB2 v9.5, v9.7, and v9.8 (pureScale) to v10.1.  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 and then to v10.1.




If you would like to see my take on earlier versions please visit on my DB2 v9.1, DB2 v9.5 and DB2 9.7 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 eBook.  You can download a free copy of the latest version of DB2 Express-C and try many of these new features yourself.  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