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:

Chopping up a file that is too big

I know the title sound intriguing, and I thought the subject was worthy of a post. A friend approached me wanting to know how could he "chop up" files that contain hundreds of millions of records into smaller files, that he would transfer to another platform. He knew he could do it manually, but he wanted a program.

Something like this is very simple in CL. In no time I had a program for him that would take a file, copy one million records at a time to a work file, and copy the work file's contents to a CSV in the IFS.

Unfortunately I do not have file containing hundred of millions of records, so I am going to use my Person file. This file has only 41 records, and I am going to show how I can "chop" it up into files that contain a maximum of ten records. The principal is the same as copying the files with hundred of millions of records.

The source for the program is not big, just 36 lines. I am going to start with the first 17 lines:

Another new release of ACS, 1.1.9.16

Addendum

I noticed today, September 20, that the familiar "Update available" pop-up now displays that release 1.1.9.16 is the new release.

---

This is the third new release of IBM's Access Client Solutions, ACS, in the past two months. While my ACS does not alert for a new release (Help > Check for Updates), version 1.1.9.16 is mentioned on IBM's ACS webpage.

This version is to remedy the following, that has been an issue for all previous versions of ACS:

• STRPCCMD command sent from a compromised IBM i
• 5250 emulator macro that contains a malicious 'RunProgram' action
• 5250 multiple sessions file (.bch, .bchx) that contains a malicious 'Run' action

From this version each of the above actions will require user confirmation to run. A confirmation will be displayed, with "Yes" and "No" options. For the 5250 emulator macro 'RunProgram' action an additional "Cancel" option is offered to end the execution of the entire macro.

Separating parts of qualified job name with SQL

In a previous post I explained how I could extract the parts of the qualified job name using the LOCATE_IN_STRING scalar function. With the latest Technology Refreshes, IBM i 7.6 TR2 and 7.5 TR8, a number of Db2 for i scalar functions and a table function were added to make this easier. These are:

• JOB_NAME scalar function:  Returns the job name
• JOB_NUMBER scalar function:  Returns the job number
• JOB_USER scalar function:  Returns the job user
• JOB_NAME_DETAILS table function:  Returns all of the above

All of the above are found in the library SYSTOOLS.

Validating CL commands with SQL

There has been an API to validate CL commands for as long as I can remember, but is not simple to use. Fortunately, as part of the latest Technology Refreshes, IBM i 7.6 TR2 and 7.5 TR8, a Db2 for i scalar function was introduced that performs the same function.

CHECK_COMMAND_SYNTAX requires one parameter, the command string to be validated. The string can be up to 32,000 characters, which should cover most command strings I use. It returns a Boolean value:

• True = the command string is valid
• False = the command string is invalid

Here are a couple of examples I made and ran in ACS's Run SQL Scripts, RSS:

Creating and using a data journal reader

Every so often IBM delivers something for the IBM i that makes me say "Wow!" Data journal readers are one of these cases. It has changed the way I am thinking of retrieving data from journals.

The CREATE_DATA_JOURNAL_READER SQL scalar function was introduced as part of IBM i 7.6 TR2 and 7.5 TR8, and is very simple to set up. It creates a SQL table function that allows me to see the entries for one of the files whose data is within a journal.

Before I can show examples of this, I need a file and it needs to be journaled.

I created a DDS file, TESTFILE, in my library, MYLIB. It contains nine fields I named FIELD1 to FIELD9. The layout of the file is immaterial to showing how to create and use a data journal reader.

Once the file was created, I needed it to be journaled. Which I did with the following commands:

ACS 1.1.9.15 is replaced

 

The original contents of this page have become obsolete, go to this page for up-to-date information.