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)) ;

January's presentations

I will be involved with one event this month:

Tuesday January 13, starting at 6:30 PM (CT), I will be presenting at this month's Central Texas IBM i User Group, CTXiUG, meeting. I will be talking about arrays in modern RPG.

It is online only. You can learn more about it and register here.

I hope to see you there!

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:

Remove the deleted records from the file

This is the second part of the trilogy I started last week:

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

In this post I will be giving an example of how I chose to remove the deleted records from all of the files in a library.

When a record is deleted from a file its space is not available to be reused, unless the file is reusing deleted records. Over time this can result in files have a few active records and many deleted ones. This is a waste of the available storage.

The Reorganize Physical File Member command, RGZPFM, is the command that will remove the delete records from a physical file. You need to be careful when using this command. If any of the files in the library are record address files the reorganization could make it impossible to retrieve the expected records from the file, do not reorganize them.

Change the files' size and reuse deleted records

Recently I have been surprised there have been several messages during the day-end process alerting that various files are full. The on-call system administrator has been answering the messages without issue, and the job continues. This is a sign there is some "clean up" that needs to be performed upon the files this error happened to:

• File size
• Remove deleted records from the file

I could use the system reply list, which will automatically answer a message for me. But this will make the response to the error happen to every time, and in this case to all files, that the errors happens to. Which is not what I want.

Making the changes I am going to suggest something that can be and easily performed on a single file basis. I want to be proactive and stop the error from happening for all the files in a library.

Rather than pack all of what I did into one post I am going make this a trilogy:

Creating generic global variables

I do use global variables, maybe the word "a lot" is an exaggeration but the phrase "many times" springs to mind. There are just some SQL statements I build in an RPG program that does not allow me to use a local (RPG) variable. I can use the value contained within a global variable in its place.

I do have a few global variables I use for a single purpose, account number, company number, etc. There are times I just want to have somewhere to put a value into so that I can use it in a SQL statement. To accommodate this general functionality, I have created three "generic" global variables, that I can put miscellaneous values into and then use. One of the advantages of using a global variable is that the value is "local" to the job, i.e., the value in the global variable cannot be seen or used by another job. Therefore, once I have a generic global variable any job can use it, without the danger of overlaying the value from another job.

I decided to create three generic global variables.