Adding 1 month to the end of the month and always get the end of the next month

I was asked how to make sure if I added 1 month to the last day of this month I would get the end of next month. If I take a date from a month and add one month to it I get the end of the next month.

In RPG if I add one month to January 31 I get February 28, the last day of the next month.

wkDate = d'2022-01-31' + %months(1) ; dsply wkDate ; DSPLY 2022-02-28

When I add one month to February 28 I do not get the last day of March, I get March 28.

wkDate = d'2022-02-28' + %months(1) ; dsply wkDate ; DSPLY 2022-03-28

Using SQL LIKE with a Subselect

This is the second part of a two part story. In the first part I described how I had downloaded a list of libraries from an IFS file to a DDL table just using SQL. In this part of the story I want to check any of job descriptions on this IBM i partitions contain one of these libraries.

In the "real world" there were 58 libraries. In this example I am only using five. To check what these libraries are called I can using the following SQL statement over the file I built in part one:

SELECT * FROM OLDLIBS

The libraries I will be using are:

Copying data from IFS file using SQL, real life example

This is the first of a two part story. I had found a number of libraries on an IBM i partition from the 2000s and 2010s for applications that had been used, but had been replaced by the main ERP application. These libraries were just wasting disk space, as during the conversion to the ERP all of the historical information had been copied from them to the ERP. I wanted to delete these libraries.

The list of these libraries had been circulated to all of the interested parties to confirm that no-one was using any of them. I copied the list from the email to Notepad, and saved as the file name: old_libraries.txt.

I uploaded the text file to my IFS folder using ACS's "Integrated File System" tool.

Searching for empty IFS folders using SQL

I was asked how to make a list of empty IFS folders using SQL. My first thought was to go to the table function IFS_OBJECT_STATISTICS. By using this table function I can retrieve a list of, for examples, just folders or the objects in a particular folder.

My thought was to do this in two steps:

1. Make a list of all the folders
2. Generate a count of the objects in each folder

New columns added to SYSCOLUMNS2 SQL View

I need to keep reminding myself to use the SYSCOLUMNS2 SQL View rather that SYSCOLUMNS as IBM tell us that it gets the results faster than the old View. IBM keeps enhancing the new View, and as part of the latest Technology Refreshes, IBM i 7.4 TR5 and 7.3 TR11, they added 14 new columns.

These new columns are particularly useful when working with DDS files as some of these columns deal with field attributes that are not available in DDL Tables and Views.

Prior to these additions I would use the Display Field Description command, DSPFFD, to generate an output file for the files I desired. This meant that this was always a two-step process:

1. Create the output file
2. "Read" the data in the output file

Now I can get the information I want in just one step, by accessing the data directly from the SYSCOLUMNS2 View.

User index information by SQL

As part of the latest Technology Refreshes, IBM i 7.4 TR5 and 7.3 TR11, we received a SQL View and Table function to use to get information about User Indexes.

I do not recall ever using a User Index, but some of the ERP I have worked with through the years had them. The IBM documentation describes them as:

A user index is an object that allows search functions for data in the index and automatically sorts data based on the value of the data...
They have an object type of *USRIDX and a maximum size of 1 terabyte. They help streamline table searching, cross-referencing, and ordering of data.

Until these TRs I would need to use APIs, ILE C, or MI instructions to access a User Index and the information contained within. Now IBM has provided us with the following:

• USER_INDEX_INFO View
• USER_INDEX_ENTRIES Table function

Retrieving the source of SQL objects

GENERATE_SQL_OBJECTS is a SQL procedure I have used which I have found very useful, but have not written about. It allows me to recreate the SQL statement that was used to create a SQL object or objects into a source file member. I could change many of SQL objects using the ALTER statement. But, in my opinion, it is easier to change the statement in a source member. I make sure that the create statement is CREATE OR REPLACE, create the changed object and old one is replaced.

There are two things I consider minor inconveniences to using this SQL procedure:

1. I cannot pass the name of the object to the procedure. All of the objects I wish to retrieve the create statement for must be placed in a DDL Table, that procedure then "reads".
2. The source member that is generated contains the create statements for all of the objects listed in the above Table.

I can use this with most SQL object types. In this example I am only using it with a few I have mentioned in previous posts.

Delaying a CL program by a fraction of a second with USLEEP

I have already written about how to delay a RPG program by less than a second by using the USLEEP procedure. Now I need to do the same in a CL program.

As we are in 2021 I hope that all of your CL source members are all the type CLLE. The program I am going to show must be compiled using the Create Bound CL program command, CRTBNDCL, not using the Create CL program command, CRTCLPGM, which is the default for type CLP.

USLEEP is a UNIX type API. This means that it is case sensitive. When I use it in a program I must use usleep, and not USLEEP or Usleep. I am just going to refer to it in the text of this post as USLEEP as it is easier for you to see its name amongst the rest of the text.

Creating an auto-scrolling subfile

The idea for this post came from a question that was asked on Facebook. Someone wanted to create a subfile that would be projected onto a screen at an event. The subfile would scroll to the next screen of results every so many seconds without the need for someone to press a key on the keyboard.

I was surprised by the replies to the question, some said it was not possible, others gave over complicated examples of how they thought it could be achieved. I knew that to do this was simple, and all the information needed to do this exists in various posts on this website.

I decided to write a program to do what was asked before. I decided to make this a little more interesting:

New SQL View gives DSPFD information

I have wanted to have a way to get to the same information via SQL that I can by using the Display File Description command, DSPFD. This absence has often led me to having to use the DSPFD command to create an output file, that I would then use SQL to gather the results I desired.

My wishes were answered in the Fall 2021 Technology Refreshes, IBM i 7.4 TR5 and IBM i 7.3 TR11, with the introduction of a new SQL view: SYSFILES.

Unlike the DSPFD command SYSFILES only contains data about what I would call "data files", what the DSPFD calls "*PF" and "*LF". Source files are included, but the column FILE_TYPE allows me to differentiate between source and data files.

The simplest Select statement to use this view is: