Wednesday, April 26, 2017

Using SQL to aggregate columns into one returned result

aggregating multiple values into one result set using sql

There are times when I have want an easy way to put values from a file, or table, "horizontally" into one column rather than vertically with more than one row.

"Vertical" "Horizontal"
TYPE        THING
ANIMAL     ELK
ANIMAL     HAWK
ANIMAL     MEERKAT
ANIMAL     ORCA
TYPE       THINGS
ANIMAL     ELK, HAWK, MEERKAT, ORCA, SEA L
FRUIT      APPLE, FUJI, BLACKBERRY, GRAPEF
VEGETABLE  SQUASH

Wednesday, April 19, 2017

Min and Max built in functions added to RPG

using the rpg min and max built in functions

A pair of introductions to RPG as part of IBM i 7.3 TR2, and 7.2 TR6, are the minimum and maximum built in functions, %MIN and %MAX. The maximum built in function returns the greatest value in a list of variables and/or values. The minimum returns the lowest value from a list of variables and/or values. Otherwise the way they are defined and the rules governing them are the same.

To use these functions there must be at least two values, and they must be of the same type. What I mean is that all of the values must be some type of numeric, character, date, time, or timestamp. I cannot mix types in the values. Pointers cannot be used.

They are so simple I can go straight to examples of using them. Let me start with a variety of numeric data types:

Thursday, April 13, 2017

Change to SQLRPGLE compile listings

type of rpg used in sqlrpgle compile listing changed to free format

Something I have noticed since the installation of IBM i 7.3 TR2 and 7.2 TR6 on the partitions I use, is a change to the compile listings of SQLRPGLE programs.

The process to create an object from a SQLRPGLE source member is a multi-step process:

  1. The syntax of the SQL within the source member is checked to ensure it is valid.

Wednesday, April 12, 2017

Nested data structures in RPG

nested data structures with other data structures

I am grateful to the folks at RZKH for applying the PTFs for the latest TR for IBM i 7.3, TR2. I started looking at the new features and functions added to RPG as part of this TR and decided to write about those first (Note: Same functions are included in 7.2 TR6 too). There were three additions, the one that first caught my eye was the ability to nest data structures.

Nested data structures are only available when defining data structures in free format RPG. If you are still using fixed format definitions this is another sign from IBM that it is time to move to free format RPG.

Inserting a nested data structure is pretty much as I would have expected, see below.

Tuesday, April 11, 2017

End of support for IBM i 7.1 announced

Steve Will, chief architect for the IBM i operating system, announced in a tweet today that the end of support, EOS, for release 7.1 will be on April 30, 2018.

Tweet from Steve Will: One of the FAQs I receive now has an official answer: IBMi 7.1 EOS 30 April 2018

Wednesday, April 5, 2017

How to write data to a multiple member file

handle multiple member files in rpg

Often I get the same question asked by several people, this time I have been asked the same question five times just this week, and I have been told that this is a question people have been asked in interviews:

How do you write data to different members in the same file using RPGLE?
The file has ten members, and you need to write data to the fifth and sixth members in one RPGLE program.

Tuesday, April 4, 2017

Saint Isidore of Seville day

Happy St Isidore of Seville day!

Saint Isidore is the Catholic patron saint for computers, programmers, and the internet. When things go wrong, very badly wrong, and everything else is failing it is always nice to know there is someone we can call on for divine intervention.

I am sure other denominations and faiths of saints, deities, etc we can ask for help from? If you know of any please post their details in the comment section below.

Monday, April 3, 2017

IBM i performance FAQ updated

ibmi performance faq updated april 3 2017

An updated version IBM's IBM I on Power – Performance FAQ was published today, called a "Spring refresh" by Steve Will the IBM i chief architect.

The What's new in the latest version?, section 1.3 on page 11, lists eight new questions added to the document.

If you are just a programmer type, not dealing with the finer points of tuning a Power server, there are sections to do with optimizing Cobol and RPG too:

  • Section 8.14 p57: How do I tune RPG/COBOL application performance and native I/O file access?
  • Section 9 p62-68: Database performance
  • Section 10 p69-70: RPG/Cobol native I/O

Good stuff to read, learn, and apply to your work.

The document is a PDF file and can be found on IBM's web site here.

Wednesday, March 29, 2017

Command syntax validation API

using qcmdchk and qmhrcvpm 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.

Wednesday, March 22, 2017

Determine the end of month using CL

get end of month date only 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.