Database Enhancements Galore In Spring 2024 IBM i TRs
May 13, 2024 Alex Woodie
The IBM i server is a database machine at the end of the day, so it’s not surprising that the latest technology refreshes (TRs) focus heavily on enhancements to Db2 for i. From new aggregate functions for the query optimizer to new and improved SQL-based IBM i services, there’s plenty of database goodness to go around in these releases.
On May 7, IBM announced IBM i 7.5 TR4 and 7.4 TR10. You can read the announcement letters for the two releases here and here. The functionality for the two releases, which will become generally available on June 14, is identical, so there’s no need to differentiate between them. You can also read more about the releases on this IBM support page. (It lists the enhancements for 7.5 TR4, but the TRs are identical, so it also applies to 7.4 TR10.)
Let’s start with the functional enhancements in the database itself. There are two additions to Db2 for i: a new ANY-VALUE aggregate function and a new CREATE ALIAS with *LAST member.
The new ANY-VALUE aggregate function will return an arbitrary value from a set of values in a group, thereby making it easier to write certain types of queries and also enables better index usage.
In the example provided by Scott Forstie, the IBM business architect for Db2 for i, using the ANY-VALUE aggregate function makes it easier to write a query to show the number of customers in each postal code. The developer simply adds the columns for city, state, and postal code to the SELECT COUNT (*) query, without having to tell the optimizer to build an index across city, state, and postal code.
The CREATE ALIAS with *LAST member statement, meanwhile, allows the developer to define an alias on a table, partition of a table, view, or member of a database file on the current or remote server. The database chooses which member to use at the time of use, not at the time of alias creation.
On the IBM i Services front, the new TRs introduce 15 new SQL services, made enhancements to nine more, launched one new dubbed Database Engineer (DBE) Service, and enhanced two other DBE Services.
IBM i admins can work with the new audit journal data mart from within the IBM i Navigator for i interface. (Image courtesy: IBM)
The QSYS2 library gains six new IBM i services, including two related to the new audit journal data mart that IBM is creating to make it easier to store and query security data. Administrators can create the new audit journal data mart with the QSYS2.MANAGE_AUDIT_JOURNAL_DATA_MART procedure, and they can see the data contained in the data mart using the QSYS2.AUDIT_JOURNAL_DATA_MART_INFO view. IBM is tracking 10 columns in its data mart; for more info on them, see this IBM support page.
The IFS can be a little bit wild, now you can start to tame it with the new QSYS2.COMPARE_IFS table function. Admins can compare use the function to compare single IFS objects or a subtree of objects in a specific directory.
IBM is bringing a pair of new table functions to track the record-level status of database files and other objects under commitment control. QSYS2.DB_TRANSACTION_OBJECT_INFO() and QSYS2.DB_TRANSACTION_RECORD_INFO() replicate the functionality that currently exists with the Work with Commitment Definitions (WRKCMTDFN) CL command.
Finally, the new QSYS2.SYSTEM_OBJECT_TYPES table returns a list of external object types found on IBM i, including the IFS and the native library.
Over in SYSTOOLS-land, IBM has provided nine new IBM i services and enhanced a couple of older ones. IBM focused its SYSTOOLS work on the tools that got the most attention from the community, Forstie says.
“We’re seeing it become relevant for things like security management, performance management, systems management,” he says. “On all these big topics, these personas will benefit from ease of use, automation, and getting the data in front of the right person at the right time.”
The new SYSTOOLS-based IBM i Services include:
- Two new table functions for audit journal entry types NA (changes to network attribute) and PS (profile swap);
- A new CHECK_PRODUCT_OPTIONS() table function that reports differences between the correct structure and the actual structure of all installed software products;
- A new ERRNO_INFO() scalar function returns descriptive text that corresponds to an “errno value;”
- A new GETENV scalar function that retrieves the value of an environment variable for the current job, similar to the Work with Environment Var (WRKENVVAR) CL command;
- A new IFS_UNLINK scalar function that deletes an IFS stream file;
- A new PRINTER_FILE_INFO view that returns many attributes of IBM i printer files, similar to the Display File Description (DSPFD) CL command;
- A new UTENV scalar function changes the value of an environment variable for the current job, similar to the Add Environment Variable (ADDENVVAR) or Change Environment Variable (CHGENVVAR) CL commands;
- And finally a new SPECIAL_AUTHORITY_DATA_MART table, a data mart that contains information about special authorities for users. These authorities can come directly from a user profile or can be obtained through membership in a group profile, IBM says.
IBM has enhanced five IBM i services with the latest TRs. Among the new services are enhancements to the Submitted Job Tracker work management feature for IBM i; new features in the SYSDISKSTAT disk-tracking system; enhancements to the SEND_EMAIL and GENERATE_SPREADSHEET scalar function; and Performance improvements for some audit journal helper functions, according to IBM.
Finally, IBM fleshed out its line of DBE Service with QSYS2.SYSMEMBERSTAT, a new view that shows one row for every table partition or table member, including rows for program described files. IBM also enhanced two existing DBE Services, including the QSYS2.DUMP_PLAN_CACHE procedure, and QSYS2.ACTIVE_QUERY_INFO, a table function that returns information about active SQL Query Engine (SQE) queries.
IBM also enhanced the database extensions in Code for i, the IBM i-based plugin for VS Code. We’ll cover those in a separate story.
