DB2 11.1 New Features

Dean Compher

27 June 2016

DB2 v11.1 became available on 16 June 2016 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 v11.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 with links you can that allow in depth reading on them.  A few older features have been discontinued or deprecated, so look over those to make sure you are not affected.  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 Info Center.  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 product editions and DB2 offerings” to see a chart of what major features are in which editions.

Before you even start planning your upgrade to DB2 v11.1 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. 

DB2 BLU Enhancements

Since it was introduced about three years ago this DB2 technology has helped many customers.  It will 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 it 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.  This technology is also in the dashDB Analytics cloud offering.  With the 1.1 release several enhancements have been made around BLU for DB2 and dashDB.

• You can now have Column Organized Tables in the DB2 Distributed Partitioning Facility (DPF) systems. You are no longer limited to one partition on one server when you want to use BLU technology.  You can now have column organize tables that are spread across your massively parallel database. 
• NOT LOGGED INITIALLY can now be used with the CREATE TABLE and ALTERTABLE statements that have column organized tables. 
• Row and column access control (RCAC) now works with column organized tables.
• Several analytics functions from our Pure Data for Analytics system (Netezza) have been implemented in DB2.
• Columnar tables now supports IDENTITY and EXPRESSION generated columns.

DB2 Native Encryption Improvements

• Native Encryption is now included in ALL DB2 editions!!!  You no longer have to pay extra for it.
• With v11.1 you can now manage your DB2 native encryption master keys using a centralized key store.  This allows you to store your DB2 encryption master keys with all of your other keys for easy management.  You need to use an approved system that uses Key Management Interoperability Protocol (KMIP) 1.1.  We also support HSM. 

DB2 pureScale Improvements

In DB2 v11.1 the install process pureScale across the cluster has been significantly simplified including the deployment of the GPFS file system that you get with the pureScale feature.  The install process also includes a quick pre-deployment feature that validates all hosts in the cluster.  For more details on the install and the improvements described below, please see the DB2 pureScale Feature enhancements page. 

• DB2 v11.1 allows SYNCH and NEARSNYCH modes when using HADR between clusters of pureScale systems.
• DB2 pureScale can now be run under RHEL 7.2.
• There is a new registry variable called DB2_DATABASE_CF_MEMORY that can be set to AUTO to allow automatic memory allocation.

Various Interesting Details

• Compression of DB2 backups and archive logs is now done using the Nest Accelerator hardware on Power 7+ and Power 8 servers, saving resources for compression of those files. You will need to set the DB2_BCKP_COMPRESSION registry variable to allow this though. 
• The ADMIN_MOVE_TABLE procedure now has the REPORT option to monitor the progress of table moves and the TERM option to terminate a table move that is in progress. 
• The relocatedb command now has the –g option that allows you to create a script to relocate the database.
• If you need to enable fast pre-allocation for new and resized log files you now have the DB2_USE_FAST_LOG_PREALLOCATION registry variable. 
• You can now do an INPLACE reorg for individual partitions of a partitioned table if the table has no non-partitioned indexes. 
• DB2 now allows you to use the plus sign (+) for the outer join operator.  See that item on the SQL Compatibility enhancements page for more information. 
• The INGEST command now supports new options including DELIMITER PRIORITY CHAR, NO CHAR DELIMITER, and RADIX POINT IMPLIED that are already supported by the LOAD and IMPORT commands.
• The INGEST, LOAD, BACKUP, RESTORE and CATALOG STOREAGE ACCESS commands now allow you to access IBM Softlayer Object Store (SWIFT) and Amazon Simple Storage Service (S3) directly from the commands. 
• When databases are upgraded to v11.1 log file names will no longer be changed from .LOG to .MIG.
• Size ranges have been increased for several DB and DBM configuration settings. 

Various Interesting Summaries

• Several new functions, data types, synonyms and other objects have been added like the BINARY data type and the ADD_DAYS function.  See the SQL Compatibility enhancements page for this extensive list.
• Several performance enhancements and a number of new features have been introduced in the IBM data server clients and drivers.
• Several catalog views and built-in administrative views and routines have been changed in this release.
• A number of behaviors have been changed that affect application development.

DISCONTINUED FEATURES

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

• RAW LOGS – You will not be able to use raw devices for logs in DB2 v11.1

• Cubing Services - Please migrate to Cognos Dynamic Cubes.

DEPRECATED FEATURES

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. 

• db2top -  Begin using dsmtop instead. 
• Private Sort Memory – You should now start using shared sort memory instead.
• ENCRYPT, DECRYPT_BIN, DECRYPT_CHAR -  Consider using DB2 native encryption.  It is now free in all editions
• TEXT ANALYTICS -  You can continue to use it, but no further enhancements will be made. 

New Editions and Edition Changes

• While the free DB2 Express-C Edition continues to be available, the Express Edition and Work Group Server Editions are being merged and the new edition will just be called Workgroup Server Edition (WSE).  WSE now has additional features including pureScale licensed for one standby node, table (range) partitioning, Encryption, Multi-dimensional Clustering and Federation to DB2 and Informix. 
• DB2 Enterprise Server Edition (ESE) also gets new features with DB2 11.1.  It now has pureScale for one standby node, table (range) partitioning, Encryption, Multi-dimensional Clustering and Federation to DB2 and Informix. 
• There are two new DB2 Editions that that are meant to make it easier for you to move your licenses between regular on-premises deployments and cloud (on-prem or hosted) deployments.  They are the new “Direct” editions.  They are priced by the new Virtual Processor Core metric.  You pay a monthly fee to use them, but you do not need to install ILMT.  These editions are not only great to move licenses around in a hybrid cloud environment, but allow you to purchase short term licenses for workload spikes or experimentation purposes.  There are two Direct Editions:
• DB2 Direct Standard Edition – has the features of Workgroup Server Edition.
• DB2 Direct Advanced Edition – has the features of Advanced Enterprise Server Edition.

Upgrading to DB2 V11.1

The DB2 upgrade portal is a great place to get all of the information that you will need to upgrade your database server to v11.1.  It has all the instructions necessary plus tools to help you plan your upgrade.  You should note that you can upgrade directly from DB2 v9.7, 10.1 and 10.5.  If you are running DB2 Enterprise Server Edition (ESE) or Advanced Enterprise Server Edition (AESE) then v11.1 allows you roll forward through logs created in v10.5 fix pack 7 and newer in non-DPF environments.  This means that you no longer need to take an off line backup prior to your upgrade if you want to make sure that you can restore your backup into the new version.  It also allows you to minimize the outage time if you have room for a second database and are running 10.5 with fp7 or newer and ESE or AESE.  Here is why.  You can take an online backup from your old instance and restore it into a new 11.1 instance while the old database continues to run.  Once the restore is finished, you can stop the old database then just roll the logs forward from the old database on the new one and point your applications to the 11.1 database as soon as you complete the roll forward. 

***

If you would like to see my take on earlier versions please visit on my DB2 v9.1, DB2 v9.5, DB2 9.7, DB2 10.1 and DB2 10.5 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.  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