Guru Odds and Ends: Readers Respond

April 30, 2018 Ted Holt

I appreciate the comments and feedback that readers have contributed. Some of you sent it my way via the Contact page at IT Jungle. Others emailed me directly. Yet others left comments at the end of the articles themselves. I am always grateful for your knowledge and wisdom, however I receive it.

Knowing that you are very busy and don’t have time to revisit the articles we have published, I’ve collected some of that feedback for your edification. Please feel free to add to the body of knowledge.

Had to chuckle when I read Sorting Options For CPYTOIMPF, Ted. Your “wonderful utility” is my bête noire! I hate it with a passion. It is the reason why the very first Open Access handler I ever wrote was to produce CSVs directly — so I could avoid it!

— Jon

Bête noire? À chacun son goût!

I had many interactive programs using GOTO to control program navigation (I supported a help screen and many command key functions) and finally wrote a program to strip out the GOTOs. IMO, the resulting programs are harder to follow unless your source is carefully and consistently indented, which means you’re using RDi. (And if not, why?) If you use GOTO for calculation loops, you’re not a programmer or even a good coder – you’re a very bad coder.

I believe a “proper program” has a single entry point and a single exit point. So, peppering your code with RETURNs may be convenient but it’s not a best practice. Finally, IBM has addressed the issue!

My solution has been to put my exit code in a subroutine within the procedure. It appears to work fine.

— Reeve

Reeve wasn’t the only one to suggest putting an exit subroutine in a subprocedure. Good idea, but obsolete now! Here’s another one:

I, too, am looking forward to using ON-EXIT. I was intrigued with your “last legitimate use of GOTO” concept. I know that it’s water under the bridge, but I would have gone with a “the last legitimate use of EXSR” concept. The subroutine to that procedure would have all the local variables available to it and the RETURN could be performed in it. It could have all been written in free format.

I remember when a new release came out, it was like Christmas Day. Opening the box and going straight to the RPG manual, we couldn’t wait to start using the new op codes and features. Our software vendor still generates code at 6.1, so I feel like I’m a bit behind because I’m not actively using full free format. There’s a surfer term for a person who acts and dresses like a surfer, but doesn’t surf. It’s a hodad (hoe-dad). I feel like an ILE hodad at times.

— Matt

Thanks for writing The Three Ways To Insert. I had never seen the use of a data structure in an update statement. I will add it to my tool box and make sure to use it.

— Ken

Why the seemingly random assignment of the ID column for the last three rows of your sample table? Wouldn’t this typically be auto-generated?

— Andy

It depends on requirements, Andy. If the ID is a meaningless value, such as a surrogate key, then you could have the computer generate it. But if it is an identifier that the corporate office assigns, then you must specify the values.

In this imaginary scenario, we can pretend that corporate has selected those warehouse ID’s for historical reasons. That is, those are the ID numbers that everyone who works in those warehouses is used to, and also the IDs used as foreign keys in the data that comes from those systems. I thought this example was less complicated with assigned IDs than it would have been with generated ones.

The easiest way to Remove Unwanted Trailing Blanks With LPEX — type a blank in the source and press CTRL+S.

–– Peder

Thanks for that information, Peder. Now we have two ways to get rid of those annoying blanks.

Regarding Faster Fetching, Revisited: When I first started using SQL embedded in RPG code, I was not a big fan of reading a record at a time. So I quickly learned about fetching multi-rows. Not only is it faster, it seems much easier to process. I usually do the fetch into a multiple-occurrence data structure with an eternally-described data structure when I need most of the data in a file. This makes coding easier for me. I will keep using this tried and proven method.

— Al

I don’t think using a MODS is a bad way or a wrong way. The data structure array is newer in RPG, and I think the syntax is easier. But if I understand correctly, one way is as good as another.

Regarding SQL Facts of UNION and ORDER BY: For Fact 7, you can use the union as a sub-query to allow the use of an expression for the ordering. An extra layer, but it gives you the result you are looking for.

— S Sims

select * from 
   (select . . . 
    union all
    select . . . ) as b
order by
   case when state = ‘MN’
      then 0 else 1 end

Thanks, S Sims. You’re right, and I didn’t think of that technique when I was writing the article. I just wanted to point out a place where expressions would not work. But I’m glad you mentioned it, because that was the technique I needed for a project I was working on at the moment.

For more ideas on this topic, see the comments of illustrious thinkers Rob Berendt and Birgitta Hauser at the end of SQL Facts Of UNION And ORDER BY, Take Two.

In response to SQL Table Functions Can Do Non-function Things, readers were kind to point out that I had reinvented the wheel yet again.

Thanks for the kind words. Building these services into DB2 for i is fun for us because we get a lot of warm feedback from clients. For the topic of library list, you could also consider using QSYS2.LIBRARY_LIST_INFO. We even return the full SQL schema name, should it exist.

https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_73/rzajq/rzajqviewliblinfo.htm

— Scott Forstie, IBM

To see my library list from Run SQL Scripts I do this:

SELECT * FROM QSYS2.LIBLIST

–Rusty

LIBLIST is an alternate name for LIBRARY_LIST_INFO.

It’s good that I wrote this utility. I work on a lot of systems, and soon came across two that did not have LIBRARY_LIST_INFO. All’s well that ends well.

Readers left two good comments in response to Susan Gantner’s article, Confessions Of A Recovering RDi Outline Addict.

I use the Fast View a lot as well, but when I find I am using the outline more frequently than normal I pull it out (I think the term for it is Detach) and stick it on my second monitor. Thanks for the tip on hiding the unreferenced definitions! I didn’t know that one and it will save a lot of time!

— Steve

I, too, love Outline view, and have spent a lot of time working out how to get the most out of it. F3 for Open Declaration is a particular favourite so I am glad you mentioned it – but did you know that there is another, often-faster way to invoke it than F3 or right-click menu? If you hold Ctrl and then mouse-over the name, the name becomes a hyperlink. A left-click without releasing Ctrl will then Open Declaration. Annoyingly Ctrl+Left-Click on its own won’t do it, there must be mouse movement after holding Ctrl and before left-clicking.

— Jamie.

That’s enough for today. I’ll cover more feedback and follow up some other time.

Thanks to all who contributed. Your comments improve these articles, and that improves the lives of your fellow readers.

RELATED STORIES

Sorting Options For CPYTOIMPF

Eliminate The Legitimate Use Of GOTO, Take Two

The Three Ways To Insert

Remove Unwanted Trailing Blanks With LPEX

Faster Fetching, Revisited

SQL Facts of UNION and ORDER BY

SQL Facts Of UNION And ORDER BY, Take Two

SQL Table Functions Can Do Non-function Things

Confessions Of A Recovering RDi Outline Addict