Identifying IBM's libraries

I have always wanted the best way to identify which libraries, on the IBM i partition I was using, belong to IBM. I had come up with doing things like considering all libraries that start with "Q", "SYS", and "#". But that was never a guarantee to separate IBM's from others' libraries.

IBM gave me a way to identify their libraries, using the SQL OBJECT_STATISTICS table function. It allowed me to identify most of the IBM libraries, but not all. I have made some additions to what they told me to make it mostly work.

I need to use three columns from OBJECT_STATISTIC results:

• OBJNAME:  Name of the returned object, which in this case will be a library
• OBJDEFINER:  User profile who created the object
• OBJNAME:  User profile that owns the object

IBM told me that any libraries created by the profiles QLPINSTALL and *IBM would allow me to identify what should be considered IBM libraries. On further investigation on several IBM i partitions I have found what I would also consider to be IBM libraries that were created by the user profile QSYS, or are owned by QSYS.

The statement I came up with is:

RPGPGM.COM-unity grows at TechXchange

At IBM’s TechXchange I had the opportunity to expand the RPGPGM.COM-unity. You can see who joined, and I gave ribbons to, here. I am sure you will recognize some of the faces and names of the people.

If you see me at an IBM i event, feel free to come over and say "Hi". In all likelihood I will have a RPGPGM.COM-unity ribbon on me, and you can join the community. All I ask in return is a photograph of you with it.

If you would like to learn more about the RPGPGM.COM-unity click here.

New parameters added to JOBLOG_INFO makes sorting and selecting so much better

One of what, IMHO, is the best modifications in the latest Technology Refreshes, IBM i 7.6 TR2 and 7.5 TR8, has been the new parameters added to the JOBLOG_INFO table function. These being:

• MESSAGE_ORDER:  The order of the results returned
• MESSAGE_LIMIT:  The number of messages to return in the results
• MESSAGE_TIMESTAMP:  All messages that are equal or greater (newer) than the entered timestamp

I can view all of my job's joblog using the following SQL statement:

November’s presentations

I will be making two presentations this month, November, both of them online.

The first is for the Central Texas IBM i User Group. This month's meeting is on Tuesday November 10 at 6:30 PM (CT). I will be giving two 30-ish minute presentations, that proved very popular at COMMON PowerUp earlier this year:

• Designing your own CL commands
• Using SQL triggers to track file changes on IBM i

As I said the meeting is online, and free. You register from their web page here.

The following week, Tuesday November 17 starting at 2:00 PM (CT), I will be talking to the QUSER group of Minnesota. I will be making two presentations here too:

• RPG Built-in Functions You Should Be Using (50-60 minutes)
• RPG Arrays – Modern Features You Should Be Using (30-ish minutes)

This online meeting is only open to members of QUSER, who can register at their website here.

Fall TRs announced

The Fall Technology Refreshes were announced at IBM’s TechXchange conference yesterday. IBM i TR3 and 7.5 TR 9 are planned to be available November 20, 2026.

What caught my eye in these are:

• SQL
  • The creation of a SELF exclusion list
  • Changes to the QSYS2/HTTP functions to allow for header options for decompression and logging

 

• RPG
  • When defining an array be able to initialize it with a list of values
  • A new Built in Function %INFO that will return the types of information that is currently found in the RPG status data structure
  • A parameter in the Create RPG commands to prevent the program from being changed with the Update Program command, UPDPGM

And many more cool things!

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: