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.
October 16, 2017 Ted Holt
In the previous episode of this exciting, action-packed series, I introduced you to the exception-handling methods that IBM has built into SQL PL. The cliff-hanger has lasted four months now, much too long. It’s high time I explained RESIGNAL, as I promised I would. I will explain SIGNAL while I’m at it.
Before I start the syntax lesson, I need to explain a bit of philosophy. I use the hit-the-ball-drag-Harry method of exception-handling when I embed SQL in RPG and COBOL programs. That is, I execute a command, test the SQL state, execute a command, test the SQL state, …Read more
October 9, 2017 Ted Holt
I have most likely never seen your database, yet I can tell you with confidence that it is full of codes. We can’t live without them. Codes give us shortcuts for all sorts of types and categories. They consume less storage than the values they represent. They help us keep the database clean and consistent within itself.
But they surely can be hard to read. Some codes are obvious. M for male and F for female, for instance. My experience is that most are not so. Look at this and see how much sense you can make of it.
select… Read more
September 11, 2017 Ted Holt
Giving up RDi and going back to developing with PDM and SEU would appeal to me as much as giving up electricity and running water and moving into a pup tent. RDi has so many nice features that enrich my life. However, RDi does have its quirks, and occasionally I run into one of them.
One of the nice features is the ability to comment and uncomment blocks of code in one fell swoop. In case you’ve forgotten (or didn’t know about) those shortcuts, Ctrl+/ comments out a line or block and Ctrl+\ removes the comment markers. I recommend you …Read more
August 28, 2017 Ted Holt
Since I am a normal red-blooded human being, I try to make life as easy as possible for myself. I don’t do very well in general, but occasionally I manage to afford myself a bit of comfort. Recently I had to deal with long strings of hexadecimal digits. Trying to read that stuff was more than I could deal with, so I wrote a function to help me.
It’s easy to think of hexadecimal literal as strings because they contain the letters A through F. However, they are not strings, but numbers. We use the letters A through F for …Read more
July 17, 2017 Ted Holt
SQL unions combine two or more result sets into one. That’s what they were designed to do. But unions also provide a way to choose between alternate result sets, i.e. to enable or disable SELECT statements at run time. I have used this feature to advantage on numerous occasions. Here’s how it’s done.
First consider the nature of unions. Each result set of a union can return data or no data, depending on the criteria in the WHERE and HAVING clauses. If a SELECT retrieves no data, the system appends an empty set to the union result. The way to …Read more
June 12, 2017 Ted Holt
I once fancied myself a logical thinker. I changed my mind when I started programming computers. I quickly realized that I was incapable of writing an error-free program. Chalk up another valuable lesson to experience. More experience taught me to program for both expected and unexpected conditions, and now I apply that concept to all languages that I use, including SQL PL.
SQL PL has excellent exception-handling methods, and they’re not hard to use. In this article and Part 2 to follow, we look at how DB2 informs you that your SQL request worked correctly or not. Next, we’ll take …Read more
June 5, 2017 Ted Holt
Modernization efforts often concentrate on the database and programs. That is well and good, but there is more to modernization. Replacing Query for IBM i with more modern query tools is also important. But what do you do with those queries that people depend on? More confusing, what do you do with query chains?
A query chain is a series of queries that run one after another, consolidating and reformatting data in temporary physical files, in order to produce a resulting data set, often in report form. It’s not unusual to see small CL programs like the following one:
PGM… Read more
May 15, 2017 Ted Holt
In Three Ways To Manage Unmatched Data I wrote about the use of the RAISE_ERROR function to force a SELECT statement to cancel when unmatched data is considered a fatal error. Another good use of RAISE_ERROR is to force an UPDATE statement to cancel when an invalid condition occurs.
To illustrate, imagine that you and I work in a factory. All factories have inventory. The people we serve purchase some inventory items and manufacture others. Our job is to write a program that will allow certain people to zero out the inventory balance for certain types of purchased items.
The …Read more
May 8, 2017 Ted Holt
The INSERT statement is THE (as in the only) SQL way to add new data to a relational database table. At the risk of sounding like a GEICO commercial, “Everybody knows that.” Well, did you know that the INSERT statement supports three distinct ways to add new rows to a table?
To illustrate the three forms of INSERT, imagine that you and I work for a small company that stores goods in, and ships goods from, a warehouse. Since the company has only one warehouse, there has never been a need for a warehouse ID column in any of …Read more
May 1, 2017 Ted Holt
We IBM i developers owe a great debt to Scott Forstie. He’s responsible for the wonderful DB2 for i Services and IBM i Services, which give us SQL interfaces for many functions of the operating system. Like IBM, we can write SQL interfaces to help us with non-database tasks. I recently did exactly that.
While I can’t say that I never use the Start SQL Interactive Session (STRSQL) command, I can say that I prefer to use GUI SQL clients, in particular the Run SQL Scripts utility that is part of IBM i Access Client Solutions (ACS). I do get …Read more