Retrieve information about SQL state

For over a year we have had a SQL table function to be able to retrieve information about the SQL codes. Now we can do the same kind of thing for SQL states. The major difference is that we retrieve the information for the SQL statuses from a SQL View: SQLSTATE_INFO in the library QSYS2.

When working with SQL codes and statuses it is important to remember that they are not all a one-to-one relationship. There are some SQL states that are associated with more than one SQL code, and there are some SQL codes that are associated with more than one SQL state.

The SQLSTATE_INFO view returns three columns:

• SQLSTATE_VALUE:  SQL state
• SQLCODE_VALUE:  SQL code
• SQLSTATE_DETAIL:  Text for the SQL state. Interestingly this is returned only in English

If SQL state is associated with more than one SQL code each combination will have its own row.

November's presentations

I will be involved with three events this month:

Wednesday November 5, starting at 6:30 PM (CT), is the next meeting of the Central Texas IBM i User Group, CTXiUG. IBM champion Gregory Simmons is the guest speaker, and he will be talking about becoming an ACS power user.

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

The following day, Thursday November 6 starting at :100 PM (ET), is the final installment of System i Developer's Lunch and Learn series. I will be presenting my top five favorite SQL.

If you have not already registered for the Lunch and Learn series you can do so here.

Updated:

Finally, on Tuesday November 18, starting at 2:00 PM (CT), I will making two back-to-back presentations to QUSER.

This is online. You can learn more and register here.

I hope to see you, virtually, at all of these presentations.

Deleting the journal receivers of QAUDJRN

QAUJRN is the system's audit journal that captures various pieces of information that you want it to. A friend has a job that uses the Display Journal command, DSPJRN, to retrieve the data from QAUDJRN.

01 DSPJRN JRN(QSYS/QAUDJRN) RCVRNG(*CURCHAIN) + 02 FROMTIME(&FROMTIME) TOTIME(&TOTIME) + 03 ENTTYP(AF) + 04 OUTPUT(*OUTFILE) OUTFILE(SOMELIB/JRN_AF)

One day his job errored, and he reached out to me for help as it returned an error he had never seen before. He sent me the job log, and I found the following within it:

Checking for expired certificates

IBM i uses certificates for various functions, and certificates will expire. I wanted to find a way where I could check the certificate store for any certificates that would be expiring soon. Whatever method I wanted needed to be simple so that I could move to other partitions too.

Fortunately, there is a SQL Table Function that will give me this information, CERTIFICATE_INFO. It has two parameters:

1. CERTIFICATE_STORE_PASSWORD:  Password for the certificate store. Rather than providing a password I can use the value '*NOPWD', which will retrieve the certificate password from the stashed password file. If a password is used it is recommended it is passed as a variable, so that people cannot see what it is.
2. CERTIFICATE_STORE:  The name of the certificate store. There are three special values that can be used as well:
   • *OBJECTSIGNING:  The object signing certificate store
   • *SIGNATUREVERIFICATION:  The signature verification certificate store
   • *SYSTEM:  The system certificate store. This is the default value the certificate store parameter is not used

To use this Table Function you must have *ALLOBJ and *SECADM authority.

Regular readers know that I always recommend if this is the first time you are using this Table Function you want to see all the columns. To do that I would use the following statement:

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

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)