Improvements to GENERATE_SPREADSHEET

The Fall 2025 Technical Refreshes brought some significant improvements to the GENERATE_SPREADSHEET SQL scalar function. These being:

• Execute SQL statement within an IFS files
• Append results from another SQL statement to an existing spreadsheet
• Create a new worksheet, and start the output at a specific row and column
• End the Java program that is started to generate the spreadsheet
• No longer have to use STDOUT to capture information from GENERATE_SPREADSHEET

I am not going to repeat what I have said in previous posts. But, I am going to give an example, below of generating a spreadsheet as a reminder:

IBM Bob formally announced

On Friday February 20 IBM's AI Bob was formally announced with a planned availability date of March 24.

You can read the announcement here.

IBM document: IBM i 7.5 vs 7.6 comparison

On January 30, IBM Support published a page called "IBM i V7R5 versus V7R6 comparison" (shame on IBM Support for not using the proper names of 7.5 and 7.6).

The page is to answer the question: What are the differences between 7.5 and 7.6?

I thought it was something useful to share because, as well as the comparison, there are links to many useful pages of information. You can reach the page here

I hope you find it as interesting as I did.

Find all the services programs used by a program

Most of the time it is my curiosity, but there have been occasions, where I needed to know if a program calls a particular service program. It is easy to discover which service a programs call a service program, but what about the service programs that that those service programs calls? Now there is way to do that.

A new SQL Table function, PROGRAM_RESOLVED_ACTIVATIONS, has been added to IBM i as part of latest Technology Refreshes, IBM i 7.6 TR1 and IBM i 7.5 TR7.

PROGRAM_RESOLVED_ACTIVATIONS has five parameters:

1. PROGRAM_LIBRARY:  Library that contains the object
2. PROGRAM_NAME:  Name of the program or service program
3. OBJECT_TYPE:  *PGM if the object is a program, *SRVPGM if it is a service program
4. DEFERRED_SERVICE_PROGRAMS:  NO do not follow deferred activations, these are service programs that had *DEFER in the BNDSRVPGM parameter of the CRTPGM or CRTSRVPGM command. YES follow the deferred activations, this is the default
5. IGNORE_ERRORS:  NO if an error is found an error happens. YES a warning is returned; this is the default

I have only used the first three.

Using SQL TRANSLATE in place of REGEXP_REPLACE

I encountered something last week that I think may be a "bug" in either RPG or in Db2 for i. I was using the REGEXP_REPLACE scalar function, and I received an error message that made no sense. I ran out of ideas of how to overcome this error using REGEXP_REPLACE, and I went on to use the TRANSLATE scalar function. Before I tell you what the error is I need to explain my situation.

I was working upon a partition which was running IBM i 7.5 with the latest PTFs for Technology Refresh 7, TR7, and the latest PTFs for Database and RPG:

• Database (SQL) = SF99950 Level 11
• RPG = 5770WDS SJ08064

The partition's CCSID is:

01 SELECT SYSTEM_VALUE_NAME,SYSTEM_VALUE 02 FROM QSYS2.SYSTEM_VALUE_INFO 03 WHERE SYSTEM_VALUE_NAME = 'QCCSID' SYSTEM_VALUE_NAME SYSTEM_VALUE ----------------- ------------ QCCSID 273

2026 Fortra survey published

This year's Fortra 2026 IBM i Marketplace Survey results has been published. This is the twelfth year of the survey, and I for one, always find it an interesting read to learn what the other members of the IBM i community are up to.

If you did not participate in this year's survey I would like to strongly encourage you to participate in the next, as it is a good insight as to what is important to people and companies using IBM i.

You can read it here.

You can also watch the discussion about the survey with Steve Will, IBM i CTO and Chief Architect, Tom Huntington, Fortra, Kris Whitney, IBM i Chief Engineer, Tomothy Prickett Morgan, IT Jungle, here.

SEND_MESSAGE can now send message to any message queue

When SEND_MESSAGE SQL procedure was introduced in 2021, as part of IBM i 7.3 TR10 and 7.4 TR4, it would be used to send messages to the System operator message queue. I have used this many times to do so, but I have always wondered how I could do the same to any message queue? Well, in the latest Technology Refreshes, IBM i 7.6 TR1 and 7.5 TR7, this functionality was added.

Two new parameters were added to the procedure to make this possible:

• MESSAGE_QUEUE_LIBRARY:  The library that contains the message queue I want to send the message to
• MESSAGE_QUEUE:  The message queue I want to send the message to

If I omit these parameters the message will be sent to the System operator message queue.

The first example I am going to show is using the procedure as it was intended, sending a message to the System operator message queue. I am going to use the message id CPF9898, which is a general escape message, i.e. I can provide any text I want for the message to use. I can show this using the MESSAGE_FILE_DATA SQL View:

End of support for small Power9 effective tomorrow

Tomorrow, January 31, 2026, is the End of Support, EoS, for the small Power9 servers:

• L922 (9008-22L)
• S922 (9009-22A)
• S914 (9009-41A)
• S924 (9009-42A)
• H922 (9223-22H)
• H924 (9223-42H)

You can read the announcement for this from January 2025 here.

What does end of support mean?

After End of Standard Service, IBM no longer provides preventive service, new updates or fixes, or development of new machine code updates, patches, or fixes (including security fixes).

Extended Support is available for these models, see here.

I do not see any information for the EoS of the larger Power9s, E950 and E980.

Better examples for creating and consuming JSON

Whenever I am asked for an example of how to generate a JSON array or how to consume JSON data I have to point people to different posts, rather than have one that covers both. I have written this post to be that, examples of how to create and how to consume JSON.

I will be using Db2 for i to generate JSON, as I find it simpler to use than doing the equivalent in RPG.

I am going to give four examples in this post to show how to generate a simple JSON array, one with labels and the other without. Then consume the generated JSON into a format that can be easily processed by RPG. All of these examples are going to use embedded SQL in RPG programs.

I will be using my PERSON DDL Table for the input for these examples. What I want to do is to create a JSON containing:

RPG BiFs that now convert CCSID

As the computing world has become more "open" we need to process data that comes in different character sets, not just the EBCDIC CCSID in the IBM i partitions we are working on. Modern RPG has been able to handle all of the character sets, with various Built in Functions, BiF, to convert the character set from one to another.

The RPG PTFs that accompanied new Technology Refreshes, IBM i 7.6 TR1 and IBM i 7.5 TR7, included an improvement to character handling. Some BiFs will now automatically match the character types of the data in the variables used. This post is going to explain what they are, and any errors I found.

The BiFs that do this implicit CCSID conversion are:

New ACS update, 1.1.9.11, fixes vulnerability

On Tuesday, January 14, an announcement was posted on the IBM's ACS Updates page:

IBM i Access Client Solutions is vulnerable to an attacker carrying out an XML External Entity injection via a crafted XFA file inside of a PDF.

Apache Tika is used by the Run SQL Scripts feature to determine the content type of binary column data in a table on the IBM i.

IBM strongly recommends upgrading to 1.1.9.11, and discontinuing use of versions 1.1.9.8 through 1.1.9.10.

The download was not available until Wednesday morning. When I received the following alert when starting my ACS:

Another way to list files defined in Querys

After writing the post about Retrieving the print file name from a Query it struck me that I could use the same methodology to answer a question I have been asked on several occasions: Which Querys are using a particular file?

I cannot answer which Querys are using a file. I can list all the files that Querys are defined to use.

What are the steps I need to perform to get that information:

1. Make a list of all the Querys in a particular library
2. Print their definitions using PRINT_QUERY_DEFINITION
3. Extract the file names from the spool file, and write them to an output file

I am going to show you how I manually executed the above steps. And then I will show a program I created to do the same thing.

First, I need to make a list of all the Querys in a library, MYLIB. I will use the OBJECT_STATISTICS SQL Table function.

01 SELECT OBJLIB,OBJNAME 02 FROM TABLE(QSYS2.OBJECT_STATISTICS('MYLIB','*QRYDFN','*ALL'))

Line 1: I only want the Query library and name in my results.