Ted Holt
-
Guru: Use SQL To Find Duplicate Source Code
March 12, 2018 Ted Holt
According to Brian Tracy, “good habits are hard to develop but easy to live with; bad habits are easy to develop but hard to live with. The habits you have and the habits that have you will determine almost everything you achieve or fail to achieve.” This is as true in programming as in anything else we may do.
Unfortunately, even those of us who strive for good work habits often have to follow the work of people who did not. One bad habit I come across occasionally is known in software engineering as WET solutions. WET stands for “write …
Read more -
Guru: Combine Related Rows Using SQL
February 12, 2018 Ted Holt
A reader writes: “Hey, Ted. In our ERP system, certain business objects, such as sales orders and purchase orders, can have multiple comment records. Is it possible, using SQL, to combine all the comment records for an order into one long comment and retrieve it as a column in a result set?”
I can relate to this. I can remember supporting an ERP system where not only the orders, but the order detail lines, could have such comments. End users depend heavily on such unstructured data to do their jobs. To answer your question, yes, it is possible and it …
Read more -
Guru: Three Suboptimal I/O Practices
February 5, 2018 Ted Holt
I have on numerous occasions looked at source code that I had written in previous years and asked myself, “Why on earth did I do that? What could I have been thinking?” We live and learn, or at least we hope we learn. Today I share three database practices that I see from time to time that can be simplified. Maybe there’s something for you to learn today.
Before I share the three examples, let me say that I do not consider the more cumbersome code to be wrong. To my way of thinking, any code that produces the correct …
Read more -
Guru: Continue A Sequence When Inserting
January 15, 2018 Ted Holt
Carlos writes, “Hey, Ted! I have a question regarding inserting rows into a file that is keyed on a sequence number. I need to insert more rows, and I need the new rows to have the next available sequence numbers. Can I perform this task with an SQL INSERT statement, or do I have to use record-level access?”
If the sequence number were defined as an identity column, Carlos would have no problem. Unfortunately, the sequence number column is a simple numeric field. Fortunately, I was able to give Carlos two solutions. It would not surprise me if you can …
Read more -
Guru: An Update Conundrum Finally Solved!
January 8, 2018 Ted Holt
Russ writes, “Hey, Ted! I was trying to use a pure SQL solution to adjust some data today. My SQL statement worked fine in quality control, but failed in production. I’ve been wondering if the failure was caused by the database or by me! My problem was to renumber sequence numbers for a customer in a table.”
Russ’s question arrived in my inbox on February 1, 2012. Yes, almost six years ago. At the time, I couldn’t help him. But with the latest technology refreshes from IBM, there is now a way to make the update work properly, and I’m …
Read more -
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