TRs provide 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 almost every IBM i user in 7.5 TR1 and 7.4 TR7. With dozens of new and improved database functions and SQL services, these TRs might actually be the biggest releases ever, according to Scott Forstie, Db2 for i architect at IBM.

In a recent interview with computer jungle on the new Watson geospatial capabilities his team has delivered in the database (which you can read here), Forstie reflected on the journey he and his team have traveled and how he got to this point with IBM i 7.5 TR1 and 7.6 TR7, the PTFs for which will be shipped next month.

“When I reflected on the work that my team and I have been doing for this next tech refresh cycle – and there wasn’t much time to reflect because we were really busy preparing all the documentation for announcement day, I thought wow, this could be considered the most important delivery in many ways and from a TR’s database,” Forstie said.

Just in terms of the numbers, the current TR cycle brings a significant batch of new SQL services to the platform. According to Forstie’s count, there are 30 new SQL services and over 15 improved services.

“There’s something for everyone,” Forstie says, “and there are meaningful things — things for programmers, for administrators, and for people who care about security. And database engineers of course — they have some improvements that are a bit quieter, but just as meaningful to them.

There are core database enhancements, as well as new IBM i services. Let’s take a look at the core database improvements first.

One of the new core database enhancements is the new SQL Error Logging Facility (SELF), available for versions 7.5 and 7.4. SELF provides a database mechanism that can be used to capture details about specific errors or warnings that occur in SQL statements.

According to IBM documentation, SELF is easy to run, safe to use in production, and provides enough contextual details (logged into QSYS2.SQL_ERROR_LOG) to identify the problem. It’s an alternative to using traditional SQL performance monitors, according to IBM, and can be used for specific tasks or system-wide.

The database engineer in you may also appreciate another new database feature in versions 7.4 and 7.5 that makes manual database changes easier. Customers were having difficulty tracking changes to data (for example, staging data model changes) when the generated columns were used. According to IBM documentation, with the new QSYS2.REPLICATION_OVERRIDE function, users can now move rows around the database using SQL, without needing Db2 for i to produce new values ​​for SQL-generated columns.

Engineers will appreciate the greater server headroom created by the new query optimizer option to be set manually when parallel processing is disabled. By default, it turns off when CPU usage hits 80%, which can help ensure that other tasks aren’t affected by long-running OLAP queries, for example. But with the new PARALLEL_MAX_SYSTEM_CPU QAQQINI option in version 7.5, users can set the bar higher or lower.

Similarly, engineers also benefit from new controls for setting the minimum clock time required for a job before symmetric multiprocessing (SMP) starts. By default, it is set to 60 seconds, which means the SQL Query Engine (SQE) will not work. invoke SMP for tasks that should take less time than that. With the new PARALLEL_MIN_TIME QAQQINI (available in 7.5 and 7.4), engineers can increase or decrease this parameter to really improve performance.

The creators of Db2 for i will also be excited about the new REMOTE TABLE support they’ve 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 built-in JSON_UPDATE scalar function added in versions 7.4 and 7.5. With this scalar function, you can update or delete a value from the JSON document with the correct key.

There is also a new built-in scalar function FIRST-DAY which can be used to derive the first day of the month in a date or timestamp format. Time buffs will also appreciate the new built-in scalar function TIMESTAMPDIFF_BIG, which is able to handle any timestamp comparison granularity and scale, according to the documentation.

Forstie and his database development team have worked hard over the past few years to create a variety of SQL services (also known as IBM i services) that tackle a wide range of tasks on the IBM i. Although the services are written in SQL and run in the database, not all of them are focused on the database itself and actually touch on many aspects of systems management including performance, analytics , application development and of course security.

From a security perspective, IBM has brought a new IBM i service to lock down NVMe drives. All NVMe drives are self-encrypting, but passwords are not encrypted. According to IBM’s documentation, it has introduced a new service that uses the Opal Security Subsystem Class (SSC) specification from the Trusted Computer Group (TCG) to allow NVMe drives to be locked in the event of a power outage or “cold reset”. » PCIe, which provides more protection against data loss.

For starters, there’s the new COMMAND_INFO service, which returns information about all CL commands in the system. This service gives the same information obtained through the CL Display Command (DSPCMD) and the Retrieve Command Information API (QCDRCMDI), says IBM.

Forstie and his team also came up with 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 provided by other means, including the STRSST Hardware Service Manager interface, the Retrieve Hardware Resource List API (QGYRHRL, QgyRtvHdwRscList), and the Retrieve Hardware Resource Information API (QGYRHRI, QgyRtvHdwRscInfo).

IBM has introduced two new services that allow the user to view and modify ObjectConnect over IP server details. You can read more about the CHANGE_OBJECTCONNECT and OBJECTCONNECT_INFO services here.

Tracking jobs submitted to the Job Queue List (JQL) is about to get easier with new submitted job tracking services. According to IBM documentation, once users add a job queue to the job queue tracking list, all jobs subsequently submitted to that queue are tracked in a file monitoring of works. Users can use the information in the job tracking file to help with maintenance, to recover from a node failure, or to balance the workload, IBM says. There are several related services in addition to the ADD_TRACKED_JOB_QUEUE service.

Administrators get more granular control over the paths their IBM i servers use to access external disks with the CHANGE_DISK_PATHS procedure added in versions 7.4 and 7.5. Only paths matching the provided parameters will be disabled, and only for certain external SCSI drives, IBM says.

Security managers appreciate the rest 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 extracting data from the audit log (a frequent target of IBM i services). With this release, IBM adds support for AD, DS, IM, PG, SK, SM, ZC, and ZR audit log entry types.

IBM has enhanced a number of existing IBM i services, all in QSYS2. It also enhanced a pair of Db2 for i services. You can learn more on 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.

“Perhaps more importantly than that, the number of satisfied ideas – those are the enhancement requests – there were 20,” Forstie says. “And then there’s also innovation, things that maybe people didn’t expect to come from the database.”


Inside IBM i’s New Geospatial Features for Db2

IBM unveils fall 2022 technical updates for IBM i

Moving the IBM i RFE site to a new location