Creating generic global variables

I do use global variables, maybe the word "a lot" is an exaggeration but the phrase "many times" springs to mind. There are just some SQL statements I build in an RPG program that does not allow me to use a local (RPG) variable. I can use the value contained within a global variable in its place.

I do have a few global variables I use for a single purpose, account number, company number, etc. There are times I just want to have somewhere to put a value into so that I can use it in a SQL statement. To accommodate this general functionality, I have created three "generic" global variables, that I can put miscellaneous values into and then use. One of the advantages of using a global variable is that the value is "local" to the job, i.e., the value in the global variable cannot be seen or used by another job. Therefore, once I have a generic global variable any job can use it, without the danger of overlaying the value from another job.

I decided to create three generic global variables.

WRKQRY missing from IBM i 7.6

I had found that the Work Query command, WRKQRY, was not working on the IBM i 7.6 partition I have been using. I thought that, perhaps, Query had been uninstalled from the partition.

I have learned that IBM is aware of this issue, described as:

WRKQRY does nothing, even though the QU1 LPP is installed

There is a PTF to re-enable the command, that was release on November 19. As the latest Technology Refresh was released after this date, if you already applied IBM i 7.6 TR1 I would check if you have already installed this PTF.

The "Known Issues" page is here.

And the "Fix information" page for the correcting PTF, SJ05457, is here.

The Run Query command, RUNQRY, has been working.

 

This article was written for IBM i 7.6.

New version of ACS 1.1.9.10

On Friday, along with the new Technology Refreshes, IBM announced there was new version of ACS. When I checked my ACS for an update (Help > Check for updates), the day before I published this post, I was not alerted that there is a new version.

If you do not see the window telling you there is an update you will need to go to the IBM's ACS website, http://ibm.biz/IBMi_ACS (the URL is case sensitive), and download the install file from the website.

Don't worry if you don't have an IBMid, you can create one in a couple of minutes.

Confirm your agreement with IBM's license.

You will then be presented with the "IBM i Access Client Solutions" page. The download for ACS's latest version is the first download.

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