IBM i TR kicks in Db2 Advancements

 With this month featuring the exciting knockout rounds of the 2026 World Cup, it would be easy to miss the announcement of the latest IBM i Technology Refresh (TR) which delivers another round of Db2 enhancements. While the Db2 advancements in this IBM i TR pale in comparison to the Database mode in the IBM Bob Premium Package for i that was highlighted last month, it’s always good to have new tools added to your toolbox. 

IBM i programmers will find a richer ILE RPG SQL precompiler with the ability to define items based on a single field contained within a external file (table or physical file).  The LIKE(*EXT) support enables developers to generate an item definition for a single field  without the overhead of defining a file or externally described data-structure. 

Developers that work with SQL Triggers may find usages for the Trigger pseudo columns. These pseudo columns enable an SQL Trigger to dynamically retrieve the name, library, and member of the Db2 table that caused Db2 to run the trigger program as shown in the following example.

 CREATE TRIGGER trigger1
    AFTER INSERT ON table1
  BEGIN
    DECLARE trig_lib, trig_mbr, trig_file VARCHAR(10);

    SET trig_lib = TRIGGER_FILE_LIBRARY_NAME;
    SET trig_mbr = TRIGGER_FILE_MEMBER_NAME;
    SET trig_file = TRIGGER_FILE_NAME;
    …
    /* rest of trigger logic */
  END ;

The pseudo column support can be useful for audit or security purposes in SQL trigger definitions like this one where the table references (i.e., table1) is unqualified.

The consumption of Db2 data from journal entries is also now easier. While the DISPLAY_JOURNAL service has greatly simplified the retrieval of journal entries, it was challenging to extract Db2 data out of the entry specific data field due to its binary encoding.  The new CREATE_DATA_JOURNAL_READER function now can be used to build a table function which will return your Db2 data in a usable format by performing the necessary conversions of the binary data.

You can think of the CREATE_DATA_JOURNAL_READER function as a one-time setup operation which generates the table function with the necessary data conversions based on the definition of the input table. In this example, a function is being created to read data from the Customers table in the DATALIB library.

VALUES CREATE_DATA_JOURNAL_READER (
                             LIBRARY_NAME   => 'DATALIB',  
                             FILE_NAME      => 'CUSTOMERS',
                             OUTPUT_LIBRARY => 'SOMELIB')

Once the data reader table function has been created, the function can be used repeatedly to extract and return data from journal entries for the Customers table as the following examples demonstrate.

SELECT cusID, cusAddr
  FROM TABLE (SOMELIB.DISPLAY_JOURNAL_DATALIB_CUSTOMERS())
  WHERE cusZIP = '55901';

SELECT cusFName CONCAT ' ' CONCAT cusLName
  FROM TABLE (SOMELIB.DISPLAY_JOURNAL_DATALIB_CUSTOMERS());

I’m hoping that these Db2 for i highlights kick starts your interest to perform a more detailed review of all the enhancements available with the newest technology refreshes for the IBM i 7.5 and 7.6 releases.