Ted Holt
-
Guru: SQL and QTEMP
January 4, 2021 Ted Holt
Hey, Ted.
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 -
Guru: Success Requires Many Teachers
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 -
Guru: SQL Writes CL
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 -
Guru: SQL and Expanding Subfiles
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.
- Every
-
Guru: Concerning The Stepping-On Of Feet
October 19, 2020 Ted Holt
It has come to my attention that once again I did not tell the truth, the whole truth, and nothing but the truth. In Three Suboptimal I/O Practices, I said that a simple SELECT INTO was preferable to a cursor that fetches one row. It turns out that there is at least one situation in which SELECT INTO will not serve the purpose, and one has no choice but to use a cursor that fetches one row.
The situation is this: the program must lock the fetched row for update. Despite its power and simplicity, SELECT INTO cannot lock …
Read more -
Guru: DISTINCT Can Hide A Performance Problem
September 28, 2020 Ted Holt
When I see the word DISTINCT in an SQL query, a little red flag goes up inside my head. Not literally, of course. But it does make me pause and scrutinize the query more closely. I have found that poorly designed queries sometimes include the word DISTINCT as a final act of redemption to forcibly return the proper result set.
The purpose of DISTINCT is to remove duplicate rows from a result set. As the DB2 for i SQL reference puts it:
The keyword DISTINCT is not considered an argument of the function, but rather a specification of an operation …
Read more -
Guru: SQL Can Read Program-described Data
September 21, 2020 Ted Holt
I thought I left program-described database files behind me in 1988. That’s when I left my last S/36 shop to begin working on the S/38. Well, I did, but not completely. From time to time I work on a system with program-described files, and even some externally described files have program-described fields. Fortunately — and I owe this to Scott Forstie — I have learned that SQL can read program-described data. Will wonders never cease?
You may be thinking, “This article doesn’t apply to me. Our files are externally described.” You may be right. Then again, you may be wrong. …
Read more -
Guru: Qualified Files – Underused and Unappreciated
September 14, 2020 Ted Holt
When IBM adds a new feature to the RPG compiler, they do so for a reason. That’s why I try to learn new techniques. I hope they’ll improve the quality of the source code I write. One relatively new feature that I do not see widely used is the qualified file. In the following paragraphs, I’d like to tell you why I like qualified files and how to use them.
To understand the need for qualified files, it may be good to begin with a brief lesson on the history of the RPG language. When RPG was originally developed, the …
Read more -
Guru: What Do People Have Against Timestamps?
August 3, 2020 Ted Holt
In my work I often see database tables and physical files with related date and time fields (columns). By related, I mean that the two fields together indicate a certain time on a certain date for a certain event. I have found these date/time pairs to be difficult to work with at times, so much so that I have come to prefer timestamps.
I wonder why database architects (I use the term loosely) specify separate date and time fields rather than a timestamp. Having done no scientific survey, I can only guess. I suspect that many tables date back …
Read more