Calculating the remainder using SQL

I had a scenario where I needed to determine the remainder value following a division operation for all the rows in a SQL Table. Rather than performing the calculation myself to determine the remainder there is a SQL scalar function that will return it for me, its name is MOD.

IBM's documentation for MOD states that it "divides the first argument by the second argument and returns the remainder". Its syntax is just:

MOD( < dividend value or variable > , < divisor value or variable > )

For example:

01 VALUES MOD(9, 2)

Another way to check if file exists in IFS

In June I wrote about how to use SQL to check if a file existed in the IFS. I received a communication from Rich Diedrich offering an alternative, using the access C procedure. I have taken his code and developed it into this example.

I created an external procedure that I made into a service program to perform the checking, and then wrote a small RPG program to call it to validate if certain files exist.

I am going to start by showing the source code for the external procedure, which resides in a module I called TESTMOD1.

CVTDAT can use 1970 rule

This almost slipped past me, the information in the IBM i 7.6 documentation that says that the Convert Date command, CVTDAT can use the '1970 rule'.

Whether CVTDAT uses the '1940 rule' or the '1970 rule' depends upon the presence of the QIBM_QBASEYEAR Environment variable. If its value is '1970' that rule is used, if it is not present then the '1940 rule' is used.

How can I tell if I have that Environment variable? There are two ways. I can use the Work with Environment Variables command, WRKENVVAR:

01 WRKENVVAR

Additions to the SYSDISKSTAT

SYSDISKSTAT returns the information about disk and solid-state drives, SSD. It comes in two forms:

• SYSDISKSTAT View
• SYSDISKSTAT Table function

I am not going to say much more about using these two as I gave a lot of detail when they were first introduced.

In the latest release IBM i 7.6 and 7.5 TR6 two new columns have been added to both of the SYSDISKSTAT:

Identifying and changing object owners

Before I delete an user profile I always want to identify which objects it owns, and then transfer them to another profile or profiles. I can see which objects they own using the Work with Object by Owner command, WRKOBJOWN. If there are a lot of objects in the results I do not find this user interface helpful.

Db2 for i offers us an alternative, the OBJECT_OWNERSHIP table function. I have written about the OBJECT_OWNERSHIP view before, it would appear I overlooked the table function.

The advantage of using a table function is that only the results for the parameters passed are found, like calling an API. With a view the rows are selected according to the selection criteria, which is like searching a file or table. In various situations, one may have an have advantages over the other. In this scenario, I only want the information for one user profile, the table function is more efficient.

The syntax for the OBJECT_OWNERSHIP is simple, as it only needs one parameter passed to it, the user profile:

Finding source members with the same name

How would I find if a source member with the same name is found in more than one source file? If there is, which one was modified most recently? I am sure those are questions many of us have asked ourselves. How could we can make this easy for ourselves to get this information? Fortunately, Db2 for i has everything I need to do it.

I start with the SYSMEMBERSTAT View, it was introduced a couple of Technology Refreshes ago, IBM i 7.5 TR4 and 7.4 TR10, and it is used in place of SYSPARTITIONSTAT when I need information about members.

What are the columns I am interested in:

SDA is not available in IBM i 7.6

The announcement of IBM i 7.6 included notice that the following application development tool set commands were no longer available:

• STRSDA:  Start Screen Design Aid
• STRRLU:  Start Report Layout Utility
• STRAPF:  Start Advanced printer Function
• MRGFORMD:  Merge Form Description
• STRCGU:  Start Character Generator Utility
• CPYIGCSRT:  Copy DBCS Master Sort Table
• MRGFORMD:  Merge Form Description

The web page with this information is here.

In the past week there were rumors circulating in social media that this is not true, and it is still possible to use the STRSDA command in 7.6 .

Retrieve the SQL statements from multiple QM queries

The germ for this post came from a message I received:

Is there any way to retrieve sources for all QMQRY objects available inside a library in one go? My objective is, there are 100's of QMQRY objects(SQL type) inside a library but they don't have predefined source members. I need search for particular string inside all those SQL queries used inside the QMQRY objects.

I decided to work out a way I could do this.

I did not create "100's" of Query Management, QM, queries as what will work for two will work for many more too.

It is possible to retrieve the SQL statement from a QM query object by use of the Retrieve Query Management Query command, RTVQMQRYM. That command copies the retrieved statement into a source member.

Moving one person's spool files to a different output queue

I was asked if there was a simple way to check an output queue, and if there is a spool file of a certain name for one user, to move it to another output queue.

Fortunately this is not as complicated as it sounds as I can use a SQL Table function to retrieve a list of those spool files, and a scalar function to move the spool file.

In this example I am going to be the user, my profile is SIMON, and whenever I find a spool file QPQUPRFIL in the output queue MYOUTQ I want to move it to the output queue OUTQ2.

First I need to produce a list of eligible spool files. Here I can use the SPOOLED_FILE_INFO SQL Table function:

SQL table function to list all imports for ILE program or service program

This is one of the IBM i enhancements that was released in version 7.6, but not in 7.5 TR6. This new table function, PROGRAM_RESOLVED_IMPORTS, allows me to get a list of all the imports for an ILE program or service program.

This Table function has four parameters:

1. PROGRAM_LIBRARY:  Library that contains the ILE program or service program. "*LIBL" is not supported.
2. PROGRAM_NAME:  Name of the ILE program or service program.
3. OBJECT_TYPE:  *PGM for ILE program, *SRVPGM for ILE service program.
4. IGNORE_ERRORS:  Optional. NO when an error is encounter an error is returned. YES a warning is returned, this is the default.

This looks like:

How to identify flat files

The question was is there an easy way to identify "flat files" without having to use the DSPFD command. The answer, of course, is "Yes".

The questioner explained that a "flat file" was a file that was generated without the use of DDS or DDL. In other words, just with the Create Physical File command, CRTPF. For example:

CRTPF FILE(MYLIB/FLATFILE) RCDLEN(100)

The questioner was finding he could identify these "flat files" with the Display File Description command, DSPFD, like this:

DSPFD FILE(MYLIB/FLATFILE)

IBM makes it easier to delete old journal receivers

Old journal receivers can quickly become one of the big wasters of storage on any IBM i partition. I have written before how to identify receivers I think can be deleted. In IBM i 7.6 and 7.5 TR6 comes a new SQL procedure that makes it easy to delete the old receivers.

I need to define what I mean by "old receivers". An "old" journal receiver has been detached from its journal and saved. In my mind if it is detached and been saved, I can delete it. If I need the information contained within it, I can restore it.

I would not use the new procedure to look if I have old receivers, I would use the JOURNAL_RECEIVER_INFO View to get to the information I would want, before using the new procedure.

For example, if I want to find the ten oldest journal on my partition, I would use the following statement:

Simple way to check if a file exists in the IFS

Someone reached out to me asking for a straightforward way to check if a file exists in a particular folder in the IFS. They included their program, which, IMHO, was overly complicated. This will be another example of providing a simple, easy to understand, solution using SQL and RPG.

In my example I will be checking if a file exists, and the path for the file will be passed as a parameter from a RPG program. I also want to make the part that does the checking be a procedure. As one procedure can be called by multiple programs, as there could be other files in the future I want to check on.

I am going to create this procedure in a module that I will then bind into a RPG program. In the "real" world the procedure would be added to a service program, so that the logic within the procedure could be changed without having to recreate all the programs that use it.

Increment a number held in a character field

The title is simplistic for what this post is really about. The question that was asked was:

I have a character field in a DDS physical file that is 20 characters long. There can be various numbers in that field of any length. I want to retrieve the value from the field, increment it by one, and update the field in the file.

If all the "numbers" in the field in the file were 20 characters long it would be easy, but as the "numbers" can be of different lengths it makes the problem more interesting.

First I need a file for this 20 character field. I decided to call it TESTFILE and the DDS code for it is:

01 A UNIQUE 02 A R TESTFILER 03 A KEY 5A 04 A FIELD1 20A 05 A K KEY

SQL scalar function to break apart IFS path name

As part of the new new release, IBM i 7.6, and the latest Technology Refresh for IBM i 7.5, TR6, I can break apart an IFS path name into its parts:

• File extension
• File name
• File prefix (file name without the file extension)
• Path prefix (path without the file name)

This new SQL scalar function is called IFS_PATH, and it is found in the library SYSTOOLS.

Before I start with my examples I need to check if there are objects in the IFS I can use. This is a simple task using IFS_OBJECT_STATISTICS:

Selecting updated and deleted values when using SQL

Have you ever wanted to confirm that a SQL Update or Delete statement ran the way you expected?

In the new release, IBM i 7.6, I can do this using what IBM describes as:

1. Selecting updated values
2. Selecting deleted values

This was not added to the latest Technology Refresh for IBM i 7.5, TR6.

SELF default changes

While playing with the new release, IBM i 7.6, I noticed that the default for the SELF, SQL Error Logging Facility, SELFCODES global variable has changed.

When it first came out, IBM i 7.5 TR1 and 7.4 TR7, the default for the SELFCODES was null.

With IBM i 7.6 and 7.5 TR6 the default is '*NONE'.

Why the difference?

Both null and '*NONE' are acceptable as the default. The difference is that '*NONE' will stop SELF monitoring immediately. Null does not, only when new SQL 'jobs' start.

CREATE OR REPLACE VARIABLE SYSIBMADM.SELFCODES VARCHAR(256) DEFAULT '*NONE' ;

This is another good enhancement to SELF.

Report of the success of the nightly saves

My manager asked me if I could provide him with a report of all the libraries that were backed up as part of the night process job, NBACKUP. "You know what I mean: one line for each library" was his final statement to me.

I did want to browse a different job log for each day. I wanted to design something that was simple. I decided to use the HISTORY_LOG_INFO table function. I could select results using:

• Starting timestamp of 1 day (24 hours) ago
• I know the job name

I started with this SQL statement:

Quickest way to determine which day of the week today is

In a program I wrote recently I needed to return which day of the week today is. My first thought was to use SQL, as it would only be one line of code. But is that the fastest way to return today's day name?

I thought about writing my own RPG routine to calculate the day name, but decided to use Google to see if there was a better example. I found an article by Rafael Victoria-Pereira for MC Press from 2015 that gave what I think is an efficient way to do this, you can read his article here.

Using his example I refactored the RPG code up to 2025 standards and created this procedure:

LISTAGG scalar function can return distinct results

I did not realize it had been so long since I first wrote about the LISTAGG scalar function. I mentioned very briefly in that article something that became very useful to me just a short while ago.

The LISTAGG scalar function aggregates the results from a one or more rows into a single new column, separated by a character of my choice.

In my scenario I have a table, TESTTABLE, that contains a column of colors, COLOR:

01 SELECT COLOR,COUNT(*) "Count" 02 FROM TESTTABLE 03 GROUP BY COLOR