Separating parts of qualified job name with SQL

In a previous post I explained how I could extract the parts of the qualified job name using the LOCATE_IN_STRING scalar function. With the latest Technology Refreshes, IBM i 7.6 TR2 and 7.5 TR8, a number of Db2 for i scalar functions and a table function were added to make this easier. These are:

• JOB_NAME scalar function:  Returns the job name
• JOB_NUMBER scalar function:  Returns the job number
• JOB_USER scalar function:  Returns the job user
• JOB_NAME_DETAILS table function:  Returns all of the above

All of the above are found in the library SYSTOOLS.

Validating CL commands with SQL

There has been an API to validate CL commands for as long as I can remember, but is not simple to use. Fortunately, as part of the latest Technology Refreshes, IBM i 7.6 TR2 and 7.5 TR8, a Db2 for i scalar function was introduced that performs the same function.

CHECK_COMMAND_SYNTAX requires one parameter, the command string to be validated. The string can be up to 32,000 characters, which should cover most command strings I use. It returns a Boolean value:

• True = the command string is valid
• False = the command string is invalid

Here are a couple of examples I made and ran in ACS's Run SQL Scripts, RSS:

Creating and using a data journal reader

Every so often IBM delivers something for the IBM i that makes me say "Wow!" Data journal readers are one of these cases. It has changed the way I am thinking of retrieving data from journals.

The CREATE_DATA_JOURNAL_READER SQL scalar function was introduced as part of IBM i 7.6 TR2 and 7.5 TR8, and is very simple to set up. It creates a SQL table function that allows me to see the entries for one of the files whose data is within a journal.

Before I can show examples of this, I need a file and it needs to be journaled.

I created a DDS file, TESTFILE, in my library, MYLIB. It contains nine fields I named FIELD1 to FIELD9. The layout of the file is immaterial to showing how to create and use a data journal reader.

Once the file was created, I needed it to be journaled. Which I did with the following commands:

Convert number to hexadecimal with SQL

In an earlier post I wrote about how to convert a character string to hexadecimal, and back to character again. The obvious question followed:

What if wanted to convert a number to hexadecimal with SQL?

One of the issues is that numbers come in different forms in modern RPG and in SQL. Here are the common types of numbers I encounter:

Type	RPG	SQL
Packed	PACKED	DECIMAL, DEC
Signed	ZONED	NUMERIC, NUM
Integer	INT	INTEGER, INT, SMALLINT, BIGINT

In this post I am going to give examples of how to convert each of these RPG number types to hexadecimal, and then back again.

This is my program:

New pseudo columns added for triggers

I have written before about how to create triggers, RPG trigger and SQL trigger, and as I have written more, and talked with other people, SQL triggers are the better trigger.

This has been reinforced an addition to Db2 for i (SQL) as part of the latest rounds of Technology Refreshes, IBM i 7.6 TR2 and 7.5 TR8, what have been called "trigger pseudo columns":

• TRIGGER_FILE_NAME:  File that caused the trigger to execute
• TRIGGER_FILE_LIBRARY_NAME:  The library that contains that file
• TRIGGER_FILE_MEMBER_NAME:  Member in that file

I am going to add a trigger to the DDS file TESTFILE. First, I need to "clone" the file to make the trigger output file. I can do this simply with SQL:

01 CREATE TABLE MYLIB.TESTFTRG AS 02 (SELECT * FROM MYLIB.TESTFILE) DEFINITION ONLY

QCMDEXC scalar function can write to job log

The QCMDEXC SQL scalar function is one of my favorite SQL features. I have used it many times in my work, and here in articles on this website. As part of the latest Technology Refresh, IBM i 7.6 TR2 and 7.5 TR8, it is further enhanced by now being able to write the command performed to the job log.

QCMDEXC scalar function now has two parameters:

1. COMMAND:  The CL command to perform
2. PRINT:  Whether the CL command should be written to the job log. This can contain the following:
   • ERROR:  Only write to the job log if the command fails
   • NONE:  No command is written to the job log. This is the default
   • VERBOSE:  The command is always written to the job log

Before I start describing in details what this change does, I need to have something I can do using the QCMDEXC scalar function. In this scenario I have a DDL table, TESTTABLE, that contains a list of files that I want to delete using a Delete File command, DTLF, in this scalar function.

I can show the contents of this table with the following:

01 SELECT * FROM TESTTABLE

It is now so easy to view CVE using SQL

Before I start describing how to use this new SQL Table Function, I need to explain what CVE are.

Common Vulnerabilities and Exposures, CVE, are standardized, publicly identified cybersecurity flaws in software or firmware. When IBM, or a researcher, find a flaw in IBM i, or related software, it is given a unique id. These ids are cataloged globally by the MITRE Corporation, and published with their severity and impacts.

You can learn more about CVE on IBM's "What is CVE?" page.

I cannot find a CL command that allows me to view CVE details. I would have to search for a particular CVE using the IBM Product Security Central page.

With the latest Technology Refreshes, IBM i 7.6 TR2 and 7.5 TR8, is a new SQL Table Function that allows me view, and search, all of CVE from my IBM i partition.

The CVE_INFO Table Function is found in the library SYSTOOL, it has one parameter the IBM i release number. I went through and checked for each release to find that V5R3 is the earliest there are results for:

Program to alert when elapsed CPU percentage becomes too high

When I published my article about Program to alert when jobs' CPU percentage becomes too high someone messaged me asking could I think of something that would do the same when the total CPU exceeded a certain percentage. The answer is, of course, "Yes".

Before I create a program we need to consider the whether the partition that the program will be run on is "capped" or "uncapped". What does that mean?

When a partition is capped it cannot use more than its allocated processing units, in other words it can never exceed 100% CPU usage.

While an uncapped partition has its base entitlement of the processing units, but can borrow unused processing power from the other partitions if available. This means that its CPU usage can exceed 100% of the partition's resources.

This means that 90% CPU utilization could be more concerning on a capped than an uncapped. Keep this in mind when you look at my example program.

I always use ACS's Run SQL Scripts, RSS, to develop the SQL statements I will be including in a RPG program. I can get the elapsed CPU percentage from the SYSTEM_STATUS SQL table function:

Using SQL to retrieve information from the internet

There are many different web sites that offer information that would be useful to consume in IBM i. I have seen several examples of doing this, and I thought I would show my example, which I think, shows how simple it can be.

In my example I want to retrieve the share prices for the three biggest technology companies in the city I live in:

• Dell, stock symbol: DELL
• IBM, symbol: IBM
• Tesla, symbol: TSLA

Retrieving the value of a column from a previous row

I was sent a copy of a job log from weeks ago, and had been asked to identify which parts of the job had run the longest. Fortunately, the spool file of the job log was still available so I could do some SQL magic to extract useful information from it. The first thing I want to do is to determine is how long each program or command took. I can extract the timestamp from the spool file, but I need to be able to retrieve the timestamp for the previous entry too. How can I retrieve that information for the previous entry?

After a bit of searching I found a SQL function that would allow me to do this. Rather than go straight into showing what I did with the joblog spool file, I am going to start with a simpler example. I have a DDL table, TESTTABLE, that has a decimal column, TEST_NUMBER, and nine rows of data. I can show the data using the following SQL statement:

01 SELECT TEST_NUMBER 02 FROM TESTTABLE

Which returns:

Calculating the number of seconds for a time

I have to admit this was a strange request. Interfacing data to another application is no big deal, but the way it wanted its date and time data formatted was. It wanted a character date, in *MDY format with the date separator characters, character time, with dots as the separator character, and an integer of the number of seconds that the time represented.

I could calculate the seconds as:

TotalSeconds = (Hours x 3600) + (Minutes x 60) + Seconds

Fortunately I stumbled across a SQL scalar function that would do this for me: MIDNIGHT_SECONDS. This returns the number of seconds that the time represents. Which is exactly what I am looking for.

I can demonstrate this with the following SQL statement:

Program to alert when jobs' CPU percentage becomes too high

I was asked if there was a way to alert if a job exceeded a certain threshold of CPU percentage. The IBM i operating system collects data through Collection Services. Navigator for i and Performance Data Investigator, PDI, can be used to analyze CPU heavy jobs, but this is all historical data there is no real time alerting.

There are various tools from ISV that can do this. For this website I always write about solutions using just what comes in the IBM i operating system, with no third party software.

This problem allows me to use one of my favorite Db2 for i table function, ACTIVE_JOB_INFO. This table function allows me to retrieve information about all of the active jobs on my partition.

Determining the Ordinal date with SQL

A friend asked me if there was a simple way to calculate the "Julian" date when retrieving dates from a DDL table or DDS file with a date type field.

Before I continue I need to describe the difference between the Julian and the Ordinal dates. While we all call a date in YYYYDDD a "Julian" date, that is not correct. The Julian date is a count of days since January 1, 4713 BCE. I found this helpful link describing why it is this date. The correct name for the YYYYDDD date is the Ordinal date, which is recognized by the ISO 8601 standard.

Db2 for i includes a Julian day scalar function. The statement below show me using it with the date June 1, 2026:

01 VALUES JULIAN_DAY('2026-06-01')

The result is:

Calculating the power and square root of a number

There are times I need to calculate the dimensions of objects, and to do that I need to use things like x squared, or the square root of y. It all brings back memories of sitting in mathematics lessons back in high school, a very long time ago.

It is simple in RPG, but I want to do it in SQL so that I can calculate the information I need in my SQL View.

I thought it would be a good reminder to show how to calculate the square root and how to use exponentiation (x^y, x to the power of y) in both RPG and SQL.

 

Determine the length of a string when using SQL

The title is not as descriptive as I would have liked, my original title was too long to appear reasonably at the top of this page. I am going to describe how you can determine the length of the string of characters within a column from a DDS file or DDL table using SQL. I have found this most useful when using it within a Select statement.

This SQL function can be called by one of two names: CHARACTER_LENGTH or CHAR_LENGTH.

There is also a related function, LENGTH, that I will describe later in this post.

As its name suggests, CHARACTER_LENGTH will return the length of a string expression. In the following example I am using the VALUES SQL statement with CHARACTER_LENGTH:

01 VALUES CHARACTER_LENGTH('A ')

Checking if there are updates for your Group PTFs

One of the duties of a good System Administrator is to keep the group PTFs up to date on the IBM i partitions you are responsible for. To assist with this IBM has "Group PTF Currency". The first time I encountered this phrase I was confused as I could not equate how PTFs related to money. The confusion was cleared up when I found out the "currency" refers to how current your PTFs are.

In the past the System Administrator would have manually check IBM's Fix Central website and compare the version of the PTFs on their partitions to the ones shown.

To make life easier IBM created a SQL view, GROUP_PTF_CURRENCY, in IBM i 7.1 that would retrieve the latest information about the group PTFs from the IBM "Preventive Service Planning", PSP, website. This View shows the PTF group level installed and the latest group level available. By comparing the two the System Administrator can decide whether it is time to get and apply the latest PTF group.

We can see the data on the PSP website as is it an XML file and can be reached at:

Checking the Electronic Service Agent

The Electronic Service Agent, ESA, is part of the IBM i operating system. Its purpose is to monitor your system's health, detect potential hardware failures, and report issues directly to IBM Support. If it encounters problems it can "call home" to report the issue to IBM.

To be able to "call home" it must be able to connect to IBM. How can we test that it can?

We can either use:

• VFYSRVAGT the Verify Service Agent command
• ELECTRONIC_SERVICE_AGENT_INFO SQL View that shows the same information

I am going to explain both of these below.

Track Group PTFs with SQL

I have mentioned the SQL View GROUP_PTF_INFO a couple of times in passing in other posts on this website, I have not explained what this View gives us.

A PTF Group is a bundle of related PTFs which are designed and managed as a single group. This simplifies PTF updates as all the necessary individual PTFs for a specific group are applied together. Examples of PTF groups are:

• SF99760 for cumulative PTFs
• SF99960 for Db2 for i
• SF99962 for the IBM HTTP server

There are several ways I can check on the PTF Groups on my IBM i partition:

• DSPPTFGRP:  Display PTF Group command
• WRKPTFGRP:  Work with PTF Group command
• GROUP_PTF_INFO:  SQL View

In all of my examples I am going to use the PTF Group for Db2 for i, SF99960, on a partition that is running IBM i 7.6 .

When I use the commands DSPPTFGRP and WRKPTFGRP I am frustrated that I cannot get an output file of the information it retrieves. With WRKPTFGRP I can only display the results:

New Job columns added to the Record Lock View

In my last post I explained how the parts of the IBM i job name had been added as separate columns to the OBJECT_LOCK_INFO SQL View. I am pleased to say that the same three columns have been added to the RECORD_LOCK_INFO View too:

1. JOB_USER:  User profile of the job
2. JOB_NAME_SHORT:  Name of the job
3. JOB_NUMBER:  Number of the job

Before these columns were added, if I wanted to list my jobs, with the user profile "SIMON", I would need to extract it from the full job name or use a wildcard in the Where clause. With the addition of these three columns, I will show much easier this becomes.

If I wanted to check for record locks, not using RECORD_LOCK_INFO, I would use the Display Record Locks command, DSPRCDLCK. For example, if I wanted to check for record locks on TESTFILE, in my library, I would use the following:

01 DSPRCDLCK FILE(TESTFILE)

Which will show me the following:

New columns added to the Object Lock View

While the addition of these three new columns to the OBJECT_LOCK_INFO SQL View may not appear to be significant, I know it will make it easier for me to process data for jobs.

The three new columns are all part of the long job name, and now have their own columns:

1. JOB_USER:  User profile of the job
2. JOB_NAME_SHORT:  Name of the job
3. JOB_NUMBER:  Number of the job

Prior to the addition of these columns if I wanted to list my jobs, with the user profile "SIMON", I would need to extract it from the full job name or use a wildcard in the Where clause.

Before I show any SQL statements I need to explain the scenario I created. I have two programs:

1. Program 1: This RPG program opens the file TESTFILE for update and then pauses for ten minutes
2. Program 2: The second program uses a display file, TESTDSPF, and uses the EXFMT operation code to show it on the screen