Merry Christmas to everyone

SELECT CASE WHEN UPPER(BEHAVIOR) = 'NICE' THEN 'MERRY CHRISTMAS' ELSE 'BAH HUMBUG!' END AS "Wishing you" FROM CONTACTS WHERE PERSON = :You AND THIS_YEAR = 2025 Wishing you --------------- MERRY CHRISTMAS

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.

WRKQRY missing from IBM i 7.6

I had found that the Work Query command, WRKQRY, was not working on the IBM i 7.6 partition I have been using. I thought that, perhaps, Query had been uninstalled from the partition.

I have learned that IBM is aware of this issue, described as:

WRKQRY does nothing, even though the QU1 LPP is installed

There is a PTF to re-enable the command, that was release on November 19. As the latest Technology Refresh was released after this date, if you already applied IBM i 7.6 TR1 I would check if you have already installed this PTF.

The "Known Issues" page is here.

And the "Fix information" page for the correcting PTF, SJ05457, is here.

The Run Query command, RUNQRY, has been working.

 

This article was written for IBM i 7.6.

New version of ACS 1.1.9.10

On Friday, along with the new Technology Refreshes, IBM announced there was new version of ACS. When I checked my ACS for an update (Help > Check for updates), the day before I published this post, I was not alerted that there is a new version.

If you do not see the window telling you there is an update you will need to go to the IBM's ACS website, http://ibm.biz/IBMi_ACS (the URL is case sensitive), and download the install file from the website.

Don't worry if you don't have an IBMid, you can create one in a couple of minutes.

Confirm your agreement with IBM's license.

You will then be presented with the "IBM i Access Client Solutions" page. The download for ACS's latest version is the first download.

Fall 2025 Technology Refreshes available

The Technology Refresh PTFs for IBM i 7.6 TR1 and 7.5 TR7 become available today.

My recommendation is you download the latest CUM PTFs as that should include all of the PTFs for your release.

Do check if the Database (SQL) PTFs are included:

• IBM i 7.6 TR1:  SF99960 Level 2
• IBM i 7.5 TR7:  SF99950 Level 11

And the ones for RPG too:

• IBM i 7.6 TR1:  5770WDS SJ08065, and for TGTRLS(V7R5M0) support:  5770WDS SJ08092
• IBM i 7.5 TR7:  5770WDS SJ08064

If after applying IBM i 7.5 TR7 you notice a performance degradation you need to follow the instructions on this page here. It appears that this is not an issue for IBM i 7.6 TR1.

Retrieving the printer name from a Query

The question was posed if it possible to retrieve the name of printer that has been entered into a Query for all the Queries in a library?

I already knew how to retrieve the SQL statement from a Query. Alas, the information about the printer is not found that way.

After some searching I found a SQL procedure that will give me the information I need: PRINT_QUERY_DEFINITION. For some reason there is no mention of this in IBM's documentation portal. I found reference to it in the IBM Support portal.

PRINT_QUERY_DEFINITION generates a spool file that lists all the information about the Query, including the choice of output.

My scenario is that I want a list of the Queries in a library and which printers they are defined to use.

Not all Queries use printers. Some will only display, and others will output to an output file. For this example, I created four Queries. The files they are built over, fields selected, selection criteria, column formatting, etc. is irrelevant. All that matters is the output type. This can have three values:

1. = Display the results on the screen
2. = Direct the output to a spool file
3. = Direct the output to an output file