TRs Deliver Database and SQL Updates for Every IBM i User
November 2, 2022 Alex Woodie
When it comes to SQL services and database updates, there’s something for just about every IBM i user in 7.5 TR1 and 7.4 TR7. With dozens of new and enhanced database functions and SQL services, these TRs in fact may be the biggest releases ever, according to Scott Forstie, the Db2 for i architect at IBM.
During a recent interview with IT Jungle about the new Watson geospatial capabilities that his team delivered in the database (which you can read about here), Forstie pondered the journey he and his team have been on, and how it’s culminated to this point with IBM i 7.5 TR1 and 7.6 TR7, the PTFs for which will ship next month.
“When I reflected on the work of myself and my team have done for this upcoming technology refresh cycle-and there wasn’t a lot of time to reflect because we were really busy getting all the documentation lined up for announce day, I thought that wow, this could be viewed as overall the largest delivery in many different ways and from the database in a TR ever,” Forstie said.
Just in terms of sheer numbers, the current TR cycle brings a sizable batch of new SQL services to the platform. By Forstie’s count, there are 30 new SQL services and more than 15 enhanced services.
“There’s something everybody,” Forstie says, “and there’s meaningful things — things for the programmers, for the administrators, and security minded folks. And database engineers of course — they have some enhancements that are a little quieter, but they’re just as meaningful to them.”
There are core database enhancements, as well as new IBM i services. Let’s cover the core database enhancements first.
One of the new core database enhancements is new SQL Error Logging Facility (SELF) function, available for both 7.5 and 7.4. SELF provides a database mechanism that can be used to capture details about what specific errors or warnings are happening in SQL statements.
According to the IBM documentation, SELF is easy to run, safe to use in production, and provides enough contextual detail (dumped to the QSYS2.SQL_ERROR_LOG) to pinpoint the problem. It’s an alternative to using traditional SQL Performance Monitors, IBM says, and can be used for specific jobs or in a system-wide way.
The database engineer in you may also appreciate another new database function in 7.4 and 7.5 that makes it easier to make manual changes to the database. Customers were having trouble tracking changes made to data (such as for staging data model changes) when generated columns are used. According to the IBM documentation, with the new QSYS2.REPLICATION_OVERRIDE function, users can now move rows in the database using SQL, without requiring Db2 for i to produce new values for SQL-generated columns.
Engineers will appreciate the greater server headroom created by the new option in the query optimizer to manually set when parallel processing is disabled. By default, it turns off when CPU usage hits 80 percent, which can help to ensure other jobs are not impacted by long running OLAP queries, for example. But with the new PARALLEL_MAX_SYSTEM_CPU QAQQINI option in 7.5, users can set the bar higher or lower.
Similarly, engineers also get new controls for setting the minimum amount of clock time required for a job before the symmetric multiprocessing (SMP) kicks in. By default, it’s set at 60 seconds, which means the SQL Query Engine (SQE) won’t invoke SMP for jobs that are expected to take less than that. With the new PARALLEL_MIN_TIME QAQQINI (available in 7.5 and 7.4), engineers can tweak that higher or lower to really dial in the performance.
The Db2 for i creators will also be excited about the new REMOTE TABLE support that they have added to SQL on IBM i. This feature makes it easier for a user to query a User Defined Table Function (UDTF) running on a remote IBM i server.
Developers working with Web and mobile data stored as JSON documents will appreciate the new JSON_UPDATE built-in scalar function added to 7.4 and 7.5. With this scalar function, you can update or remove a value from the JSON document given the right key.
There’s also a new FIRST-DAY built-in scalar function that can be used to derive the first day of the month in a date or timestamp format. Time afficionados will also appreciate the new TIMESTAMPDIFF_BIG built-in scalar function, which is able to handle any granularity and scale of timestamp comparison, according to the documentation.
Forstie and his database development team have been working hard create over the past few years to create a variety of SQL services (also called IBM i services) that tackle a wide range of tasks on the IBM i. While the services are written in SQL and run in the database, they’re not all focused on the database itself, and in fact touch many aspects of systems management, including performance, analytics, application development, and of course security.
Security-wise, IBM has brought a new IBM i service for locking NVMe drives. All NVMe drives are self-encrypting, but the passwords are not encrypted. According to IBM’s documentation, it has introduced a new service that uses the Trusted Computer Group (TCG) Opal Security Subsystem Class (SSC) specification to enable NVMe drives to be locked when power is lost or a PCIe “cold reset” occurs, which provides more protection against data loss.
For starters, there is the new COMMAND_INFO service, which returns information about all CL commands on the system. This service gives the same information obtained via the Display Command (DSPCMD) CL command and the Retrieve Command Information (QCDRCMDI) API, IBM says.
Forstie and his team have also brought forth HARDWARE_RESOURCE_INFO, which is a view and table function for QSYS.2 that returns information about configured hardware resources. This IBM i service replicates data previously delivered through other means, including the STRSST Hardware Service Manager interface, the Retrieve Hardware Resource List (QGYRHRL, QgyRtvHdwRscList) API, and the Retrieve Hardware Resource Information (QGYRHRI, QgyRtvHdwRscInfo) API.
IBM has introduced two new services that allow the user to view and change details for the ObjectConnect over IP server. You can read more about CHANGE_OBJECTCONNECT and OBJECTCONNECT_INFO services here.
Tracking jobs submitted to the Job Queue List (JQL) is about to get easier thanks to the new Submitted Job Tracker services. According to IBM’s documentation, once users add a job queue to the job queue tracker list, any jobs subsequently submitted to that job queue are tracked in a job tracking file. Users can use the information in the job tracking file to assist with maintenance, to recover from a node failure, or balance workload, IBM says. There are several related services in addition to the ADD_TRACKED_JOB_QUEUE service.
Administrators get more fine-grained control over the paths their IBM i servers use to access external disks thanks to the CHANGE_DISK_PATHS procedure added to 7.4 and 7.5. Only paths matching the parameters provided will be disabled, and only for some external SCSI disks, IBM says.
Security officers appreciate the remainder of the new services, including one that sends a message to the QSYSOPR message queue when a high level of consumption of an important system limit is detected on the server. Finally, IBM has added a number of new services aimed at pulling data out the audit journal (a frequent target of IBM i services). With this release, IBM adds support for audit journal entry types AD, DS, IM, PG, SK, SM, ZC, and ZR.
IBM enhanced a number of existing IBM i services, all of them in the QSYS2. It also enhanced a pair of Db2 for i services. You can read about these at this page for IBM i 7.5 TR1 and this page for IBM i 7.4 TR7.
Many of these enhancements were requested by IBM i users through the new IBM Ideas website, which replaced the request for enhancement (RFE) process earlier this year. But not all of them.
“Maybe more importantly than that are number of ideas satisfied — those are the requests for enhancements — there were there 20,” Forstie says. “And then there’s innovation as well, things that people maybe didn’t expect to come from the database.”
RELATED STORIES
Inside IBM i’s New Geospatial Functions For Db2
