GENERATE_SPREADSHEET now allows me to give sheet a name

Thanks to the latest Technology Refreshes, IBM i 7.6 TR2 and 7.5 TR8, we can now give our sheets generated by GENERATE_SPREADSHEET real names, rather than "Sheet 1". This only works for Microsoft Excel, XLS and XLSX, and OpenDocument, ODS, spreadsheets.

There is a new parameter for GENERATE_SPREADSHEET, SHEET_NAME, that allows you to give the sheet a name. While there does not appear to be a limit in the number of characters you can use in this parameter, Excel has a limitation of 31 characters for a sheet name. Before you go and start making your sheet name 31 characters you need to read on to understand why your sheet names will need to be less than that.

In my examples I want to generate a list of names in Excel from the PERSON file I have used before. I want to concatenate the last and first names together, and sort the results by name.

Let me jump straight to my first example, generating an Excel spreadsheet in ACS' Run SQL Scripts, RSS.

Award for this website for second year

I was notified on Monday that this website, RPGPGM.COM, for the second year has won an award from Feedspot.

Anuj Agarwal, founder of FeedSpot, informed me of this:

I emailed regarding your blog Rpgpgm.Com which was selected by FeedSpot as one of the Top 10 IBM Blogs on the web (2026 Edition).

https://bloggers.feedspot.com/ibm_blogs/

This year RPGPGM.COM is ranked in eighth place.

Thank you Anuj, and the panel at FeedSpot, for this honor.

ACS 1.1.9.17 available now

Another release of IBM's Access Client Solutions, ACS 1.1.9.17, is available.

This version is to remedy a couple of CVE related to the SFTP client. And to fix an error with the RESTRICT ON DROP on the CREATE object SQL statement. You can learn more about them on IBM's ACS updates page here.

When copying data from IFS file preventing errors due to nulls

I was asked to help a colleague with a problem he was having. He had a CSV, Comma Separated Variable length record, file in the IFS, and was getting errors with a program when it tried to copy the contents of the CSV file to a DDS files. The program had been running for many years without errors, until now. Looking in joblog I could see that the copy was failing due to null values in the CSV file's columns.

How is null shown in a CSV? Normally it is when two commas are next to each other: ",,"

For this post I created a DDS file, you will not be surprised to learn that I called it TESTFILE, and it contains three fields:

01 A R TESTFILER 02 A FIELD1 10A 03 A FIELD2 5P 0 04 A FIELD3 Z

Being proactive about system sizings

There are a couple of SQL views that keep track of objects exceeding the maximum system sizes. I can use these to be proactive, capture information about objects reaching their maximums, before it happens.

In this example I want to show any object whose sizing limit has reached a value of 75% or more.

The Views I could use are:

• SYSLIMITS:  Full version of the View. You need *JOBCTL authority, or QIBM_DB_SQLADM or QIBM_DB_SYSMON function usages. If the Limit Type is "OBJECT" then you will need *EXECUTE to the library and *OBJOPR and *READ authority to the object.
• SYSLIMITS_BASIC:  Requires less authority than SYSLIMITS, and as it has less columns the results are returned faster.

Latest IBM i roadmap

When I find these IBM i Roadmaps I always like to share them with you.

The next time someone tells you that "AS400" or "iSeries" is old, show them this chart. Explain that IBM i is a thoroughly modern operating system, and its creator, IBM, has plans for new releases into the next decade.

I don't know of another operating system vendor who provides similar information.

I copied this from the IBM Modernizing IBM i Applications Redbook.

Copying JSON in CLOB to an IFS file

The germ for this post comes from a comment made on the Better examples for creating and consuming JSON post. The question was:

How can I create an IFS File of the JSON Object from the MYLIB.OUTPUT object?

In that post I used the JSON_OBJECT scalar function to create a JSON array in a DDL table with a CLOB column:

01 CREATE TABLE MYLIB.OUTPUT (OUTPUT CLOB)

I filled the CLOB with a SQL Insert statement with the JSCON_OBJECT scalar function, creating a JSON array from the data in the Person file:

Who remembers the RPG Debugging Template?

Someone asked me about what IBM midrange programming was like in the days before the AS/400. I am old enough to have first worked on the IBM System/36, learning RPGII. One of my most treasured possessions was a "RPG Debugging Template", this folded piece of card had all of RPGII specifications on each fold. This could then be use on a green bar print of a program to determine which column various operations and indicators were in and, therefore, would help me determine what the code would do.

I know it came in a two-card set. I thought I had both. After going through all of the draws in my desk I could only find the one with the specifications I used the most:

What I will be doing in October

This month I do not have any speaking engagements.

But I will be attending IBM's TechXchange, in Atlanta GA, Sunday October 25 - Tuesday October 27. I will have my RPGPGM.COM-unity ribbons with me. If you see me say "Hi", and ask for a ribbon. All I ask in return is a photograph of you with the ribbon.

End of support for IBM i 7.4

Today IBM i 7.4 reaches End Of Standard Support, EOSS.

If you are still using IBM i 7.4 what does that mean?

• You already know that there have been no new features since April 2026.
• Standard PTFs stop, although critical security fixes will be available if you purchase a Service Extension for this release.
• Increase in the cost of you IBM Software Support. Typically, a Service Extension sees...
  • Year 1: increase of 1.5x to 2x of your previous base Software Maintenance rate per core.
  • Later years: increase could reach 3x to 4x.
• End of Service Extension will be September 30, 2029.

For more information see this IBM page here.

If you have IBM Power9 – Power11 you should plan to upgrade your IBM i to 7.5 or 7.6 as soon as you can.

If you have IBM Power8 or older, you need to upgrade your hardware too.

Making a list of users with *ALLOBJ authority is much easier with SQL

The germ for this post comes from a series of conversations I had with a colleague. He had been asked to generate a Microsoft Excel spreadsheet that contained a list of all the current enabled user profiles with All Object, *ALLOBJ, authority. This was something that had been done for years as a series of manual steps, but he decided could do it more efficiently with a CL program.

He would come to me to show me what he had done, and to say he could do it without using any SQL. It is right there are many, many processes that can be done without using SQL, but is that the efficient and timely way to do it?

I am not showing his work, but what I would do if I could not use SQL.

Firstly he would need to use the Display User Profile command, DSPUSRPRF, to make a list of all the user profiles:

Controlling the action of the cursor in a subfile

After reading the post about controlling the cursor's movement on a display file, a friend asked:

How can I make the cursor stay in the same column when I tab through a subfile?

What I described in that post will not work on a subfile as each record's fields have the same names. I would have to use another approach.

Subfile have their own keyword to control cursor movement: Subfile Cursor Progression, SFLCSRPRG. This is a field level keyword that is placed with a field in the subfile. The field must be input or both, of course. Alas, only one field in the subfile can have this keyword. You cannot condition the keyword by using indicators.

Here is my very simple display file with a subfile with the SFLCSRPRG keyword: