What the Spring 2023 TR Brings Db2 for i

April 26, 2023 Alex Woodie

As the beating heart of the IBM i platform, the Db2 for i database received its share of attending from Rochester for the Spring 2023 Technology Refresh. Among the goodness becoming available next week are enhancements to the database proper as well as a gaggle of new and improved IBM i services.

One database enhancement common to both IBM i 7.5 TR2 and 7.4 TR8 is an improvement to SQL-based Web services using specific data types. The database and its SQL dialect already supported working with character large object (CLOBs) from the SQL HTTP REST functions, and provided commands for building request and response operations with them.

With this release, IBM has added support for binary large objects (BLOBs) to the two supported operating systems. The TRs add various commands for get, post, delete, patch, and put operations on BLOBs in QSYS2 HTTP functions, as well as “verbose versions of these functions,” IBM says. For more info, see this IBM support document.

Another database enhancement found in both OSes is a new set of built-in functions for time- and data-based operations. These functions, including ADD_DAYS, ADD_HOURS, etc. will make it easier for developers to build time-oriented business logic with SQL. See this IBM support doc for more info.

In addition to time, IBM is improving its space functions in the database via enhancements to the Watson Geospatial Analytics, which debuted last year. With these TRs, IBM has added the ST_GEOHASHVALUE scalar function, which gives developers more control over a geohash, or a number that uniquely identifies a particular region. For more info, see this IBM support doc.

IBM i 7.5 TR2 gets an enhancement to the RESTRICT ON DROP function that’s designed to prevent the accidental or purposeful deletion of certain database records like SQL tables and DDS-based physical files, which debuted with IBM i 7.5 last year. With TR2, IBM has extended the capability beyond the database to include protection for procedures and functions. You can find more on this at this IBM support doc.

Another new feature unique to IBM i 7.5 TR2 is the new compression capability that leverages Power10 hardware. IBM bolstered the SQL Query Engine (SQE) with an open source ZLIB algorithm to automatically compress large cached query plans (those larger than 256KB). Early testing shows the performance gains on big, complex queries could be as high as 20 percent, according to IBM. More info can be found here.

That’s it for enhancements to the database itself. Now let’s take a look at the new SQL-based services, which Scott Forstie, IBM’s Db2 for i database architect, and his team have been furiously adding over the past few years to replace the need to work with complex APIs or memorize CL commands.

All told, IBM added 18 new IBM i services to 7.5 TR2 and 7.4 TR8, and enhanced an additional 10 pre-existing IBM i services in 7.4 TR8 and 11 in 7.5 TR2. The new ones include:

* QSYS2.DNS_LOOKUP_IP, which provides a scalar function that returns a hostname for a specified IP address;

* QSYS2.NETWORK_ATTRIBUTE_INFO, which provides a view that returns a single row containing information about the network attributes of the system;

* QSYS2.RDB_ENTRY_INFO, which provides a view that returns information about relational database (RDB) directory entries;

* QSYS2.SAVE_FILE_INFO, which provides a view that returns general information about save files in QSYS2 (it doesn’t work on save files that contain IFS data, however);

* QSYS2.SAVE_FILE_OBJECTS, which provides a view that returns information about the objects in a save file (but not IFS data);

* QSYS2.SAVE_FILE_OBJECTS (), which provides a table function that returns information about the objects in a save file (no IFS support);

* TAPE_CARTRIDGE_INFO, which provides a view that returns information about tape cartridges;

* And QSYS2.SYSTOOLS.DEFECTIVE_PTF_CURRENCY, which provides a view that returns a list of defective PTFs on the system that do not have the corrective PTF applied.

The remainder of the new IBM i services are designed to return data from the audit journal. Specifically, there are nine new table functions in SYSTOOLS designed to handle specific QAUDJRN entry types, including AP, AX, OR, PA, PF, PU, RA, RO, and RZ types. For complete details on these items, see the IBM support doc for audit journal entry services.

IBM also improved a handful of existing IBM i services for 7.5 TR2 and 7.4 TR8, including QSYS2.ASP_INFO, QSYS2.JVM_INFO, QSYS2.JVM_INFO(), QSYS2.PROGRAM_INFO, QSYS2.PTF_INFO, QSYS2.STACK_INFO(), QSYS2.SYSDISKSTAT, QSYS2.SYSDISKSTAT(), QSYS2.SYSTEM_STATUS_INFO, and QSYS2.SYSTEM_STATUS(). The QSYS2.IFS_OBJECT_STATISTICS() table function was enhanced for 7.5 TR2.

There weren’t any new Db2 for i services with these TRs. But IBM did enhance the existing QSYS2.SYSFILES view, which returns information about database files. This is available for both OSes currently under support.

You can read more about all of the enhancements in IBM i 7.5 TR2 at www.ibm.com/support/pages/ibm-i-75-tr2-enhancements, while you can do the same with 7.4 TR8 at www.ibm.com/support/pages/ibm-i-74-tr8-enhancements.

This Issue Sponsored By

Table of Contents

Content archive

Recent Posts

Subscribe

Pages

Search