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