Using derived columns to sort files

My employer uses "descriptive" part numbers for the parts they manufacturer. Without going into too much information, "descriptive" part numbers contain information about the item within the part number, it could contain characters that describe the material used to make it and/or characters to denote its end use. I am often asked to create a report for certain collections of parts. These "collections" are identified by a range of characters in certain positions of the part number, and the sort order desired may not be in a simple sort order, I will explain what I mean by that later.

The "descriptive" part numbers I will be using in these example consists of three parts:

REGEXP like added to SQL

I can remember many years ago having a conversation with a non-AS400 (yes, it was that long ago) person about the programming environment we worked with. He scoffed at the AS400 stating he could never work on it as it did not support Regular Expressions. I am not going to describe in too much detail what Regular Expressions, sometimes called regex or regexp, are as others have done a good job doing that. To give a very simple explanation: it is a sequence of characters that define a search pattern, which is then used to find or find and replace parts of strings. If you are interested in learning more check the Wikipedia page about regular expressions here.

Within the Db2 for i PTFs that accompanied IBM i TR2 and 7.2 TR6 a number of regular expressions functions were introduced to SQL. I am not going to describe them all in one post, as it would be too long, as I feel they merit their own posts. To start this post is about the REGEXP_LIKE. I am just going to give a simple overview of how to use this, if you want more details or want to learn how to make complex statements I suggest you visit the page on IBM's KnowledgeCenter, link at the bottom of this post, or use your favorite search engine to search for "REGEXP_LIKE". The posts about REGEXP_LIKE for Oracle's PL/SQL I found helpful.

Finding the minimum value across several columns

Sometimes I find something that I think is cool, it may be something small, like this, but I feel it is worth sharing.

I was asked about how to find the minimum value from multiple columns. SQL offers us the MIN function for returning the smallest value from a column. I can use that to get the smallest value from multiple columns, and the smallest value from all of those columns.

I have a file with three fields containing the following numbers:

Sometimes a List API is better than the SQL View

Sometimes it is just "better" to use a list API than it is to use the SQL View that "replaced" it. This week has given me an example of this. I was asked to create a program that would allow users to email themselves reports. I knew I would have to present the users with a list of spool files from which they could select the one they would want to email.

"No problem", I thought, "I will just use the Output Queue Entries SQL View."

I created a View over the OUTPUT_QUEUE_ENTIRES View, and then created a program using a multiple row fetch to get more than one row from the View. In the "test" IBM i partition the program ran a bit slow the first time I ran it, then ran faster each time after as the DB2 query engine created a temporary access path for me. Being a "test" partition there are not many output queues or spool files when compared to the "live" partition. I moved the objects I created to "live" and called the program there. It took more than 20 minutes to load the subfile with the list of user's spool files. With several hundred users and a very large number of spool files (many thousands, does anyone ever delete spool files?) in the "live" partition it was searching through all of them to find the few spool files belonging to me. The time taken made this method unacceptable. Back to the "drawing board".

We are not impostors

Last week I found a wonderful article by Roger Pence in IBM Systems magazine.

It started with the title "Your are not an impostor", and continued with the subheading "Self-taught RPG programmers bring valuable skills to enterprise programming teams". As one of those self-taught programmers it caught my eye, and he made some good points about what would make me feel like an impostor, and what I can do to overcome it. It confirmed my opinion that there is a place in every organization for the self-taught and motivated programmers, alongside the formally educated graduates.

You can read the article on IBM Systems online magazine here.

If, like me, that site does not always load, I have placed a copy here (PDF file).

Alas, when I tried to go to the URL given for "The Impostor's Handbook: A primer for self-taught programmers", bit.ly/2ivK5lt, it went to a 404 page not found error page. If you have the correct URL for this page I would be grateful if you would post it in the comments section below.

Resetting the value of a variable

One of my colleagues was reading the post about initializing variables when defining them and asked a question that I had not considered when writing that post.

"What if I change the value in the variable," he asked, "can I change it back to its original value?"

Fortunately there is in RPG an operation code that will do this. The RESET operation resets the value of any variable or record format back to the value it was at the initialization phase of the program. It is not new operation, I have used it for as long as I have programmed on the AS400 - IBM i.

Types of saves

I have Steve Pitcher to thank for sharing this image so that I could post it here. It is from IBM's KnowledgeCenter showing the different types of saves that can be performed using the various save commands, on the right side, and the options on the SAVE menu, on the left.

Checking Software License expiration dates using SQL

Included in the latest Technical Refreshes for IBM i 7.3 and 7.2 is a SQL View and Procedure to help us monitor the licenses of the software on our IBM i.

IBM uses Software License Keys with certain IBM i products to ensure that I have paid the appropriate license fees to use these products on an IBM i. I know of several third party IBM i software vendors who also use this method to stop the unauthorized copying of their software to unlicensed servers. It is all standard stuff to prevent the distribution of unauthorized software.