ACS 1.1.9.10 is replaced

 

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

 

Fall 2025 Technology Refreshes available

The Technology Refresh PTFs for IBM i 7.6 TR1 and 7.5 TR7 become available today.

My recommendation is you download the latest CUM PTFs as that should include all of the PTFs for your release.

Do check if the Database (SQL) PTFs are included:

• IBM i 7.6 TR1:  SF99960 Level 2
• IBM i 7.5 TR7:  SF99950 Level 11

And the ones for RPG too:

• IBM i 7.6 TR1:  5770WDS SJ08065, and for TGTRLS(V7R5M0) support:  5770WDS SJ08092
• IBM i 7.5 TR7:  5770WDS SJ08064

If after applying IBM i 7.5 TR7 you notice a performance degradation you need to follow the instructions on this page here. It appears that this is not an issue for IBM i 7.6 TR1.

Retrieving the printer name from a Query

The question was posed if it possible to retrieve the name of printer that has been entered into a Query for all the Queries in a library?

I already knew how to retrieve the SQL statement from a Query. Alas, the information about the printer is not found that way.

After some searching I found a SQL procedure that will give me the information I need: PRINT_QUERY_DEFINITION. For some reason there is no mention of this in IBM's documentation portal. I found reference to it in the IBM Support portal.

PRINT_QUERY_DEFINITION generates a spool file that lists all the information about the Query, including the choice of output.

My scenario is that I want a list of the Queries in a library and which printers they are defined to use.

Not all Queries use printers. Some will only display, and others will output to an output file. For this example, I created four Queries. The files they are built over, fields selected, selection criteria, column formatting, etc. is irrelevant. All that matters is the output type. This can have three values:

1. = Display the results on the screen
2. = Direct the output to a spool file
3. = Direct the output to an output file

Deleting QAUDJRN's journal receivers if they are in QSYS

A couple of weeks ago I wrote about deleting the System Audit Journal's receivers. The scenario had the journal's receivers not in the QSYS library, but in QGPL. Someone messaged me saying that in their IBM i partition QAUDJRN's receivers are in QSYS. When they ran the SQL procedure to delete old journal receivers, DELETE_OLD_JOURNAL_RECEIVERS it returned no results.

01 CALL SYSTOOLS.DELETE_OLD_JOURNAL_RECEIVERS( 02 DELETE_OLDER_THAN => CURRENT_TIMESTAMP, 03 JOURNAL_RECEIVER_LIBRARY => 'QSYS', 04 JOURNAL_RECEIVER => 'QAUD%', 05 DELETE_UNSAVED => 'NO', 06 PREVIEW => 'YES')

I checked the last save information for the journal receivers in QSYS. The SQL table function OBJECT_STATISTICS's SAVE_TIMESTAMP column was null. Using Display Object Description, DSPOBJD, the save date field, ODSDAT, was blank. How could I determine how old each of the receivers were?

Fall 2025 IBM I on Power Performance FAQ

In the Spring and Fall IBM updates the IBM i Performance FAQ. The latest one was released on Friday.

It covers all aspects of the IBM i from hardware to software and programming. If you have not perused it I think you will find the parts that are relevant to your career interesting.

You can find it here.

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.

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