Db2 11.1 Enhancements Since GA

Db2 V11.1 was released in June 2016, with many new features which have already been covered by IDUG in a number of articles and recordings -

Db2 11 : Coming Soon To a Server Near You : http://www.idug.org/p/bl/ar/blogaid=496

Db2 11 BLU Performance : http://www.idug.org/p/bl/ar/blogaid=508

Db2 11 Spotlight Session : http://www.idug.org/p/bl/ar/blogaid=498

Db2 11 Update and Recovery Enhancements : http://www.idug.org/p/bl/ar/blogaid=510

What’s new for BLU in Db2 11 : http://www.idug.org/p/bl/ar/blogaid=604

Since then a lot has happened in the Db2 world, not least is the wholesale renaming of the DB2 family including the change from DB2 for LUW to just Db2.

But more relevant to the current article is the new way that IBM delivers DB2 product updates. By taking a brief look at the page for downloading Db2 fixpacks ( https://www-01.ibm.com/support/docview.wss?uid=swg27007053 ), an immediate change in terminology can be seen for Db2 11.1. Instead of being called “Fix Pack <n>” we now find “Mod <n> Fix Pack <m>” (and sometimes “iFix<nnn>”). This is because IBM is now officially differentiating between Db2 updates which purely contain fixes and those which also provide new functionality (“Mod” = “Modification”). So far IBM has release two “Mod Packs”, containing both new functionality and fixes.

It is expected that at some stage the major version numbers will change (perhaps to 11.5 or 12.1, although the numbering of DB2 releases has always been a mystery to me), and after that point the 11.1 updates will be fixes only. Then the “Mod <n>” would stay fixed and the “Fix Pack <m>” would be incremented. Until that point, it is expected that every Db2 update will contain a Mod increment, as new functionality is made available via the continuous delivery development model now being followed by the Db2 lab.

This new clarity about what an update contains is also seen in the version returned by db2level. So, Db2 11.1 Mod <n> Fix Pack <m> will be see as 11.1.<n>.<m> e.g. Mod 2 Fix Pack 2 is

Given that we now know officially that both Mod 1 and Mod 2 contained new functionality, let us look at some of what has been provided. In previous versions, Fix Pack 1 tended to be “functionality that wasn’t ready at GA”. While some of this may be the case with 11.1, it does look like the continuous delivery development model is bearing fruit with many of the changes in these first two Mods having only recently been requested via the RFE (Request For Enhancement) and TAB (Technical Advisory Board) processes. In particular, the fast tracking of JSON functionality into the product has been as a direct result of input from the Db2 TAB.

BLU Enhancements

Probably the biggest enhancements to the BLU environments are those which improve performance of inserts and updates. There are two big improvements in this space. Firstly, in Mod 1 improved synopsis table handling meant an improvement in performance of inserts and updates across small numbers of rows (typically where less than 100 rows are being inserted or updated in one transaction). Then in Mod 2 functionality was added to allow parallel processing of inserts on columnar tables.

These enhancements are likely to be the first of many, as IBM works to enable Db2 as an HTAP (Hybrid Transactional and Analytics Processing) platform.

PureScale Enhancements

There aren’t so many pureScale enhancements as there are BLU enhancements. What improvements have been made are largely in continuing to lift restrictions within pureScale. For example, the ability to upgrade to a new version without having to take an offline backup or reinitialize any HADR standbys has also been extended to pureScale environments. Also the ability to perform a database rebuild is now available with pureScale.

There is also faster crash recovery of pureScale members, with better parallelism when recovering the global lock manager and an improved hashing algorithm. This will be most beneficial in very large pureScale environments, where recovery of a crashed member can take a considerable amount of time.

HADR Enhancements

There haven’t been a lot of HADR enhancements in Db2 recently, so it is great to see some really important improvements appearing now.

Probably the biggest of these is the fact that it is no longer necessary to rebuild the HADR standby after a version upgrade. This is a consequence of the work previously done to eliminate the need for an offline backup before upgrading a Db2 version. This change works for upgrades from DB2 10.5 to Db2 11.1 Mod 1 and higher.

Another big improvement in the HADR space is that now the log buffer shipping connectivity can be secured using SSL. This closes a big hole in the security of a Db2 system using HADR. Currently this functionality is not for power Linux and zLinux, although these platforms should be supported soon.

The final big improvement is the ability to recover a single tablespace on an HADR standby following an event that renders it unavailable. A typical example of when this can happen is when a LOAD (COPY YES) has been applied to the primary database and the load copy file is not made available to the standby in a timely fashion (either the load copy was not written to a shared file system or connectivity to the shared file system was lost during the restore). Previously a full rebuild of the HADR standby was required to rectify this situation. Now the tablespace in question can be recovered onto the standby preventing this potentially lengthy period without a complete failover.

JSON Support

Previously Db2 had introduced some support for JSON via the introduction of the DB2 Wire Listener, supporting some of the MongoDB JSON APIs. However, this functionality proved to be cumbersome and slow, and really did not take off. Within the Db2 engine some work had been done to provide functionality for accessing JSON documents and transforming them between text (JSON) and binary (BSON) formats. These functions have now been externalized, as a first step towards a new wave of JSON support in Db2 which we can expect to see over the next year or so.

From the perspective of Db2 adoption for new applications this is probably the most critical piece of work being undertaken within the Db2 space at the moment, since so much new application functionality relies heavily on JSON support. For example, most mobile and dynamic web applications are based around JSON-based RESTful services. In addition, there is a new breed of analytics applications starting to appear which use JSON as a means of data transmission e.g. Big Data ETL tools such as Sqoop tend to use JSON formats.

Federation Enhancements

Federation support was a part of Db2 which had for a long time been in the doldrums. However in recent times it has enjoyed a renaissance, largely because data volumes for certain analytics applications have grown to a size that makes copying data impractical and thus the data must be accessed in situ. The first indication of this was that the InfoSphere Federation Server product was integrated with the standard Db2 installation process, rather than there having to be a number of separate installs for the various federation components. This integration work has continued, with ODBC installation and configuration added to the standard Db2 install in Mod1. Then in Mod2, further enhancements were added including SSL support for federation to Db2 family databases and native support for MySQL and PostgreSQL databases (rather than relying on generic ODBC support).

Other Enhancements

As well as these major areas of improvement there have been other improvements across many areas of Db2. Here are some of the highlights -

  • The previously mentioned improvements in crash recovery speed for pureScale members is only part of a lot of improvements in crash recovery processing, including ability to connect to databases while the backout phase of crash recovery is still taking place and allowing connections to a database being brought online during HADR takeover while the recovery is still in flight. These changes continue to make Db2 systems more available even when problems occur.
  • Db2 now has a native BOOLEAN data type available. This has been a requirement for a long time, to make migration from other databases easier. Support for BOOLEAN variables in routines was introduced in Db2 10.5, but now we can define a BOOLEAN column in a table as well.
  • A lot of work has been done to improve the various flavours of GROUP BY both row and column based tables.
  • In BLU tables it is now possible to alter a VARCHAR column to a longer maximum length
  • WLM has been improved, particularly in better management of workloads based on CPU consumption (now available at an overall database level) and total run time (now available without taking wait time into account).

More details of these and all the other enhancements can be found in the DB2 Knowledge Center.

Finally, the DB2 Early Access Program has been opened up so that anyone who wishes can download and experiment with new Db2 functionality before it is officially released. This fits in well with the new continuous delivery development method, and allows more people to provide feedback on new functionality under development as soon as possible. Check this out at –


Recent Stories
Db2 11.1 Enhancements Since GA

DB2 12 for z/OS - Two Highly Requested SQL Enhancements

PBR RPN explained