Oft-Requested SQL Function Coming In IBM i 7.6

April 8, 2025 Alex Woodie

IBM i engineers and product managers have no shortage of feature requests to wade through, some of which make it into the platform and many of which do not. With the upcoming delivery of IBM i 7.6, IBM will be delivering one of the most often requested features: Support for UPDATE and DELETE statements in the data-change-table-reference clause of a SQL statement.

While the capability to update and delete statements with data-change-table-reference may not sound like a big deal, it actually represents a significant improvement that will simplify database programming tasks for developers, according to Scott Forstie, IBM’s Db2 for i business architect.

“Everybody who is a database programmer knows how to update rows and delete rows, right?” Forstie told IT Jungle in a briefing last week. “What they don’t have before 7.6 is a way to wrapper the update and delete with a query to be able to extract whatever detail or insight they want as to what did this action just do to my database file.”

There are all sorts of practical examples of how this feature could be used in business. For instance, say a group of workers was given a raise. The rows in the database for salary will be updated with the current rate, but unless they create another column for the previous salary – or implement a temporal table, which IBM added back with IBM i 7.3 – the old salary information will be gone and no calculations can be performed on it.

With the support for UPDATE and DELETE statements in the data-change-table-reference clause, a programmer can now write a query that simultaneously updates the row with the new salary level, deletes the old value, and return the amount of the increase, all in one trip to the database.

“This is more pragmatic within my programming logic [compared to temporal tables]. I flowed through a certain line of path of code and I need to do some data manipulation, but I also need to have the oversight or insight as to which rows I’ve been working with,” Forstie said. “So it becomes very simple for SQL programmers to have summary reports or just extraction of insight that helps them in their program logic or in any other way they would use such information.”

Other SQL databases have supported UPDATE and DELETE statements with data-change-table-reference clause. However, up to this point, Db2 for i has only supported INSERT commands with data-change-table-reference, not the additional SQL commands.

“In other versions of DB2, data change table references can also be used with UPDATE and DELETE statements,” IT Jungle contributor Michael Sansoterra, a database administrator at Broadway Systems, wrote back in 2015. It would be “an exciting prospect if these features ever make it into DB2 for i.”

That day will finally arrive when IBM i 7.6 ships on April 18. According to Forstie, support for UPDATE and DELETE statements with data-change-table-reference, along with integrated multi-factor authentication (also added with this release) is one of the “big rocks” on IBM i chief technology officer Steve Will’s back.

“People have been asking for it over and over again, whether they’re LUG companies or from the IBM Ideas portal or the advisory groups,” Forstie said. “Everybody wanted it, because the programmers need help. It simplifies a complex programming task. It’s just a natural win for them.”

There’s another, secret benefit that comes with support for UPDATE and DELETE statements with data-change-table-reference, Forstie said: faster processing through parallelism and the database’s support for symmetric multi-processing (SMP).

“When they get this data-change-table-reference support, they can use it to implement a parallel execution of update and delete,” he said. “So not only do you get the insight from the [capability it delivers], but if you choose to use it, you get a faster overall execution.”

IBM used to charge extra for the Db2 SMP function, known as option 26. But thanks to the IBM i simplification exercise that IBM did with its various IBM i license program products (LPPs) back in 2022, Db2 SMP is now bundled with the operating system.

Like the new MFA function, support for UPDATE and DELETE statements with data-change-table-reference is another feature that will only be delivered with IBM i 7.6.

IBM has made various other database changes with 7.6, including a certain number of new and improved SQL services. One of those changes is a new table dubbed SQLSTATE_INFO that returns information about SQLSTATE values used by Db2 for i and the corresponding SQLCODEs. Database engineers will also appreciate the addition of a QRO_HASH optional filter to the DUMP_PLAN_CACHE procedure, which will make it easier to capture SQL Query Engine (SQE) details for specific SQL statements.

The Db2 for i extension for Code for IBM i is also being updated. While not an IBM product, the open source project is extremely popular, with more than 27,000 unique downloads. The database extension has recently gained a new SQL syntax validation function, as well as integration with AI platforms for natural language processing, according to a Steve Will blog post.

IBM i 7.6 will be generally available on April 18. Will is hosting a webcast on April 10, at 9 a.m. ET to discuss the new release as well as the release of IBM i 7.5 Technology Refresh 6. You can sign up here.

This Issue Sponsored By

Table of Contents

Content archive

Recent Posts

Subscribe

Pages

Search