Ted Holt is the senior technical editor at The Four Hundred and editor of the former Four Hundred Guru newsletter at Guild Companies. Holt is Senior Software Developer with Profound Logic, a maker of application development tools for the IBM i platform, and contributes to the development of new and existing products with a team that includes fellow IBM i luminaries Scott Klement and Brian May. In addition to developing products, Holt supports Profound Logic with customer training and technical documentation.
March 29, 2021 Ted Holt
A stub program is a program that does nothing but stand in as a place holder for a real program, which may or may not exist yet. I have used them for years to help me test program changes. There are also stub subroutines, stub subprocedures, etc. What I want to talk about today is how to use a similar concept for SQL queries.
You can use your favorite search engine to learn about stubs, but I’ll give you an example to increase the chances that you’ll know what I’m talking about. Let’s say that I am modifying a CL …Read more
March 22, 2021 Ted Holt
I’m a fairly decent typist, and chances are you are, too. A person doesn’t sit at a keyboard for decades and not improve. At the same time, I don’t get paid to type, and I do everything I can to reduce the number of keystrokes I have to produce while carrying out the duties of the job.
If you find yourself keying the same old long commands over and over — and who doesn’t? — I’ve got a tip for you. I have an easy way to reduce long commands to just a few keystrokes. Maybe it will save you …Read more
March 8, 2021 Ted Holt
I got egg on my face again. I told a couple of colleagues that they could use the SELECT INTO statement to load multiple rows into an array data structure in an RPG program. Boy, was I wrong! I had confused SELECT INTO with the FETCH statement, of course, which retrieves data over which a cursor has been declared.
But the matter continued to nag me. I much like the simplicity of SELECT INTO. There’s no cursor to declare, open, fetch from and close, the same reason I like the FOR loop in SQL PL. It seemed (and continues …Read more
February 15, 2021 Ted Holt
When is a boy not a boy? When he’s abed! When is a door not a door? When it’s ajar! When is an outer join not an outer join? (Sorry, no dad joke here. Three dad jokes in one paragraph would have been too many, don’t you agree?) Let me answer that last question.
In my work I often see outer joins that are not really outer joins, but inner joins. Oh, based on what I’ve heard from IBM, the query engine may treat them as outer joins, but the result set is the same as that produced by an …Read more
February 8, 2021 Ted Holt
Is redundancy good or bad? I say it depends. According to Nassim Nicholas Taleb, “Redundancy is ambiguous because it seems like a waste if nothing unusual happens. Except that something unusual happens — usually.” I have seen some unusual behavior when joining database tables, but try as I might, I can’t figure out what that unusual behavior depends on. Let me show you what I mean.
First, we need some data for illustration. Let’s say that our company uses an ERP system that was designed for make-to-stock manufacturing. What the factory builds goes into the warehouse, and customer orders are …Read more
February 1, 2021 Ted Holt
A strange thing happened to me recently. I was writing a new program and like a good programmer, was not reinventing the wheel. I was calling a utility program that calculated the values I needed. However, this utility program, which had always worked correctly, was giving me invalid data. How is it possible that a program can work properly for a long time and suddenly go bad?
The answer to this question was ably answered by Rick Cook, who wrote “Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the Universe trying …Read more
January 4, 2021 Ted Holt
For many years IBM i developers, operators, and others have taken advantage of an operating system feature called the QTEMP library. Through the years you have referenced it various times in IT Jungle as a useful feature of IBM i. I recently read an article in which a respected IBM expert from the Rochester lab services team told people not to use the QTEMP library when working in SQL. It would be very interesting if you could dive into this topic and explain when/if there is still a time and place for QTEMP. I think a lot of …Read more
December 14, 2020 Ted Holt
I am not a “super programmer”- if such a thing even exists. I am not a genius, nor am I a guru. I’m not an expert. Whatever success I have had as a computer programmer these years, I attribute to a very few causes. I would like to end this year by writing about two of them.
Number 1: I have learned, often the hard way, how to keep myself out of trouble. My code is dull and bland and boring, and I like it that way. I strive to make my code straightforward, honest, and so easy to understand …Read more
November 30, 2020 Ted Holt
SQL is the one tool I cannot work without. Take it away from me and I’ll start driving a truck for a living. Naturally I’m eager to find more ways to make SQL work for me. Today I’d like to share how I recently used SQL to write a huge CL command for me. This is a technique that’s good to know.
My challenge was to copy all of the several hundred physical data files in a library to a save file so that those files could be loaded onto another IBM i system. The Save Library (SAVLIB) command was …Read more
October 26, 2020 Ted Holt
I’ve heard it said on more than one occasion that SQL does not work as well as record-level access (RLA) when loading subfiles. I understand why people feel that way. They’re usually thinking about repositioning to a key value, and there is no SETLL (Set Lower Limit) op code in an SQL cursor.
Yet I think SQL is better, and today I’d like to share one case that I think presents a good illustration. I had in mind the expanding subfile. There’s a parallel between the two. Consider:
- Every time you clear the subfile, you open a new cursor.