What's New with Db2 for i 7.5

The announcement of a new software release is an exciting time since you get to discover all the new enhancements that can make your life easier as a software user or consumer. Without further ado, let’s dive into some of the new capabilities that are available with the Db2 for i 7.5 release.

You will probably quickly notice that Db2 for i 7.5 doesn’t contain any heavy hitters like Temporal Tables & RCAC that were delivered in prior releases. However, the latest release does contain an impressive of collection of smaller improvements that enhance SQL development, data security, Db2 performance & scalability, and DBE enablement. So instead of having to make room for a couple of big tools in your IBM i toolbox, you’ll be dropping in an assortment of smaller tools. 

 

On the SQL development front, you will find a new data type and a built-in function. Boolean is the new data type available to SQL developers. This new Boolean type will enable your programs to be more strongly-typed and easier to understand.

CREATE TABLE supplier (suppID INT, suppName VARCHAR(10), 

                       activeSupp BOOLEAN);

INSERT INTO supplier
   VALUES(1, 'ABC CO', TRUE),
         (2, 'ACME', FALSE),
         (3, 'IBM', NULL);

SELECT suppName FROM supplier WHERE activeSupp IS TRUE;

 

The new TRY_CAST function enables easier identification of invalid column values since it returns the NULL value when invalid data is detected as opposed to signaling a hard failure like the CAST function.

 

The IBM i 7.5 release contains several enhancements that tighten access to the valuable business data stored in your Db2 for i databases. In addition, the new RESTRICT ON DROP clause can be used to prevent accidental deletion of your data. Once this setting is added to your Db2 tables, you no longer have to worry about someone accidentally wiping out a table by typing in the wrong table name on an SQL DROP statement or the DLTF CL command. Any tables using this setting cannot be deleted until someone runs an SQL ALTER statement to remove the setting.

 

The Db2 performance and scalability improvements provide constructs to allow you to both scale the size and performance of your databases. The maximum size of Db2 indexes has been increased from 1.7 TB to 4-16 TB.  The new maximum size is dependent on the logical page size of your existing indexes. 16 TB is the new max size for SQL indexes since they're created with a 64K logical page size. The good news is the maximum size does not require a rebuild of your existing indexes. This larger index maximum size removes one of the last limits to growth barriers for Db2 for i databases. Prior to the new release, partitioned tables allowed clients to grow tables way past the 1.7 TB size limit, but non-partitioned indexes over partitioned tables were still stuck with the 1.7 TB max size. With Db2 for i 7.5, index size should no longer be a concern when moving to a partitioned table implementation.

 

Speaking of table partitioning, the IBM i 7.5 release is the latest release to includes a licensing change to the software feature, Db2 Multisystem, which enables the usage of partitioning. Db2 Multisystem is now a no charge feature instead of the chargeable feature. The no charge licensing change also applies to Db2 SMP and the High Availability Journal Performance features: journal caching and journal standby. It’s on my to-do list to my update my recent Db2 Add-Ons entry to highlight that these four features as of June 1, 2022 are no longer chargeable for the IBM i 7.x releases (7.1, 7.2, 7.3, 7.4  and 7.5). Just because Db2 SMP & Db2 Multisystem are simpler to obtain, does not mean that you should rush out to use them. These two features work best when careful planning and preparation are part of the deployment process and our Lab Services team has the expertise to assist in that process.

 

Db2 SMP also has some interesting enhancements in the latest release. First, the optimizer was enhanced to do a more accurate optimization with *OPTIMIZE % setting for the PARALLEL_DEGREE query option. This improvement should result in a more balanced use of system resources when a query is running with this setting. Second, DBEs can also protect their system CPU resources from being overrun by queries using parallel processing with the new PARALLEL_MAX_SYSTEM_CPU query option. Let’s say you set this option to 80 (which is the default) - once your server CPU utilization exceeds 80%, Db2 will automatically start throttling back the number of parallel threads used by Db2 for parallel processing until the server CPU utilization drops below 80%.  As you can see below, the Plan Cache Properties were updated to give DBEs insights into how many SMP queries have been automatically governed by Db2 due to this new query option.  The Total Number of Queries with Runtime Adjusted SMP Degree property is used to track this event – these properties show that no automatic SMP throttling has occurred yet on the system.

Temporary index tracking and analysis is also simpler with Db2 for i 7.5 thanks to some new Plan Cache Properties as well as a new service – MTI_INFO. In the figure below, notice the MTI related properties now have their own properties section and there are new properties to track the usage of non-reusable temporary indexes. 

If these temporary index metrics start to rise, then DBEs will have a much easier time getting the details for each MTI with the new MTI_INFO service.  The following query shows how straightforward it is to generate a list of all the MTIs currently created over the tables in a specific schema. The Table_Schema parameter could be changed to *ALL, if one needed a list of all the MTIs on the system.

SELECT * FROM 

 TABLE(MTI_INFO(Table_Schema => 'TOYSTORE3', Table_Name => '*ALL'))

 

This figure shows just a subset of the details returned by the new service for each MTI.

One last item to highlight is a Db2 usability enhancement in the newest version of the IBM i Access Client Solutions (ACS). Usability of Run SQL Scripts is improved with the addition of file tab support. As you can see in the figure below, this new support allows you to easily jump back and forth between SQL scripts. Providing a big productivity boost when you need to make similar changes to multiple SQL script files. I’ve also found it quite useful when developing and testing a new SQL routine. The file tabs allow me to keep my SQL routine source separate from the SQL that I use to test out the new routine.

Hopefully, these Db2 for i 7.5 highlights have you gotten you excited to try out the new release.  Although there are not any major Db2 enhancements in this release, remember that Aesop once said, Good things come in small packages.  More complete details on the Db2 for i 7.5 release can be found here.