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.

A new way to list columns for SQL insert

The latest Technology Refreshes introduced a new way to give the columns I am inserting with an SQL Insert statement into a Table or file.

This is the DDL Table I will be inserting into:

01 CREATE TABLE RPGPGM1.TEST_TABLE 02 (IDENTITY BIGINT GENERATED ALWAYS AS IDENTITY NOT NULL, 03 LAST_NAME FOR COLUMN "LNAME" VARCHAR(30), 04 FIRST_NAME FOR COLUMN "FNAME" VARCHAR(20), 05 ADDRESS_CITY FOR COLUMN "CITY" VARCHAR(20), 06 ADDRESS_STATE FOR COLUMN "STATE" CHAR(2), 07 ADDRESS_COUNTRY FOR COLUMN "COUNTRY" CHAR(3)) ;

Happy New Year 2026 to you all

New Years is one of my favorite times of year, as we are filled with the excitement for what 2026 will bring us all.

Before we put our feelings and thoughts of 2025 behind us, what did you find interesting last year? These were the top ten popular posts from last year:

Find where a command is used in programs

A friend had been tasked to determine which programs used a particular CL command. When telling me about this she mentioned a CL command I had never used: Print Command Usage, PRTCMDUSG. She had used this command to check which CLP programs used the CL command.

Never having used this command, I wanted to try it for myself. I created a scenario where I wanted to find all the programs in my library that use the CL command DSPOBJD.

I created three programs with the DSPOBJD. The first I called TESTCL1, as a modern CLLE program:

01 DLTF FILE(QTEMP/OUTFILE) 02 MONMSG MSGID(CPF2105) 03 DSPOBJD OBJ(MYLIB/*ALL) + 04 OBJTYPE(*FILE) + 05 OUTPUT(*OUTFILE) + 06 OUTFILE(QTEMP/OUTFILE)

The second program, TESTCL2, I created as an OPM CLP program.

Saving using the ZLIB algorithm

ZLIB was introduced as an improved save compression algorithm as part of IBM i 7.4. With IBM Power10 processors the algorithm uses the on-chip Nest Accelerator (NX) GZIP, therefore, it is faster, less CPU intensive, and produces a smaller save file than other compression routines. I wanted to try it out to see how big a difference it would make.

First thing I need to do is to check if the IBM Power server and model I will be performing my tests upon is at least a Power 10. I can get the model number from the system values, but I cannot get the system type. The most convenient place I know to get that is from the API QLZARCAPI. After running the following:

01 CALL QSYS/QLZARCAPI

And I look in the job log I can see information on the first line of data:

SYSTEM INFO -> SYSTEM SERIAL NUMBER: XX-XXXXX . SYSTEM TYPE-MODEL: 9105-22A.

I admit I do not know all the system types and model numbers, therefore, I googled it and found that I am on an IBM Power S1022.

Determine if a number is even or odd

Have you ever had the need to determine if a number is even or odd? I have had to in various scenarios. This has been made a lot easier with the addition of a couple of scalar functions that have been added as part of the latest Technology Refreshes.

In the past I would need to check the remainder of dividing a number by two.

If I was to do this in RPG my program could look like:

01 **free 02 dcl-s Number packed(1 : 0) ; 03 dcl-s Remainder packed(1 : 0) ; 04 Number = 6 ; 05 Remainder = %rem(Number : 2) ; 06 dsply ('1. Remainder = ' + %char(Remainder)) ; 07 Number = 7 ; 08 Remainder = %rem(Number : 2) ; 09 dsply ('2. Remainder = ' + %char(Remainder)) ; 10 *inlr = *on ;

Improvement to RPG compile listing

One thing that has always annoyed me within RPG compiler listings is how it handles long variables names. In the "Additional diagnostics messages" section of the listing it did not list the entire variable name, just the first seven characters followed by an ellipsis ( ... ). This could lead to some confusion if there is more than one variable that have the identical first seven characters.

I know this is an extremely simple piece of code, but it illustrates what happened:

01 **free 02 dcl-s Really_not_this_one char(1) ; 03 Really_long_variable_name = 'X' ; 04 *inlr = *on ;

Line 2: A variable is defined with the name Really_not_this_one.

Line 3: I then use a variable that has not been defined, Really_long_variable_name, that has the same first seven characters as the variable I defined on line 2.

When I compile this it fails with a level 30 error, as the variable on line 3 has not been defined.

New date formats for RPG

Included within the new Technology Refreshes, IBM i 7.6 TR1 and IBM i 7.5 TR7, comes three new date formats to help us with the 2040 date problem. All of the new data formats include the century in the date:

• *DMYY:  DD/MM/YYYY format
• *MDYY:  MM/DD/YYYY format (isn't this the same as the *USA format?)
• *YYMD:  YYYY/MM/DD format

These, like the other date formats, can be used with following Built in Functions, BiFs, and operation codes:

• %CHAR:  Convert to character BiF
• %DEC:  Convert to decimal number BiF
• %DATE:  Convert character or decimal to date BiF
• MOVE and MOVEL:  Move and move left operation codes
• TEST(D):  Test date operation code

Let me give some examples of using these new data formats:

Create a program to change the size of a file, reuse deleted records, and remove deleted records

This is the final part of the following trilogy:

1. Change the file's size and reuse deleted records
2. Remove the deleted records from the file
3. Create a program to perform both of the above

The earlier posts described how to manually perform the SQL statements needed. In this post I am going to show a program that combines both of the SQL statements, and makes a program that can be run time and again.

I am not going to repeat a lot of what I said in those posts, therefore, I recommend you read them before you start with this one.

I will show this program in three parts, as I think that will make it easier to explain and for you to understand. This is the first part: