This is another one of those posts I did not realize I had not written about, database constraints.
Constraints allow me to move the control the integrity of my data into the database itself. I am going to give some simple examples of what can be achieved with constraints with DDL Tables. Here I am going to cover the following:
There are other types of constraints, but for this post I am only going to write about these.
At this month's TUG event my friend, Sue Romano, Senior Software Engineer at IBM, showed where we could go to download the latest version of the "Db2 for i poster".
The link is found at the bottom of the "Db2 for i – Technology Updates" page. When I visited the page the bottom of the page was obscured by an "accept cookies" notice, which I had to close. Click on the link and the PDF of the poster will open.
|
If you want to go directly to this version of the poster click here.
When printing the poster I found that standard paper it is not the best. If you are in the USA, use legal size paper.
Thank you to Sue for bringing this to my attention. And to the Db2 for i team for keeping it up to date.
What I have been predicting for a while happened on Tuesday September 16, 2025. IBM announced that the end of standard support for IBM i 7.4 will occur on September 30, 2026.
After that date support for this release will only be offered as a Service Extension. In the past the Service Extension is offered at twice the cost of standard support.
If you have Power9, Power10, or Power11 you can upgrade your IBM i release to the two remaining supported releases: 7.5 and 7.6 .
If you have a Power8, 7.4 is the last release that can be run on that server. You will need to upgrade to a later Power server, Power11, to be able to upgrade to one of the newer releases.
IBM's announcement for the Withdrawal from marketing and change in service level: IBM i 7.4.
IBM's document of the IBM Power systems and the IBM i releases they can run.
The idea for this post came from a question I was asked. There were a number of program dump spool files, QPGMDMP, and the manager wanted to know what was the value in a same variable from all of the spool files. While others said there was no alternative than to browse each spool file for the information. I knew there was a better way using SQL.
In these examples I am not using program dump spool files as you would be unable to replicate what I am going to show. Therefore, I am using the output from the Work Output Queue command, WRKOUTQ, with it outputting to a spool file:
01 WRKOUTQ OUTPUT(*PRINT) |
Today, September 15, Fortra's thirteenth annual "IBM i Marketplace Survey" starts.
IMHO it is important for all of us, no matter where we are in the world, to take part. The results of this survey gives a "snap shot" of what is important to the IBM i community. You will often see IBM-ers quoting the results of the survey too.
The survey is open September 15 – October 31, with the results being published in January 2026.
Please take the time to participate in the survey.
I had a scenario where I needed to determine the remainder value following a division operation for all the rows in a SQL Table. Rather than performing the calculation myself to determine the remainder there is a SQL scalar function that will return it for me, its name is MOD.
IBM's documentation for MOD states that it "divides the first argument by the second argument and returns the remainder". Its syntax is just:
MOD( < dividend value or variable > , < divisor value or variable > ) |
For example:
01 VALUES MOD(9, 2) |
Last week I was fortunate to be in Oslo, Norway, for COMMON Norge's "Professional day". I took this opportunity to hand out some of the the "RPGPGM.COM-unity" ribbons. You can see who I gave them to here.
Norway is the fifth country I have handed ribbons out to. It is wonderful to see how the community is expanding to different areas of the world.
What is the RPGPGM.COM-unity? You will have to click on this link to learn about it.
In June I wrote about how to use SQL to check if a file existed in the IFS. I received a communication from Rich Diedrich offering an alternative, using the access C procedure. I have taken his code and developed it into this example.
I created an external procedure that I made into a service program to perform the checking, and then wrote a small RPG program to call it to validate if certain files exist.
I am going to start by showing the source code for the external procedure, which resides in a module I called TESTMOD1.