Db2 for i 7.6 Highlights

 IBM i 7.6 has now been announced which means a new set of features and functions for your favorite relational database management system – Db2 for i. Similar to the Db2 for i 7.5 release, Db2 for i includes a wide assortment of functional and performance enhancements to help developers and database engineers, but no major additions. 

Probably the most notable feature in the 7.6 release is the enhanced data change table reference support. For several releases, Db2 for i has only supported the data table change reference for Insert statement. This support has been most widely used to simplify retrieving that value that Db2 generates for an identity column like orderID as demonstrated in the following example.

SELECT orderID FROM FINAL TABLE (
     INSERT INTO orders(orderDate,orderQty,orderItem) 
            VALUES('11/03/2024',50,'JM12'))

 

Starting with 7.6, developers can also use data change reference table support with Delete and Update statements. This new support enables developers to retrieve values from rows that are being updated and deleted.  In this example, orders older than 4 years old are being removed from the Orders table.  The OLD TABLE intermediate result table represents the table rows before the delete. This SQL request returns the range of order date values deleted by the statement.

SELECT MIN(orderDate), MAX(orderDate) INTO :firstDate, :lastDate FROM OLD TABLE (
   DELETE FROM orders WHERE orderDate < (CURRENT DATE – 4 YEARS) )

 

What’s even better is that you can improve the performance of Delete and Update statements using this syntax with parallel processing for the first time! Db2 for i has supported parallel processing for a very long time with the Db2 Symmetric Multiprocessing (SMP) feature, but delete and update operations could only utilize a single thread until this new support in the IBM i 7.6 release. Parallel processing is definitely a great way to speed up long-running Delete and Update statements.

 

On the performance front, the SQL Query Engine (SQE) includes a couple of new features to boost query performance in the newest release.  The first is a new method for using indexes with composite keys.  This new data access method should be helpful for queries with a complex set of search conditions featuring inequalities (>, >=, etc.).

 

SQE in the IBM i 7.6 release also includes enhancements to its Adaptive Query Processing (AQP) support. AQP is one of the self-learning query optimization methods used by SQE during the execution of longer running queries. If AQP detects that a query’s progress is too slow, then it will attempt to build a better plan based on its current runtime statistics. In prior releases, AQP was only applied to queries with joins – the support to also assisting with tuning queries performing grouping.

 

The SQL performance analysis tools supporting SQE were also enhanced. One improvement that may seem minor but should greatly improve the usability of the tools is consistent labeling in the graphical tools provided by the IBM i Access Client Solutions (ACS) SQL Performance Center. For example, some metrics were reported in seconds and others in milliseconds in the past. This inconsistency made it difficult to understand and compare the feedback returned by SQE and Db2 for i. SQE was also enhanced to collect additional metrics while a query is running. Now, you will be able to see if a query was slowed by waiting for a lock and if so, how much time was spent waiting for a lock.

 

At the end of 2024, I highlighted some significant advancements to the ACS SQL Performance Center. One of those enhancements was providing access to index advisor. With the latest ACS, the SQL Performance Center Index Advisor interface was enhanced to provide access to condensed index advice and filtering capabilities.  The following graphic shows the new filter capabilities that can be applied to enable more efficient usage of the index advisor output.

The security of your Db2 for i databases is also stronger in the IBM i 7.6 release which features several security advancements such as the integrated multi-factor authentication.

This wraps up my highlights of the latest Db2 for i release, make sure you make time to review the complete list of Db2 7.6 enhancements. I hope this summary has whet your appetite for the IBM i 7.6 release.