Command syntax validation API

Like many posts in this blog, this started off as a simple explanation of one API, and quickly became a more complex scenario giving more useful information to you, the readers of this blog. It all started when I wrote about using the QCMDEXC SQL procedure. I received a message asking me why I had not used the QCMDCHK, Check Command Syntax, API before using QCMDEXC. This is a good point: How can I check if a CL string being received is valid before I pass it to either the QCMDEXC SQL function or API? And if it is not valid how can I return a useful error message?

When writing the example RPG code for this post I found that the most interesting information QCMDCHK generated about any error was written to the program message queue. This is information I want, so to get these diagnostic messages from the program message queue I would need to use another API, the Receive Program Message API, QMHRCVPM.

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: