Determine the end of month using CL

The idea for this post came from several people who asked me the same question: "How to find the date of the end of the month? only using CL". When I quizzed them they explained that this was asked during an interview. If anyone asks me for help I always ask them how they would do it, and they all had pretty much the same solution, but struggled with how to determine if February should have 28 or 29 days. They asked how I would have done it. I came up with two solutions that I will share here.

The first version is very similar to what they can come up with. In this example the date is in *DMY format.

System, Partition, and Processor pool information all in one place

I am the first admit I do not know everything about IBM i as, in my opinion, there is just too much to know. Therefore, I am always grateful when people send me things they have found as there are many times they are new to me too. I have Laurent Gomes to thank for bringing to my attention the API QLZARCAPI.

I find it interesting that I cannot find any mention of it IBM's KnowledgeCenter, and only a passing reference in the documentation for the QMGTOOLS. What this API does is to display the system, partition, and processor pool information for the current IBM i partition.

TR2 and TR6 released today

Today is the day that the new Technology Refreshes, TRs, become available for IBM i 7.3 and 7.2. You can see what is included in them in an earlier post I wrote about 7.3 TR2 and 7.2 TR6.

TRs are available as PTFs that are downloaded from IBM's Support site:

New PowerSystems S812 out today

The latest of the PowerSystems server is out today. The S812 is the lowest end Power8 server, for up to 25 users. Alas, it is an either IBM i or AIX scenario as the server can only host one partition. It is in P05 tier and will be marketed to new small size customers looking for a robust server, and as a replacement for the smaller Power6 and Power7 servers.

You will find more information about this new PowerSystems server:

Interview with IBM Systems Magazine

About a month ago I was contacted by Paul Tuohy and asked if I would consider being interviewed about this blog, RPGPGM.COM, for his iTalk with Tuohy podcast for the IBM Systems Magazine. I was very flattered that he considered me worthy, as he has interviewed members of IBM's IBM i and PowerSystems teams, and many of the IBM Champions.

Yesterday the interview was published on IBM Systems Magazine's website, with the title Simon Hutchinson on RPG Code and RPGPGM.com, in both text and audio. You have all read how I write, now you can hear what I sound like.

I just want to thank Paul for making the interview so comfortable, I feel I could have continued talking for a lot longer than the allotted time.

Difference between UNION and JOIN

After last week's post about using a Select statement with an UNION several people have asked me to clarify the differences between an Union and a Join.

I think using some simple graphics makes it a whole lot easier to explain. I have two files:

File 1

File 2

New in DB2 for i in forthcoming TRs from COMMON

Last Tuesday (March 7, 2017) the COMMON user group posted a video called What new in DB2 for i, presented by IBM's Scott Forstie, to their website.

In this 40 minute video Scott discuss the enhancements to DB2 for i (SQL) that are being delivered at part of IBM i 7.2 TR6 and 7.3 TR2, at the end of this month.

Fortunately this video is available to the general public on COMMON's website here.

Using Union to combine data from two files into one View

When, in the ERP application my employer uses, an order is closed and posted to General Ledger the order's data is copied from the live file to the history file, and then deleted from the live file. This becomes an inconvenience when I am asked to produce a list of orders for a customer over a date range; the eligible data could be in both files. I have always wanted a way to link the live and history files together so that I can get the data from one place in the order I want.

Like most IBM i developers I want to use SQL to get data rather than use RPG's data access operation codes. I want to link these files together using SQL as I can get large chunks of data in one SQL operation faster than I can in RPG. Fortunately there is something in SQL to give me what I need, the UNION clause, that will allow me to join two or more selects statements together.

Using Get Diagnostic for SQL errors

I have already written about using DB2 for i's (SQL) GET DIAGNOSTICS to get the number of rows changed by a SQL statement. I can use the GET DIAGNOSTICS statement to get a lot more information about the SQL statement that has just occurred. In fact there are approximately 100 different types of information that can be retrieved using this statement, which is too much for one post. Therefore, I have decided to concentrate upon those keywords I think would be useful for diagnosing errors.

I have identified seven of keywords that I would find useful when trying to determine the cause of an error:

PTF cover letters for 7.3 TR2 and 7.2 TR6

The cover letters for the PTFs for IBM i 7.3 TR2 and 7.2 TR6 were released onto IBM's Support site yesterday. You can find them at:

• IBM i 7.3 TR2 PTF cover letter
• IBM i 7.2 TR6 PTF cover letter

The PTFs will be available for download on March 17, 2017.

For the details about what will be in these Technical Refreshes see IBM i 7.3 TR2 and 7.2 TR6 announced, which gives you an overview and has links to all the information from IBM.

Why I should be using the QCMDEXC SQL procedure

My favorite API has to be QCMDEXC. I think it was the first API I used when I when I was making the transition from RPGII to RPGIII many, many years ago. I use it so often I think half the programs I write have it in them. QCMDEXC allows me to execute any CL command that does not return any values.

For several releases there has been a SQL procedure QCMDEXC, which does the same as the API. Then in IBM i 7.1 TR7 there was an update to the SQL procedure. You will find that I have used it in several examples in prior posts, but I have not given it its own post until now. I have to confess that I am so accustomed to using the API program version of QCMDEXC, I keep forgetting to use the SQL procedure.

Using API to test Help Panels

After publishing the post about how to create help text for display files using UIM several people messaged me that there is an IBM i API that can be used to test the help, without needing to add code to a display file. I have to admit I was unaware of the API, and am grateful to those who messaged me.

I decided to create a program to call the Display Help, QUHDSPH, API that way I can have a simple user interface to use, I only need to enter the variables I want and leave the rest with there defaults. I am going to show a very simple example here to illustrate how QUHDSPH can be used. As this is a simple example program a lot of the code I would add for myself, for validation etc. is not present.

For simplicity I am using a display file to enter the variables for the help module I want to see. There are other ways I could have done this, perhaps using a command instead, but I want this to be K.I.S.S compliant.