Db2 And SQL Services Get Upgrades With TRs
October 12, 2020 Alex Woodie
Among the biggest enhancements that IBM is bringing to IBM i with the latest batch of Technology Refreshes are new capabilities added to the integrated Db2 database and, specifically, the slew of SQL-based services that retrieve all types of data from the platform for IBM i professionals.
As we previously told you, IBM has decided to rename SQL Services, which were the collection of pre-defined SQL queries that recreate traditional IBM i commands and sometimes bring something entirely new. The growing gaggle of services (or perhaps it’s a herd?) are now being officially referred to as IBM i services, which probably better describes their general nature and the wide number of general system things that can be accomplished with them. (After all, not everybody gets enthused about interacting with SQL, but we all love IBM i.)
With IBM i 7.3 TR9 and 7.4 TR3, IBM has introduced 29 new or improved IBM i Services, bringing a host of new functionality to the fingertips of IBM i programmers, administrators, and database engineers everywhere. You can read about all 16 new IBM i services, 10 enhanced IBM i services – as well as the two new Db2 for i service and the one enhanced Db2 for i service in 7.4 TR3 at this IBM Support webpage (or this page for IBM i 7.3 TR9).
We’re not going to write about all the services here. But in terms of the crème de la crème, we checked in with Scott Forstie, IBM’s Db2 for i architect extraordinaire, who gave a great rundown of the top new SQL and database enhancements in last week’s COMMON announcement webcast.
At the top of the list are the new QSYS2.IFS_READ and QSYS2.IFS_WRITE functions and procedures, which may not jump off the page as important but perhaps are laying the groundwork for amazing things to come the future (and giving do-it-yourselfers more tools for, well, doing SQL things themselves).
Ostensibly, this function and this procedure were added to give SQL programmers the ability to construct or consume the contents of an IFS stream file. Sounds simple enough, right? IFS_READ is a table function that can be used to read an IFS file by path name, and return the results as a character, binary, or UTF-8 data, while IFS_WRITE is a procedure for writing data to an IFS file as character, binary, or UTF-8 data.
“Some of the artifacts of your business are in the database,” Forstie says. “Other artifacts of your business are in IFS, whether logs or receipts or whatever your storing out there – you can turn SQL loose on them.”
Forstie says this function will allow developers to “dream bigger” and do all sorts of things to bring the power of SQL to the data they have squirreled away into the IFS. In fact, you can use the IFS_READ service just like a search engine, if you like.
“Now that you can read things, it seems like a very natural extension to say, given my own little search engine, what would a search engine be constructed to do? Well whatever you want it to do,” Forstie says. “This is not exactly a Lucene search engine, but it’s whatever you want it to be with the SQL.”
The possibilities with IFS_WRITE are also compelling. Considering the fact that the procedure supports binary, character, and UTF-8 data, developers will be able to incorporate the ability to write to the IFS using whatever language they want, Forstie says. And with embedded SQL support in RPG and COBOL, developers working in those languages will also be able to take advantage of this new capability.
“It doesn’t have to be limited to just character data,” Forstie says. “So this binary data, of course, can be whatever you want it to be. And if there are numeric types include here, you could use an INTERPRET built-in function to extract them and treat them like read data types within SQL. This is our back and forth with IFS.”
Many of the enhancements in these TRs come from the community, through IBM’s request for enhancement (RFE) program. Apparently, there has been considerable demand for writing and reading to and from the IFS with SQL for some time.
“This comes up all the time. People are asking, ‘Why doesn’t the database give us a way to do this?’ So now we have the support,” Forstie says. “There are so many new things that are possible. It’s going to take some time for the community to absorb them, I think.”
Forstie and his partner in crime, Tim Rowe, who’s the business architect in charge of application development and systems management tools on IBM i, demonstrated another new IBM i service during their COMMON webcast called QSYS2.DATA_QUEUE_ENTRIES. This table function is used return one or more messages from the specified data queue.
“DATA_QUEUE_Entries allows you to probe into any data queue and see what’s there,” Forstie says. “So just like with the IFS, you can treat it like character data, UTF-8 data…or binary data.”
Developers who find themselves frequently using the Display Database Relations (DSPDBR) command on a greenscreen console will be happy to hear that there is now a SQL equivalent, called SYTOOLS.RELATED_OBJECTS that can be used to return data in a GUI, namely Access Client Solutions, which is the main way to consume IBM i (and Db2 for i) services. This Db2 for i Service (it’s not an IBM i service – they’re different) is a table function that displays a list of all objects that depend on the specified database file, either directly or indirectly.
“It’s far better than DSPDBR will ever be,” Forstie says. “Why? Because it not only returns you the same information as DSPDBR, but it goes further. It finds other dependent objects. Would you ever find procedures and functions from DSPDBR? No. Triggers? You can get it all.”
This can bring about a whole new level of program understanding, or “impact analysis at your fingertips,” Forstie says. “So database engineers, incorporate this one to understand your network of dependencies.”
Database engineers may also be interested in the QSYS2.DB_TRANSACTION_INFO, which is a view that returns one row for every job that has an uncommitted database action. No other service will show you that there are pending row or object changes, Forstie says, which makes it a handy tool to ensure that the database is all caught up before cloning it or using a FlashCopy command.
Administrators may be interested in the new QSYS2.OPEN_FILES() table function, which returns a list of files that are open in all threads for a job. This new IBM i service is functionally the equivalent of the Display Job (DSPJOB) CL command, but it’s powered by SQL.
One of the enhanced IBM i services is the QSYS2.SERVER_SHARE_INFO view, which returns information about IBM i NetServer shares. This view displays information that’s similar to List Server Information (QZLSLSTI) and Open List of Server Information (QZLSOLST) APIs, and is useful for determining if security configuration for networked systems is up to speed, Forstie says.
“I know those things are really hard to keep in spec. With SQL, maybe you add a query like this to a dashboard that you use,” he says. “This is not the sort of thing that you’d want to execute every two seconds. Your configurations are probably not changing constantly. But maybe once a day, look at this information.”
On a related note, the new QSYS2.EXIT_POINT_INFO and QSYS2.EXIT_PROGRAM_INFO views return information about exit points and exit programs. While these programs won’t simplify the process of implementing an exit program, they will make it easier for others to discover existing programs, Forstie says.
The updated QSYS2.DISPLAY_JOURNAL() table function, meanwhile, returns information about journal entries. The function returns information similar to what is returned by the Display Journal (DSPJRN) CL command and the Retrieve Journal Entries (QjoRetrieveJournalEntries) API. But best of all, it now provides full coverage for Syslog.
“With Syslog support . . . we now have 100 percent coverage of audit journal entry types, and user-created audit journal entries as well,” Forstie says. “What this means is we could ask for Syslog format detail on your IBM i and plug it into your SIEM [security information and event management system] and you’re going to have all this ignorant flowing to your SIEM.”
There were a few enhancements to the database itself that don’t fall into the category of an IBM i (or Db2 for i) service.
For instance, IBM bolstered the DROP statement to support the IF EXISTS clause, which it says will streamline Database Definition Language (DDL) processing. It also added the SELECTIVITY clause to SQL query predicates, which will allow database performance experts to better influence the SQL Query Engine plan choice, the company says. It also bolstered the SQL precompiler for RPG, COBOL, C, and ++ to support the WHENEVER SQL statement.