Db2 PTF Group Enhancements Target Web Services, Audit Journal
October 4, 2021 Alex Woodie
It is indisputable that the Db2 for i database is the beating heart of the IBM i platform. There were no earthshattering enhancements to this database in the latest batch of Technology Refreshes for IBM i 7.3 and 7.4 that IBM announced and shipped last month. But there were a few items worth mentioning that shipped in the Db2 PTF Group, including new HTTP commands for calling the database via REST and new SQL-based commands for querying the security audit journal.
The top new Db2 for i feature arguably is the new set of functions for HTTP requests to consume Web services. According to IBM, these new functions will enable SQL programmers to use Representational State Transfer (REST) techniques and protocols directly through SQL, including SQL embedded within RPG and COBOL.
REST, of course, has supplanted the Simple Object Application Protocol (SOAP) as the predominant method for calling and consuming Web services over the Internet. Most REST calls return data encoded in Java Object Notation (JSON), and IBM i’s REST capabilities are no different. REST and its common GET, PUT, POST, and DELETE commands have become the de facto standard for remotely calling APIs and participating in the wide world of Web services and microservices, and IBM i fits right into this API world.
To that end, IBM has made available a series of functions for calling the Db2 for i database over HTTP, including QSYS2.HTTP_GET(), QSYS2.HTTP_POST(), QSYS2.HTTP_PUT(), and so forth. You can get the full list in the Db2 for i entry on the IBM support website.
“These HTTP functions exists in QSYS2 and have lower overhead than the SYSTOOLS HTTP functions,” Big Blue said in the IBM support webpage. “Additional benefits of the QSYS2 HTTP functions are HTTP authentication, proxy support, configurable redirection attempts, and configurable SSL options.”
The new REST techniques are faster because they don’t rely on Java, as the existing REST commands in SYSTOOLS do, said IBM i chief architect Steve Will.
“When you want to consume a Web service today from within SQL, for example, you select from this JSON table,” Will said in a Web presentation sponsored by COMMON last month. “This is bunch of JSON that has been stored in a CLOB [character large object] and you can query it. You can get information from it. That is how you do it before this announcement.”
Developers can still use the old Java method, of course. But he expects they will implement the new QSYS2 approach because it’s faster.
“There’s nothing associated with that Java engine that’s necessary” in the new commands, Will continued. “But you can still get access to the information that’s in that JSON CLOB file data. So [it’s] slimmer, easier, and actually considerably more like it’s done in other platforms and other languages.”
You can find more details about these new HTTP REST functions here.
Also included in the Database Group PTF are 14 new IBM i Services, including 12 in SYSTOOLS that are designed to help users get more useful data from the audit journal system, and two that reside in QSYS2.
According to IBM’s documentation, the new QSYS2-based IBM i Services provide similar functionality that users previously got through CL commands and APIs.
For example, the new QSYS2.COLLECTION_SERVICES_INFO command returns the configuration properties for Collection Services in much the same manner that the Configure Perf Collection (CFGPFRCOL) CL command and the Retrieve Collection Services Attributes (QypsRtvColSrvAttributes) API did. Users would use this command to see where various components, such as PowerHA, security services, or spool services, are storing collections.
Similarly, the new QSYS2.WORKLOAD_GROUP_INFO command returns values for workload groups that are similar to what one would get with the DSPWLCGRP CL command and the Retrieve Workload Groups Information (QLZRTVWC) API, IBM says. That includes the name of the workgroup, any processor limits, and so forth.
The new SYSTOOLS services should help SQL-loving users, developers, and engineers get more info from the audit journal, which is notoriously difficult to work with. “The highlight of these changes,” IBM says in its announcement letter, “is the audit journal function, which provides table functions unique to specific audit journal entry types. Not only do the table functions return the basic audit journal detail, but they also extract the entry-specific detail into easily consumed return columns.”
According to the IBM support website, these 12 new SYSTOOLS services enable users to use SQL to get at various types of audit journal entries, including use of command strings (CD type), create object (CO), user profile changes (CP), delete operation (DO), environmental variable (EV), generic record (GR), action to system value (SV), and several audit journal entry types related to Db2 Mirror operation (MO, M6, M7, M8, and M9).
These IBM i Services can be accessed through Access Client Solutions (ACS) and the new IBM i Navigator. They can also be used by vendors to simplify access to certain data in the IBM i. The new audit functions, in particular, can be used by both users and vendors, Will said.
“We’ve got a bunch of different security vendors who make their living creating tools around this, and then we’ve got clients who want to do more of it themselves,” Will told IT Jungle in a briefing. “We’re trying to make it possible for both sets of customers to more easily get that information if they want to code it themselves. We recognize that just using individual APIs and display objects out to print, et cetera – it’s too cumbersome either way, whether you’re a security vendor or whether a user trying to do outside the security tools. But yeah, we’re trying to make it easier.”