Ted Holt
-
Guru: Quirky SQL Creations
December 11, 2017 Ted Holt
Hey, Ted:
We are creating a view from a source member using the Run SQL Statements (RUNSQLSTM) command. None of the objects are qualified in the source member. The system always creates the view in the wrong library, no matter how we set the current library. Can you tell me what is happening?
–William
William ran up against the quirky behavior of the SQL CREATE VIEW statement. It sure threw me for a loop. I would have thought that the view would be created in the current library. Not so. William found the answer to his question in the IBM …
Read more -
Guru: Using SELECT * With Cursors
November 27, 2017 Ted Holt
From time to time someone brings to my attention the use of SELECT * with SQL cursors in RPG programs. Specifically, is that a good idea or a bad idea? I have learned that the answer to that question is “It depends.” Using SELECT * in a cursor declaration may or may not get you into trouble.
To set the stage, let’s begin with a simple example — an RPG program that reads one table (physical file) and prints each row (record). Even though most programs use data from more than one table, programs that read only one table are …
Read more -
Guru: At Last! A Tool To Search an Output Queue!
November 6, 2017 Ted Holt
As far as I’m concerned, a technician can’t have too many tools! I appreciate the good people of IBM for the software tools they provide to help us do our jobs. I also appreciate those people who freely share software tools they’ve written. I’m pleased to pass along a tool from faithful reader Tim Swearingen.
The tool is a CL command called Search an Output Queue (SRCHOUTQ), and it fills a gap. You can use SRCHOUTQ to look for a string inside the spooled files of an output queue. The search, I am happy to say, is case-insensitive. Here’s more …
Read more -
Guru: Wow! I Could Have Had Long Column Names! – Take 2
October 23, 2017 Ted Holt
In 1990, three computer science professors named Gorla, Benander, and Benander wrote about debugging effort in COBOL programs. Among their claims was that debugging is easier if variable names were between 10 and 16 characters long. The original native data definition facilities allowed variable names up to 10 characters, but nowadays we can define alias names that Gorla, Benander, and Benander would be proud of.
It is common in many DB2 for i shops to have physical files with field names of six characters or less. This practice dates to predecessor systems, such as the System/36, for which the …
Read more -
Guru: Error Handling in SQL PL, Part 2
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 -
Guru: Making Sense of Codes
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 -
Guru: Remove Unwanted Trailing Blanks With LPEX
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 -
Guru: Legible Hexadecimal
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 -
Guru: Conditional SQL Unions
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 -
Guru: Error Handling in SQL PL, Part 1
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