How IBM Improved The Database With IBM i 7.6

April 28, 2025 Alex Woodie

Developers have a host of new database functions to play with in IBM i 7.6 and 7.5 TR6, not the least of which is the new data change table reference functions. In addition to that big new feature, IBM is bringing updates to SELF, enhancements to parallel execution of SQL statements, and 10 new IBM i services, among others.

The Db2 for i database is the beating heart of the IBM i platform, storing, processing, and serving data to all of the IBM i applications. While other databases are available on the platform (MySQL, MariaDB, and Postgres) through the AIX PASE runtime environment, the fact that Db2 for i remains by far the most-used database is a testament to its efficiency, utility, and power. And its very tight integration with the rest of the tooling in the IBM i stack and, of course, to the fact that this is the database that OS/400 and IBM i shops have used for decades.

IBM has increased the QRO hash size in the SQL query engine from 32 bits (INTEGER) to 64 bits (BIGINT). This feature, which is only available in IBM i 7.6, was made to better support larger SQL plan caches, IBM says. IBM has made changes to other SQL services that rely on the QRO. You can read more about that in the IBM Support pages.

IBM added wait time metrics for object and record locks. (Image courtesy IBM)

Waiting around can be such a bore. But with IBM i 7.6, database engineers will appreciate the new “wait” insights provided by Db2 for i. With this release (and only this release), engineers can get access to a host of metrics around wait times, including the average object lock and record wait times and counts, as well as average seize wait times and counts. These wait metrics are now available in Visual Explain, via plan cache snapshot, database monitor, and the live plan cache as well as SQL performance monitors, according to IBM. Read more here.

Accuracy is important. IBM showed it understands this by updating the labels and units it uses in its database tools to describe how Db2 for i is running. For instance, instead of a column labeled “temporary storage,” with IBM i 7.6 the column has been upgraded to read “average temporary storage (MB).” Similarly, “statement open time (ms)” has been upgraded to read “average statement open time (ms).” It’s a small change, to be sure, but it shows IBM’s commitment to accuracy with 7.6.

New IBM i Services

For the better part of the last decade, IBM has been adding a dozen or more IBM i Services to the database with each new release. IBM i Services are SQL-based alternatives to traditional ways of accessing things on the platform, such as IBM i objects, system information, and other components. There are hundreds and hundreds of IBM i Services at this point, residing in either QSYS2 or SYSTOOLS. (FYI, IBM used to call them SQL services, but it changed that back in October 2020.)

IBM is rolling out 10 new IBM i Services with IBM i 7.6, six of which are also supported with IBM i 7.5 TR6. The six new IBM i Services that are common to both releases include:

AUTHORITY_COLLECTION_IFS: A new SQL table function that returns information about the authority check for an object
VERIFY_NAME(): A new SQL table function that checks whether the input name is correctly constructed to be either a system object name or an SQL name
DELETE_OLD_JOURNAL_RECEIVERS(): A new table function that deletes detached journal receivers according to filtering criteria, or optionally returns a preview of the journal receiver without deleting it
IFS_PATH(): A new table function that returns a specified part of the input path string, which is assumed to be an object in the IFS
AUDIT_JOURNAL_PO(): Support for PO (printer output) with the table helper functions for audit journal entries
that provides detailed information for audit journal entries, in this case for PO, or printer output;
AUDIT_JOURNAL_SF(): Support for SF (spooled file) with the table helper functions for audit journal entries

Additionally, IBM is rolling out these four new IBM i Services that are exclusive to IBM i 7.6. They include:

CHANGE_TOTP_KEY(): A new table function that generates a new time-based one-time password (TOTP) key, saves a specified TOTP key, or removes the TOTP key for the user invoking the function. TOTP keys are instrumental for the new multi-factor authentication (MFA) capability IBM introduced with IBM i 7.6
CHECK_TOTP(): A new table function to validate whether a TOTP value is working correctly between the client app that generates the TOTP key and the server that actually validates the TOTP value. It’s similar to the CHKTOTP CL command
KERBEROS_KEYTAB_ENTRIES(): A new table function that returns the entries in a Kerberos key table, similar to the DSPKRBKTE CL command and Qshell keytab command
PROGRAM_RESOLVED_IMPORTS(): A new table function that returns the imports for an ILE program or service program that are resolved by exports from one or more service programs

Enhanced IBM i Services

As previously stated, IBM has added hundreds of IBM i Services to tackle common everyday administrative tasks in the operating system (perhaps the number is over 1,000 now). IBM also uses new releases of the operating systems and OS updates to enhance existing IBM i Services.

To that end, IBM has enhanced twenty existing IBM i Services in IBM i 7.6, including QSYS2 updates to table functions for viewing and working with active job information; views for retrieving security attribute information; a table function for setting alternate subsystem (SBS) routing and a view for retrieving SBS routing for specific users; views and table functions for retrieving block size information in disks; and views for retrieving information about MFA settings for user profiles.

Over in SYSTOOLS, IBM has added a handful of new table functions to streamline use of the IBM i audit journal. Specifically, it has changed or updated the table functions for the following audit journal entry types: AD (auditing change); CP (user profile change); DS (service tools user ID and attribute change); GR (generic record); NA (attribute change); PW (password); SM (systems management change); and VP (network password error).

IBM also updated the view that compares what PTFs are installed on a system against the service levels listed in IBM Preventative Service planning website. Date formatting has been updated in the view that shows when your Power box is due to be powered down. Finally, IBM has added tracking in system limits for a new work management limit: a maximum of 19,004 message keys in a job message queue.

Some of these enhanced IBM i Services are available in both IBM i 7.6 and 7.5 TR6, while others are only available in 7.6. For more information, check out the IBM Support pages for 7.6 and for 7.5 TR6.

Db2 Services and SELF

IBM also bolstered the SQL Error Logging Facility (SELF), which provides a database mechanism for capturing details about specific errors or warnings happening in SQL statements.

With the Spring 2025 Technology Refresh, IBM is giving database engineers the option to end SELF logging for all jobs, which is available in IBM i 7.4 through 7.6. IBM also added a new feature that allows the database engineer to request logging for all errors and warnings.

IBM added a new Db2 for i Service with 7.5 TR6 and 7.6, and updated an existing one. The new QSYS2.SQLSTATE_INFO table function returns information about SQLSTATE values used by IBM i. It enhanced the existing QSYS2.DUMP_PLAN_CACHE() table function with new QRO_HASH filtering options.

This Issue Sponsored By

Table of Contents

Content archive

Recent Posts

Subscribe

Pages

Search