Easy way to convert date to words using SQL

The idea for this post came from a meeting of the programming managers at work. My programming nemesis, the "I can do everything you do in RPGIV just as well using RPGIII" guy, was bragging that no-one could write a better way of changing a date into words than his old RPG38 program.

"I can do what your program does in just a few lines of code," I explained. He disputed that I could, and the challenge was on.

His old program is passed a date, as a number, and returns:

1. Day number of the week, where 1 = Sunday, 2 = Monday, etc.
2. Day of the week
3. Name of the month
4. The date in words, for example "THURSDAY JANUARY 1, 2017"

Redpaper: Tools and Solutions for Modernizing, updated

Yesterday an updated version of the Tools and Solutions for Modernizing Your IBM i Applications Redpaper was released. This Redpaper, which was first published in 2014, describes the tools that are available from Independent Software Vendors, ISV, and Business Partners, BP, to modernize IBM i applications and databases.

It has sections about:

1. Mobile, web, and client solutions
2. Database modernization tools
3. Security
4. Tools for understanding and modernizing RPG and COBOL

While I usually avoid mentioning ISV and BP software in this blog, sometimes it is interesting to see what is "out there".

You can download the latest version of this Redpaper, as a PDF, here.

Searching the History log using SQL

I am sure I am not the only person who has received a message, email, or telephone call informing me that there was an error message that happened earlier. When I reply asking for more details it is not uncommon to be told: "I don't know what the error was, I just replied with a C". This, of course, tells me nothing useful to be able to make a diagnosis what and where the error happened. Often it drives me to the History log which I have to search through looking for an error message. Narrowing the possible time frame makes the searching easier, but if the best the message sender can tell me is "earlier this afternoon" or "during the day end run" I am left with a large amount of log data to search through.

IBM has come to my rescue with a DB2 of i (SQL) table function that allows me to search the History log and select the data I want to see. I am not sure if this is part of the IBM i 7.3 TR1 and 7.2 TR5 updates. You can search to see if it is present on your IBM i using the PTF information View, PTF_INFO, to look for relevant PTF:

MAGIC starts in the eastern USA

Yesterday I came across a new IBM i user group with the wonderful name of MAGIC. I reached out to them for more information and received a wonderful reply from Laura Hamway. Her explanation of what the group is and what they want to achieve, was so good I am just going to include it here.

Mid-Atlantic Group of IBM i Collaborators (MAGIC) was formed to help IBM i Users in this region (VA, NC, DE, MD and PA) to network and to facilitate ongoing education. The group was formed to support this region but anyone can join. We have all the frame work done, now we are ready to get started in 2017!

ON-EXIT provides code executed at end of procedures

As part of the IBM i 7.3 TR1 and 7.2 TR5 enhancements a new RPG operation code, ON-EXIT, was added. The purpose of this operation code is to give a section of code that is executed whenever a procedure ends, with or without an error. Alas, when an error occurs in the procedure it is not possible to "correct" or prevent the error being returned to the calling program or procedure using the ON-EXIT section. But it does allow for the programmer to add code to, perhaps, flag the error or to perform various clean up tasks before the procedure ends.

The ON-EXIT section must occur at the end of the procedure. It is started by the ON-EXIT operation code, and is ended with the END-PROC operation code that flags the end of the procedure.

Who remembers these? RPG coding sheets

When I posted pictures of the IBM print charts I had found, Kevin Adler sent me pictures of some of the RPG history he has, RPG coding sheets.

Looking closely at them I think they may have been sheets used in the days of punch cards.

When I asked Kevin how he came to possess them he replied: "I got them from a former team leader before he retired."

Discovering how much personal storage space remains

This post is based upon something I made for myself, and after discussing it with some friends they said it would be worthy of becoming a post as others have the same need.

All of the code shown on this blog is written on servers generously provided by the IBM i hosting provider RZKH. Their hosting package gives me a fixed amount of storage, disk space, which I can use. While I am a careful user, who deletes all objects when I have finished with them and regularly clears my output queue, I want an easy way to see the following:

1. How much storage space was I allocated when my profile was created?
2. How much space have I used?
3. How much space do I have left I can use?
4. What percentage of my allocated space have I used? If I reach a certain percentage I know I need to start deleting and clearing immediately

Modern RPG Redbook updated version now available

A new draft of the IBM Redbook "Who Knew You Could Do That with RPG IV? Modern RPG for the Modern Programmer", completed October 20 2016, has been made available. I recognize many of the authors as people I respect as highly knowledgeable in the use of modern RPG.

The chapters cover many items which should interest anyone using, or wanting to learn, modern RPG:

Validation trigger in RPG

When I wrote about creating a validation trigger in SQL several people asked me to give an example using RPG. Never being one to turn down a challenge this post is going to show how to do the same as my SQL example, but completely in RPG.

I am not going to go into the reasons why you might want to consider using validation triggers, as I gave my opinion in the previous post.

I started my previous example giving an example of a SQL DDL table, in this example I am going to use a DDS physical file. FILE1 has three fields:

Who remembers these? Print charts

I could not resist posting pictures of a find I made in a box at home, a pad of IBM 132/10/8 print charts. The numbers denote that the chart is 132 characters wide, with 10 characters per inch and 8 lines per inch. These were used to assist programmers designing reports, and make it easier to translate the desired layout to Output specifications.

My wife had acquired the pad of charts in a previous job many years ago, and had put it in a "safe place" in case she needed them in the future. The charts had been stuffed into box of other old computer items during one of our moves, only to be rediscovered yesterday during one of those "I wonder what's in that box?" moments.

Creating a program to show jobs in message wait

I do get many requests asking for whole example programs, especially ones with a subfile, rather than just bits and pieces of code that I usually give in these examples. With this in mind this post brings a number of things I have written about before together into one program, which I thought would be an interesting refresher. So here goes...

I was asked to write a program to only show jobs that are in message wait status, and I want to add the ability give the user some options that would allow the user to analyze the error and reply to it.

To summarize what I need to do:

Coping with null with derived columns

I was assisting a colleague creating a SQL View when I encountered something I had not thought of before. He wanted to create a view with a derived column based on a column in the "right" table of a LEFT OUTER JOIN. Everything looked fine until the LEFT OUTER JOIN did not find a row in the "right" table. We could easily stop a null appearing for the columns from the "right" table, but what about the derived column?

Null is a concept that many people struggle with. I often have had to explain what null is and why it is different from blank or zero to older RPG programmers, who always have a confused or dismissive look in their faces. Users just don't understand the concept. This is why when I create output I always put some value in the column that comes up null.