Ted Holt
-
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 -
Guru: Graphical Debugging Through ACS
June 8, 2020 Ted Holt
ACS (IBM i Access Client Solutions) keeps getting better and better. I can’t stay up-to-date with the latest releases of all the software I use, but I do everything I can to keep up-to-date with ACS. One of the handiest of the relatively most recent releases is the ability to access IBM i Debugger through Run SQL Scripts. If you haven’t tried IBM i Debugger, you’re missing out.
IBM i Debugger (a.k.a. System Debugger) is part of the IBM Toolkit for Java. It’s graphical, and it makes the green-screen debugger (STRDBG) look like something built by cavemen. To show you …
Read more -
Guru: A Simple, Effective Way To Compare Data Using SQL
April 20, 2020 Ted Holt
One thing I have learned over the years is that no matter how much technology changes, people always ask the same questions like: “Why does the computer say we have 50 widgets when we only have 45?” and “Can you tell me who changed the due date of order number 12345?” and “How do sales of widgets this year compare to the same time last year?” It is the last of these that I wish to address today.
Comparing data is a very common activity. Everybody compares data: sales people, production people, purchasing people, and of course management people. This …
Read more -
Guru: SQL Checks For Control Breaks
April 6, 2020 Ted Holt
A control break occurs when the combined value of one or more fields changes from one row (record) to the next when reading a data set sequentially. I used to write RPG programs with control breaks often. Now that reports are less common, I write them less often, but that’s not to say I never write a program with control breaks.
When I first learned to handle control breaks in RPG, I used the L1 through L9 level indicators. These worked wonderfully and fed my family for several years. When I moved from the System/36 world to the S/38 (and …
Read more