Database Enhancements Galore In Technology Refresh
April 20, 2020 Alex Woodie
Db2 for i is the beating heart of the IBM i server. There’s no doubt about that. So when IBM delivers the next set of Technology Refreshes (TRs) for IBM i 7.3 and 7.4 next month, you can bet that they’ll be loaded with new and improved features to keep the database humming.
To get up to speed with the database-related enhancements in IBM i 7.4 TR2 and IBM i 7.3 TR8, IT Jungle caught up to Scott Forstie, a senior technical staff member at IBM’s lab in Rochester, Minnesota, and the database architect for Db2 for i.
There’s a lot of database-related stuff in the new TRs. For simplicity’s sake (and because our time is limited), Forstie chose to highlight the three areas that he considered to be most impactful for IBM i users.
First is support for data arrays with the JSON_TABLE table function. With this release, IBM i 7.3 and 7.4 can now consume JSON data feeds that are expressed in the data array format, without the programmer having to do any funky reformatting or otherwise messing around with the data to be able to manipulate the data in the array format using SQL.
“If all the feed providers are publishing arrays only, you have to learn that bit of knowledge and know how to program around it,” Forstie says. “If you follow one of these examples, you’d get a failure [error].”
With the enhancement to the JSON_TABLE function, developers don’t need to do anything special to consume the JSON feed, which could be something like a currency conversion or weather data. This will lower the skill-level for working with JSON data, particularly for folks who are new to JSON. They can follow the examples provided by IBM, and not run off the rails.
“This is an era of creative computing,” Forstie says. “If your first experience with this intellectual challenge is a negative one, you’re getting just failures, you may not pursue it.”
The second big feature noted by Forstie is the new COMPARE_FILE utility, which allows users to see if any two files are similar or dissimilar. According to Forstie, this new utility will be very useful for data engineers.
“If you get back zero rows, then nothing’s different,” he says. “If you get back rows, each of those rows is going to indicate how these files are diffident at the row level. If you’re not interested yet in the data, then you can compare the attributes and know if they’re the same or different.”
The COMPARE_FILE utility works with files in the same database, as well as with files in remote databases. If the customer has purchased the parallel processing add-on for Db2 for i, then the utility will take full advantage of the extra computing horsepower, Forstie says.
Engineers who are managing multiple databases, or perhaps migrating a database from using traditional DDS files to using more modern SQL-created files, will find the utility useful. So will developers who are upgrading or modifying applications and need to track the state of the programs and its outputs.
There are all sorts of uses for the COMPARE_FILE utility, Forstie says. “When talking to some of our stakeholders, I was surprised how excited they were about this,” he says.
Forstie saved the best for last. His final category of Db2 for i improvement are all of the additions to IBM i Services, the group of SQL commands that are replacing traditional APIs and CL programs (or at least augmenting them).
IBM i 7.4 TR2 and 7.3 TR8 sport no fewer than 28 new or enhanced IBM i Services, according to IBM. Those are in addition to the existing IBM i services. “Our team really went above and beyond,” Forstie says. “There’s something for everybody here. There’s probably more than a couple of things for everybody, depending upon their role.”
The latest batch of IBM i Services trend a bit toward automating work and automating systems, the Db2 for i architect says. For example, the new IFS_OBJECT_PRIVILEGES user defined table function (UDTF) tells the administrator what the object privileges are for a given IFS object. This can help them gain a better understanding of how their IFS system is set up from a security point of view.
“The IFS has traditionally been not only very popular and widely used, but difficult to manage, and one part of that is security management,” Forstie says. “Is your security implementation correct? Does it match your strategy? Now you can deploy some very simple but under the covers very powerful SQL services to know where’s your business at today in this topic.”
Another new UDTF that administrators can explore is called SYSTEM_STATUS_INFO. This function will give the admin more detail about the IBM i partition, which will help her manage the system. There’s also a new DELETE_OLD_SPOOLED_FILES procedure, which will “turn the laborious topic of spooled file space management into an easy and automatable solution,” it says in the IBM announcement letters for 7.4 TR2 and 7.3 TR8.
There’s a bunch of timesaving utilities like that to be found in IBM i Services. Forstie didn’t know off the top of his head how many IBM i services there are at this point, but suffice to say, there are plenty of them. And from the sound of it, there will be many more added in the months to come.
“I tell you, these IBM i services are the key to open the door to extreme automation,” Forstie says. “They’re so easy to use. You have access to so much data. You’ve got the query engine as your programming buddy. You can do a lot of things you never did before.”
For more information on the database enhancements with 7.4 TR2 and 7.3 TR8, check out the Db2 for i – Technology Updates landing page on the IBM support website, which is located at www.ibm.com/support/pages/node/1116645/. For more information on the updates to IBM i Services, go to www.ibm.com/support/pages/node/1119123.
