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.
