Determining the Ordinal date with SQL

A friend asked me if there was a simple way to calculate the "Julian" date when retrieving dates from a DDL table or DDS file with a date type field.

Before I continue I need to describe the difference between the Julian and the Ordinal dates. While we all call a date in YYYYDDD a "Julian" date, that is not correct. The Julian date is a count of days since January 1, 4713 BCE. I found this helpful link describing why it is this date. The correct name for the YYYYDDD date is the Ordinal date, which is recognized by the ISO 8601 standard.

Db2 for i includes a Julian day scalar function. The statement below show me using it with the date June 1, 2026:

01 VALUES JULIAN_DAY('2026-06-01')

The result is:

June's presentations

My presentations this month start today, June 1! At 4:00 PM (CT) I am the guest speaker for COMMON N2i as part of their "N2i Getting Started" series. I will be talking about a few of my favorite RPG Built in Functions, BiFs, and why should be using them. This is a longer and more detailed presentation than I gave as part of System i Developer's Lunch & Learn last week.

You can register for this online event by clicking on the link on this page here.

For the rest of the month my presentations take on an international flavor.

Wednesday June 17 at 12:00 PM (ET) I will be virtually hanging out with my friends at the Toronto Users Group (TUG), Canada, talking about supercharding your SQL in RPG programs.

You can register by clicking on the link on this page.

Thursday 25 June I will be talking to COMMON Austria, at 4:00 PM (CET, 9:00 AM (CT)) about what is IBM i? and what is new and useful in CL programming language.

The event is online, and you can register here. Wir sehen uns dort!

ACS version 1.1.9.13 available

Yesterday, Wednesday May 27, I noticed that a new release of ACS, 1.1.9.13, is now available for download.

This is an update to remediate a number of CVE, and no enhancements have been added.

Calculating the power and square root of a number

There are times I need to calculate the dimensions of objects, and to do that I need to use things like x squared, or the square root of y. It all brings back memories of sitting in mathematics lessons back in high school, a very long time ago.

It is simple in RPG, but I want to do it in SQL so that I can calculate the information I need in my SQL View.

I thought it would be a good reminder to show how to calculate the square root and how to use exponentiation (x^y, x to the power of y) in both RPG and SQL.

 

Determine the length of a string when using SQL

The title is not as descriptive as I would have liked, my original title was too long to appear reasonably at the top of this page. I am going to describe how you can determine the length of the string of characters within a column from a DDS file or DDL table using SQL. I have found this most useful when using it within a Select statement.

This SQL function can be called by one of two names: CHARACTER_LENGTH or CHAR_LENGTH.

There is also a related function, LENGTH, that I will describe later in this post.

As its name suggests, CHARACTER_LENGTH will return the length of a string expression. In the following example I am using the VALUES SQL statement with CHARACTER_LENGTH:

01 VALUES CHARACTER_LENGTH('A ')

Generating help text using an IBM i command

This is another post about something I did not know existed until a few days ago. I have been building some commands to help automate certain processes, and I thought it would be a good idea to create help text for the commands so that anyone using it could use it instead of asking me how to use the command.

Help text for commands must be in the form of a panel group object, *PNLGRP. The programming language used to create the contents of a panel group is User Interface Manager, UIM. Don't worry if you have never used UIM, I think it is a simple language to understand.

Several years ago I wrote a post about creating help panel groups with UIM, therefore, if you want more information than I am going to mention here please refer to that post.

I "stumbled" across the command GENCMDDOC, Generate Command Documentation, which will create a template for my command that I can then add details to. I admit I have never heard of it before, and having used it I found it saved me a lot of time.

First I need a command. I just randomly copied some parts of commands I had created earlier into one new command, TESTCMD.

PowerUp attendees rally to join RPGPGM.COM-unity

The RPGPGM.COM-unity continues to grow. I handed out more of the ribbons at this year's COMMON PowerUp than I have at any other event. You can see who I gave them to here.

If you see me at an IBM i event feel free to introduce yourself to me. 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.

Determining the number of entries in a list parameter from a command

I was creating a command with what I am going to call a "list parameter", to allow the entry of up to ten libraries. While writing the Command Processing Program, CPP, I encountered something I could not find a good description of how to handle. Therefore, I am writing this post to give you a good example of how I did it.

I am not going to go into too much detail on how to create commands and their parameters as I covered many of the basics in two earlier posts: Creating your own commands, part 1 and Creating your own commands, part 2.

My example command, MYCMD, has just one parameter, which will be the list of up to ten libraries. The source for the command is as follows:

01 CMD PROMPT('List of libraries') 02 PARM KWD(LIBRARY) TYPE(*NAME) MIN(1) MAX(10) + 03 PROMPT('Libraries')

Line 2 and 3: The command parameter LIBRARY has a number of keywords:

• KWD:  The keyword name for the parameter
• TYPE:  It is a name type parameter, which means it is equivalent of ten long character
• MIN:  The minimum number of entries that must be given
• MAX:  The maximum number of allowed entries
• PROMPT:  The description that appears on the screen

Checking if there are updates for your Group PTFs

One of the duties of a good System Administrator is to keep the group PTFs up to date on the IBM i partitions you are responsible for. To assist with this IBM has "Group PTF Currency". The first time I encountered this phrase I was confused as I could not equate how PTFs related to money. The confusion was cleared up when I found out the "currency" refers to how current your PTFs are.

In the past the System Administrator would have manually check IBM's Fix Central website and compare the version of the PTFs on their partitions to the ones shown.

To make life easier IBM created a SQL view, GROUP_PTF_CURRENCY, in IBM i 7.1 that would retrieve the latest information about the group PTFs from the IBM "Preventive Service Planning", PSP, website. This View shows the PTF group level installed and the latest group level available. By comparing the two the System Administrator can decide whether it is time to get and apply the latest PTF group.

We can see the data on the PSP website as is it an XML file and can be reached at:

Checking the Electronic Service Agent

The Electronic Service Agent, ESA, is part of the IBM i operating system. Its purpose is to monitor your system's health, detect potential hardware failures, and report issues directly to IBM Support. If it encounters problems it can "call home" to report the issue to IBM.

To be able to "call home" it must be able to connect to IBM. How can we test that it can?

We can either use:

• VFYSRVAGT the Verify Service Agent command
• ELECTRONIC_SERVICE_AGENT_INFO SQL View that shows the same information

I am going to explain both of these below.

ACS version 1.1.9.12 available

 

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

 

Track Group PTFs with SQL

I have mentioned the SQL View GROUP_PTF_INFO a couple of times in passing in other posts on this website, I have not explained what this View gives us.

A PTF Group is a bundle of related PTFs which are designed and managed as a single group. This simplifies PTF updates as all the necessary individual PTFs for a specific group are applied together. Examples of PTF groups are:

• SF99760 for cumulative PTFs
• SF99960 for Db2 for i
• SF99962 for the IBM HTTP server

There are several ways I can check on the PTF Groups on my IBM i partition:

• DSPPTFGRP:  Display PTF Group command
• WRKPTFGRP:  Work with PTF Group command
• GROUP_PTF_INFO:  SQL View

In all of my examples I am going to use the PTF Group for Db2 for i, SF99960, on a partition that is running IBM i 7.6 .

When I use the commands DSPPTFGRP and WRKPTFGRP I am frustrated that I cannot get an output file of the information it retrieves. With WRKPTFGRP I can only display the results: