Adding check constraints to your database

I have previously written about other types of constraints: unique, primary key, and referential. In this post I am going to describe the check constraint, which is a way I can "push" validation of data into the database, rather than have the logic in all the programs that insert, update, or delete the data from the file or table.

I am going to use a table called PARENT again. I have added a couple of additional columns to it:

01 CREATE TABLE MYLIB.PARENT ( 02 PARENT_ID INTEGER NOT NULL, 03 LAST_NAME VARCHAR(30) NOT NULL, 04 FIRST_NAME VARCHAR(20) NOT NULL, 05 DATE_OF_BIRTH DATE NOT NULL, 06 START_DATE DATE NOT NULL, 07 STATUS CHAR(1) NOT NULL, 08 PRIMARY KEY (PARENT_ID), 09 CONSTRAINT PARENT_ID_CHECK CHECK(PARENT_ID > 0), 10 CONSTRAINT START_DATE_CHECK CHECK(DATE_OF_BIRTH < START_DATE) 11 ) ;

How to view the top 10 jobs using the most CPU

The germ of the idea for this post came from a question I was asked. The question was for screen that would show the top ten jobs consuming the most CPU, which would refresh on a regular basis. In previous posts I have written about the parts needed to achieve the desired result, here I am going to put it all together.

How do I get the jobs that are consuming the most CPU? I can get the elapsed CPU percent and CPU time from one of my favorite Db2 for i Table functions, ACTIVE_JOB_INFO.

The statement I will be using is:

01 SELECT JOB_NAME, 02 ELAPSED_CPU_PERCENTAGE, 03 ELAPSED_CPU_TIME 04 FROM TABLE(QSYS2.ACTIVE_JOB_INFO( 05 RESET_STATISTICS => 'NO', 06 DETAILED_INFO => 'NONE')) 07 ORDER BY ELAPSED_CPU_PERCENTAGE DESC,ELAPSED_CPU_TIME DESC 08 LIMIT 10

Fall 2025 Technology Refreshes announced

The announcement for the latest round of Technology Refreshes for IBM i has been announced for 7.5 (TR1) and 7.5 (TR7). The planned availability date for these is November 21

Having given the announcement and enhancement information a quick read I see lot more useful changes for us.

The Db2 for i enhancements that caught my eye were:

• CERTIFICATE_USAGE_INFO:  Returns information about applications that use certifications and the related certificate information
• QIBM_SELF_BY_DAYS:  Global variable that will allow for the automatic purging of rows from the SQL_ERRORT, SELF Error Log, table
• QIBM_SYSIXADV_BY_DAYS:  Global variable that will allow for automatic purging of rows from the SYSIXADV, System Index Advisor, table

October's presentations

On Thursday 23 October, 16:00 – 17:45 (CET), I am doing an online presentation to COMMON Austria, for their 16th "Know Your IBM i Websession".

You can learn about it, and register, here.

I g'freu mi drauf, di dort zu seh’n.

October is also the start of the extremely popular System i Developer Lunch and Learn series. For three weeks, Tuesday October 21 – Thursday November 6, System i Develop is offering a series of free technical discussions from IBM i experts: Jon Paris, Susan Gantner, Patrick Behr, Mike Pavlak, and myself.

You can see what the Lunch and Learn sessions are, and register for them, here.

I hope to see you, virtually, at my presentations.

Basic constraints with DDS files

In my previous post I described how I could add several constraints to DDL Tables. Here I am going to describe how I can do the same with DDS physical files.

I will have two sets of parent and child files. The first I will add the constraints using SQL statements. The second I will use the Add Physical File Constraint command, ADDPFCST.

I will be adding the following constraints to the physical files:

1. Add primary key constraint using SQL
2. Add primary key constraint using CL command
3. Foreign key constraint using SQL
4. Foreign key constraint using CL command

 

Basic constraints with SQL tables

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:

1. Unique constraint
2. Primary key constraint
3. Foreign key constraint

There are other types of constraints, but for this post I am only going to write about these.

 

Get your copy of the Db2 poster

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.

EoS for 7.4 announced

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.

Extracting the same data from multiple spool files with SQL

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)

Forta survey

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.

Click here to take part.

Click here to see the results of last year's survey.

Calculating the remainder using SQL

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)

RPGPGM.COM-unity i Norge

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.