Database And Developer Enhancements In IBM i 7.5 And 7.4 TR6

May 9, 2022 Alex Woodie

IBM has made a number of enhancements to the Db2 for i database with the launch of IBM i 7.5, which becomes generally available tomorrow. It also is bringing new features for developers, including changes to Integrated Web Services (IWS), which will improve REST integration.

Most of the new features added to Db2 for i are only available in IBM i 7.5, which will give users a reason to adopt it. However, there are a few enhancements added in IBM i 7.4 Technology Release 6, which doesn’t ship until May 24.

As we previously reported, IBM i 7.5 expands the size of the binary radix index from 1.7 TB to 16 TB, which will help users who are cramming more and more data into their databases.

“This should matter to people who perhaps have a radix index over multiple SQL partitions,” Scott Forstie, IBM’s business architect for Db2 for i, said during a webcast on the new IBM i TRs hosted by COMMON last week. “So it’s a SQL multiple member file and you can have an index over it so your heritage applications don’t know the difference.”

The index limit now is probably so big that nobody will ever reach it, Forstie says. “I challenge anybody to hit this limit,” he says. “We had a hard time doing it ourselves. That’s very large.”

Another big new feature is the introduction of a new Boolean data type, which will provide another SQL-native option for data manipulation. IBM has introduced a slew of new Boolean data operators with this release, including constants, such as true, false, on, off, and unknown; using scalar functions, like Boolean, istrue, isfalse, isnottrue, and isnotfale. using aggregate functions, like any, every, and some; and using predicates, like is true, is false, true, and false.

As previously noted, the Boolean operators can also be used with JSON data stored in the database. The pre-compilers for ILE languages (RPG, COBOL, C, and C++) have been enhanced to support Boolean.

Getting Boolean support is a big deal, says Forstie. “You know, a lot of databases have Boolean, so it was a gap that I wanted to get in and I’m happy to say it’s here and it’s full glory,” he says. “The team really pursue this with rigor. They looked at all the different interfaces, all the different compilers, APIs, how should the data surface, what language constructs with programmers want to use to interact with it, how could you maybe transform existing data that’s trying to be Boolean, which is all about true and false to be Boolean. So this enables that journey into Boolean.”

There’s a bunch of other cool stuff that IBM has done with the database in IBM i 7.5, too. For example, it’s delivered improvements to the SQL Query Engine (SQE) that will improve query costing and thereby drive improvements in query performance. In 7.5, SQE will recognize system-wide high consumption of resources and automatically adjusts query execution to be less aggressive, IBM says.

Your security officer (or the person with security responsibility at your shop) will be happy about the new RESTRICT ON DROP function added to the database. This new clause allows an administrator to restrict the ability of a user to “drop” (or delete) a database element, including SQL tables, database physical files, and database source physical files. The user doesn’t have authority to drop the information, even if they have ALLOBJ authority.

“This was something that some of our clients were asking us to do,” says Alison Butterill, the offering manager for IBM i. “When I’m in a file and I’m working with SQL, who has the ability to drop the file? You can drop records in the file, with rows in the file, [or] you can drop columns in a table. But they don’t want to have people with the wrong authority able to drop an entire file.”

This function makes sure that a user is authorized to drop a table, a column, or a row. “It will go out and check your user profile to make sure you’re actually allowed to do it,” Butterill says.

IBM has added a new “ragged flash” copy capability to Db2 for i that will allow users to make an instant point-in-time copy of their IBM i partition using FlashCopy services. Prior to this feature, an administrator would indicate whether active transactions should be automatically rolled back or left uncommitted upon IPL of the flash copy, IBM says. “By having control over the behavior of the IPL of the flash target, clients gain the ability to control how they utilize the copy node,” the company says.

There is also a new TRY_CAST() built-in scalar function that provides an “easy and reliable technique for using SQL to evaluate the veracity of existing data,” according to IBM. A user on IBM i 7.5 can use the function to “try casting a data element to a specific data type and discover whether the data adheres to the rules of that data type,” the company says.

IBM i 7.5 and 7.4 TR6 users will also benefit from two new QSYS2-based HTTP functions that let developers modify resources, including the HTTP_PATCH() Scalar function and the HTTP_PATCH_VERBOSE() Table function. By using QSYS2, the HTTP patch functions, which are methods for making partial changes to existing resources, take less overhead than traditional SYSTOOLS methods, IBM says.

Even though IBM did not issue a TR for IBM i 7.3, these two functions will also be made available to IBM i 7.3 via a PTF, according to IBM’s Db2 for i support page

On the application development front, IBM has made several enhancements in IBM i. As we previously reported, there are two new RPG opcodes in the compiler, IBM Rational Development Studio. The new opcodes support IBM i 7.3 and higher.

According to the RPG Café, the new ON-EXCP opcode allows the developer to monitor for a specific message ID. “By default, ON-EXCP can monitor unhandled escape message sent to a program or procedure called from the monitor block,” IBM writes in the Café. “If you want to monitor only for messages sent directly to the procedure containing the monitor group, you can specify ON-EXCP(C).”

The new SND-MSG opcode, meanwhile, sends an informational or escape message, with a default type of *INFO. The message can be a string or a built-in function. The informational message can be sent to the current procedure, IBM writes, and an escape message is sent to the caller of the current procedure. The %TARGET function can be used to control where the message is sent.

IBM has also added a new ILE C/C++ built-in functions for atomic memory access. “In a program with multiple threads,” IBM writes, “you can use these functions to atomically and safely modify data in one thread without interference from another thread.” This feature appears unique to IBM i 7.5.

IBM has also made some additions to Integrated Web Services (IWS), which is used for serving and consuming REST-based Web service and API requests.

Previously, the number of supported parameters on an API call was limited to seven, according to IBM. However, that limitation forced developers to change their backend code to leverage data structures to pass more data. So in this release, IBM made changes to the Call Services Program Procedure API (QARUCLSP) to raise the limit to 248 parameters.

IWS services will be able to leverage the increased number of parameters immediately, and there will be change in the behavior of existing IWS services, IBM says. However, calling this API directly “will require special attention to the format required for specifying the increased number of parameters,” IBM says.

The addition of Boolean support with JSON data makes IBM i an ideal platform for serving REST-based microservices, Forstie says.

“I said from day one we’ve got to also do this [Boolean support] to JSON because self-described data, it exists, and REST services are very popular,” the database expert says. “I think doing Web services from 7.5 is the best place because you get the Boolean support.”

IBM has also widened the logging capabilities in IWS. Prior to this release, the logging was limited to error-centric or debug-centric logging, IBM says. For starters, the log data can be written in JSON, which enables consumption in log analysis tools like Splunk and or the Elastic Stack, IBM says.

Also, the logging function can now track more information about usage of each REST API, including who is calling, from where they are calling, and what is being requested, IBM says. It has also added HTTP message logging, including the ability to log the HTTP header as well as the contents of the HTTP message.

As noted, IBM i 7.5 ships tomorrow, while IBM i 7.4 TR6 doesn’t ship for another two weeks. For more information, see the announcement letter for IBM i 7.5 and the announcement letter for IBM i 7.4 TR6.

RELATED STORIES

IBM Merlin Brings New Approach to Developing on IBM i

Announcement Day: IBM Lifts The Veil On IBM i 7.5 And 7.4 TR6