DB2 11.5 New Features
Dean Compher
26 July 2019
DB2 v11.5 was announced at IDUG on 4 June 2019 and has lots of new features and improvements, some of which you can begin taking advantage of without doing anything other than upgrading your database. It became available for download on 27 June. As with the past “features” articles, this is a summary of the new features in v11.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 that allow in depth reading about them. A few older features have been discontinued or deprecated, so look over those to make sure you are not affected. I also 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 or in the roadmap. For a complete list of all new and changed features, please see the What’s New Knowledge Center. You can also see recorded webcasts that give more in depth information on Channel DB2.
Please see the “Functionality in DB2 product editions and DB2 offerings” to see a chart of what major features are in which editions. The roadmap for upcoming features are in the AHA database that anyone can access. At this site you can also vote on the features you would like to see in an upcoming release and request a new one if something you want is not there.
Before you even start planning your upgrade to DB2 v11.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. Considering that Db2 10.5 is going out of support on 30 April 2020, now is a good time to start planning your upgrade to Db2 11.5.
This article is organized as follows:
· New Editions (Packaging Changes)
· Tech Preview, Beta, and Future Release
With Db2 11.5 there are three editions. You can continue to use your previous editions like Workgroup Edition and Advanced Enterprise Server Edition, but if you need new licenses or you need to add capacity to an existing database, you will need to purchase one of the new editions with some limited exceptions. You can talk to your friendly IBM representative to get information about the migration of your existing editions to one of the new ones.
With these new editions PVU (processor value unit) licensing is being replaced by the much simpler VPC (virtual processor core) licensing. Now you just need to know how many cores you have on your server, VM or cloud server and you can figure out a price. You no longer need to determine the CPU model number, number of sockets in the server, etc.
Also, all the new 11.5 editions have the same advanced features like BLU, Compression, Federation, etc. So talk to your friendly IBM representative about how you can migrate your existing licenses to one of the new editions. The only differences between the new editions are the maximum resources they can consume and the type of support you get.
|
|
Community |
Standard |
Advanced |
|
Support |
· Free, no support -or- · $50US/core/month |
Regular Support |
Regular Support |
|
Uses |
Dev & Prod |
Dev & Prod |
Dev & Prod |
|
Metric |
N/A |
VPC |
VPC |
|
Fit |
Developers |
Departmental |
Enterprise |
|
Caps |
4 cores 16 GB Ram 100 GB storage |
16 Cores 128 GB RAM |
Unlimited |
With Db2 11.5, you always get the Community Edition when you download the software. You can continue using it with that license or download a license for the Standard or Advanced if you own it, apply the license and then your install becomes that edition. You have your choice of where you get the software – Passport Advantage, Fix Central, or other IBM download site. You always get the same copy of Db2 11.5 no matter where you start. So, I recommend getting your software from Fix Central if you aren’t using Docker so that you install the latest fixes and mods the first time. If you are using Docker, I recommend getting your Db2 11.5 image from DockerHub Db2 and then if you want to run Standard or Advanced editions apply your license. On the DockerHub Db2 page scroll down to see the instructions for applying your license to the container.
One of my favorite new features is the external table. This feature allows you to create a table based on a file and use the data in the file in SQL. This means that you can query data in a file in Db2 without having to load it first. You can also extract data into files using external tables and SQL. The file can be local on the database server or in object storage or other cloud storage. It can be a delimited file or have fixed length fields.
You can explicitly crate an external table and use it over time, or you can you can just refer to the file in statements where you would normally use a table. If you have a file that has the same columns as an existing table then you can use the LIKE keyword to create the external table instead of having to list all the columns and their datatypes.
BLU Tables with CLOB and BLOB
You can now create column organized tables with CLOB and BLOB columns. This was a restriction in earlier versions, where tables with those types of columns had to be row organized.
Now that BLU/column organized tables are available in all new editions of Db2 in version 11.5, this feature is applicable to all. As you may know, you can create a column organized table, but until enough data is inserted, the compression library will not be created and the data at the beginning of the table is not compressed. With the automatic compression feature, Db2 11.5 will automatically look for uncompressed data in column organized tables and compress it once the compression dictionary is built. Other compression improvements have been made for inserts into column organized tables as well that should improve performance.
With Db2 11.5 you no longer need to buy Federation Server to allow access to non-IBM data sources like Oracle, SQLServer and even some no-SQL sources. That functionality is now in all the new editions of DB2 11.5. With 11.5 you can access additional data sources, and, in the future, data virtualization technology will be added to allow processing to be done closer to the edge where the sources live.
IF EXISTS & IF NOT EXISTS
When creating a table, you can now specify the “IF NOT EXISTS” parameter to tell Db2 to create the table if it doesn’t already exist, but not to throw an error if it already exists. That is, you just want a table there after the command and you don’t want an error if it is already there. Be careful when using it because the existing table with the same name may not have the same columns/datatypes as the CREATE TABLE specifies.
When dropping a table you can specify “IF EXISTS” parameter to avoid getting an error when you try to drop a table that isn’t there. These new parameters are most useful in scripting where just want the table to be there or not be there and you don’t care about the previous state.
JSON
You can store JSON documents in Db2 columns including VARCHAR and CLOB columns and query that data using Db2 functions that can make the data appear as relational data. These functions are ISO compliant and actually appeared in the last mod pack of Db2 11.1. See my JSON article of a summary of the interesting things you can do with JSON in Db2 including indexing fields in the JSON document. For much more on this topic see the Db2 v11 JSON Highlights eBook. Db2 11.5 introduced the QUOTE_IDENT and QUOTE_LTERAL functions to make it easier to use strings from JSON documents in SQL statements.
Several new features have been added to make command line monitoring your database easier. The new features include:
· New monitoring metrics for determining failure rate of SQL statements
· Improved table functions provide access to information without having to perform joins
· Improved table functions provide direct access to WLM statistics
· New monitor element for concurrently executing activities
· New monitor elements report thread and memory usage for workload and service class objects
Various Interesting Details
The DB2 11.5 release is intended deliver features in a more continuous manner. Some of the upcoming features will initially be delivered as a Tech Preview to allow you to try them out before they are ready for prime time. Other’s will just be delivered, but timing is currently unknown. Please note that it would generally be a very bad idea to use any of these features in production databases while they are considered “beta” or “tech preview”. Here are the features currently available in this mode.
Advanced Log Space Management - Tech Preview
Some applications will do an update here and there but go a very long time between commits. This makes for transactions that span many logs and can cause you to run out of active log space, because Db2 can’t archive any log starting with the one containing the first update of your long transaction. To solve this Db2 will watch for this condition and copy the offending log records to a file outside of your active log set, allowing the active log files to be archived. You turn this on with a registry variable as described in the link above.
Machine Learning added to Optimizer - Tech Preview
A machine learning algorithm is included in Db2 11.5 that can make amazing performance improvements for some types of queries. You can turn it on in your development environment using a registry variable and try it out on some of your complex queries. As mod packs are released additional improvements will be added to this feature. See this link to learn more about the tech preview.
Graph Database Capability – Future Release
This is coming in a future release to allow you to support graph queries and SQL on the same data. We will provide a layer between the graph application and Db2 that will support storing graph data in tables. It will support open-source graph with Gremlin language and Tinkerpop framework, and will also allow you to run SQL analytics on graph data.
Augmented Data Explorer w/ Natural Language Query - Beta
Allows those unfamiliar with the data in any Db2 database to explore and visualize the data. In addition, it allows those unfamiliar with SQL to query the database by typing queries in natural language. It’s kind of like an Alexa or Google Home for the database. This is a tool you can download and let anyone in your organization access via a web browser. It provides key insights through automatically generated visualizations and natural language summaries. Down load it now and give it a try!
Adaptive Work Load Manager – Future Release
This feature is already in production for Db2 Warehouse on Cloud and IBM Integrated Analytics System and will become a feature in Db2 in an upcoming release.
Schema Level Authorization – Future Release
You will be able to grant new authorities at the schema level such as SELECTIN that will grant select authority to all current and future tables in a schema.
Reduced Logging for BLU – Future Release
This feature is for column organized tables and will function like the Not Logged Initially feature for row organized tables. That is, you will be able to insert lots of data and not swamp your transaction logs. This feature is already undergoing testing, but it is not clear when it will be available.
Various Interesting Summaries
The features below are some of the ones that are no longer available in v11.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. Only three items have been deprecated in 11.5.
Non-PureScale Operating Systems Supported or Removed from Support
· Linux
o RHEL 7.(5), SLES 12 SP(3), Ubuntu 16.(4) LTS
o Intel x86-64, System Z, Power 8, 9 (compat-mode only) LE
o Potential futures for newer RHEL , SLES, Ubuntu
· AIX
o AIX 7.1 TL(5) SP(3): Power 7 and 8
o AIX 7.2 TL(3) SP(2): Power 7, 8, and 9
· Solaris – removed
· CentOS – removed (for now, anyway)
- 2012, 2012R2, 2016 (x86-64)
- Potential future – Newer Windows release
- 8, 8.1 and 10 (x86-64)
Upgrading to DB2 V11.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 v11.5. It has all the instructions necessary plus tools to help you plan your upgrade. If you are running DB2 Enterprise Server Edition (ESE) or Advanced Enterprise Server Edition (AESE) then v11.5 allows you roll forward through logs created in v11.1in 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 11.1 ESE or AESE. Here is why. You can take an online backup from your old instance and restore it into a new 11.5 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.5 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 10.5 and 11.1 articles. 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.