DB2 11.5.0 – 11.5.7 New Features
Dean Compher
31 January 2022
Like many IBM software products, Db2 has gone to more of a continuous delivery model. Instead of large releases with lots of new features being released every couple of years, fewer new features are released on a much more frequent basis. Some new features are released in a Tech Preview mode that are available for you to try out and will be fully supported for production in a later release. My last new features article was about the initial release of v11.5.0 and this article will catch you up on the biggest features released since then.
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 some of the most useful features now available or in the roadmap with links provided for more in-depth reading. For a complete list of all new and changed features by mod pak, please see the What’s Changed Knowledge Center. You can also see recorded webcasts that give more in depth information on Db2 LUW Academy.
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.7, you should review the materials on the DB2 Upgrade Knowledge Center. 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 11.1 is going out of support on 30 April 2022, 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.x there are three editions. You can continue to use your previous editions like Workgroup Edition and Advanced Enterprise Server Edition. If you are expanding an existing database, you can purchase additional licenses of what you have, but if you purchase new licenses for a new system, you should purchase one of the new editions.
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.
For non-production environments, there is also an Authorized User License metric for Standard and Advanced editions, where you can license a set of users who cause work to be done in the database and as long as only those users use the database. With this type of license you don’t need to worry about the server resources except for the maximum size for Standard Edition.
Also, all the new 11.5.x 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 |
Community Support Only (on line forum) |
Regular Support |
Regular Support |
|
Uses |
Dev & Prod |
Dev & Prod |
Dev & Prod |
|
Metric |
N/A (Its free) |
VPC Non-prod Auth User |
VPC Non-prod Auth User |
|
Fit |
Developers |
Departmental |
Enterprise |
|
Caps |
4 cores 16 GB Ram |
16 Cores 128 GB RAM |
Unlimited |
With Db2 11.5.x, 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.
The current versioning number is in this format: V.R.M.F:
· V Version (e.g. 11)
· R Release (e.g. 5)
· M Mod Pak (e.g. 7)
· F Fix Pak (e.g. 0)
The current full version of Db2 is 11.5.7.0. Should a fix be released before the next mod pak then it will increment to 11.5.7.1. When a new mod pak is released the fix pak number is reset to 0.
These features are fully supported for use in production uses. Some first showed up as a tech preview in an earlier mod pak, but as of 11.5.7.0 are fully supported.
Use S3/Cloud Object Storage with Db2 Utilities
Several Db2 facilities now allow you to read and write files to S3 and Cloud Object Storage. Db2 can read files directly from S3 Storage. This means that Db2 can load a file into the database directly from Cloud Object storage. Certain types of files like backups can be placed directly into Cloud Object Storage. One use case is where you want to load data from an external organization, but don’t want to give them access to your data center. One of the organizations could subscribe to an object store such as AWS S3 and give the other access to read or write to the subscription. Then you could set up a process to check the S3 store periodically and load any new files that show up there. Another use case is to backup your database to a multi-region object storage and your backups are then stored in different parts of the country which is very good for Disaster Recovery.
The utilities that support Cloud Object Storage are LOAD, LOAD CLIENT, INGEST, BACKUP and RESTORE. Db2 can also place log archive files into object storage and external tables can read and write files to object storage. To see more information about these, see the Knowledge Center for the particular utility or facility. Amazon S3, IBM S3 Cloud Object Storage, Azure and Private S3 Storage are all supported platforms.
These new privileges and authorities can be granted or revoked at the schema level and apply to all relevant objects in the schema. In addition, any new objects that are created in the schema are automatically included. For example, if you grant SELECTIN privilege on schema MYOBJS to role READERS, then all members of READERS can select from the tables in that schema and if a new table is added later to MYOBJS then the members of READERS are automatically able to select from it. Some of the new privileges include SELCETIN, INSERTIN and UPDATEIN. As with other privileges they can be granted to users, roles or groups.
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.
Db2 has a new REST Endpoint that you can use to query and administer your database using a REST API. This way developers and other who prefer to interact with your Db2/LUW databases using a REST API can do so. You can either format new queries as part of dynamic API Calls or build RESTful services around individual SQL Statements that allow developers to use the queries without having to know SQL. They can pass parameters to these services so that you can query different rows or update different values. This also has some good security implications. The endpoint is downloaded as a container image and can be deployed in Docker, Kubernetes or RedHat Openshift. You can deploy one endpoint for each database or have one endpoint that queries multiple databases.
Authenticate to Db2 with JSON Web Token
You can now authenticate to Db2 using a JSON Web Token instead of a password. This is primarily for applications connecting to a database and not individual user connections. This is becoming a common way for applications or services in a Kubernetes environment to authenticate to a variety of other services that require authentication.
Database Machine Learning Functions and Procedures
You can now implement popular Machine Learning and Deep Learning functions within the database. Popular functions from Scikit-learn and TensorFlow are implemented. Frequently, when doing a task like training a machine learning model, a huge amount of data will be copied across the network from Db2 to the server where the application is installed. The application will then call many of the functions installed there such a test-train-split and linear regression. Instead, you could call those same functions in the database just as you would the built-in database functions like AVERAGE or SUM. And like executing those functions in the database you just transmit the result of the function to the application instead all of the rows read by the function.
The functions are not installed by default. Instead, there are a set of steps that the administrator executes to implement them. You can read more about the in-database functions at the link at the top of this topic.
Pacemaker Replacing TSA for HADR
Pacemaker with Corosync is replacing Tivoli System Automation as the preferred clustering software to control Db2 failover. Pacemaker is now shipped with Db2 and should be used for new implementations when you want to automate fail over. TSA continues to be supported.
Workload manager has been available for some time, but it did take some work to configure it and there are times when that is worthwhile. Adaptive workload manager does basic workload management for you with little or no configuration. It primarily calculates the maximum workload a Db2 database can handle without thrashing, and then queues any incoming queries that would exceed that threshold. When resources become available, it then lets the queued queries execute. This feature is already in production for Db2 Warehouse on Cloud and IBM Integrated Analytics System and is now available for all flavors of Db2.
This feature transforms and optimizes gremlin queries for analyzing data in your Db2 database. If you are using or developing an application that uses edges and vertices then you may want to look at this feature. Db2 provides 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.
Schema Level Backup and Restore (Columnar ONLY)
You can now backup the objects from a particular schema and then restore them to the same or a different database using the db_backup and db_restore commands. They are not part of the regular BACKUP DATABASE utility, but are instead Python scrips written by Db2 development for this purpose. They only work with Column Organized tables.
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. Please note that it would generally be a very bad idea to use any of these features in production databases while they are considered “tech preview”. Here are the features currently available in this mode.
Machine Learning in the Optimizer - Tech Preview
A machine learning feature has been added to the query optimizer in Db2 11.5 that can make performance improvements for some types of queries. The general concept is that it will learn from its mistakes, generally in more complex query plans. One example is where the optimizer created a query plan with several steps. For each step is estimates the number of rows (cardinality) returned from the step. When the query is executed, Db2 will capture both the estimated and actual cardinality from each step. When they are significantly different the optimizer can learn and create better and better plans as similar queries are subsequently run. In addition to cardinality, other factors can be improved. This does not negate the need for good statistics on the tables and indexes.
You can turn this feature on in your development environment using a registry variable and try it out on some of your complex queries. The functionality has been expanded in various mod paks, but it is still in Tech Preview as of 11.5.7
These features were fully available in 11.5.0 and described in my 11.5.0 article. It also contains a list of deprecated and discontinued features. Here is a list of the 11.5.0 (GA) features.
Query a delimited or fixed column file as a table without loading the data into the database.
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. Multiple performance enhancements have also been done in 11.5.x
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.
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.
IF EXISTS & IF NOT EXISTS
Prevent getting errors when creating a table that already exists or when deleting a table that is not there by using this syntax.
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.
Several new features have been added to make command line monitoring your database easier like dcmtop.
Upgrading to DB2 V11.5.7
The DB2 upgrade page is a great place to get all of the information that you will need to upgrade your database server to v11.5.7 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.7 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, 11.1 and 11.5.0 articles. If you notice any features you like that I did not include here, please add them to my Facebook Page and share your thoughts about them.