Ted Holt
-
Guru: Enumerated Data Types In RPG
September 16, 2019 Ted Holt
IT has changed a lot since I entered the field several decades ago, but some things have not changed. I would read in those early days that COBOL was dead, and I read the same thing now. Yet COBOL is 60 years old and still going strong. Back then I heard RPG criticized as “Real Poor Garbage”. These days I hear it scorned as “legacy”, which I assume is supposed to mean the same thing. Yet today’s RPG is better than any of its predecessors for business programming.
RPG supposedly does not have the features of modern languages. Maybe not, …
Read more -
Guru: I’m A Number, You’re A Number, Everybody’s A Number
September 9, 2019 Ted Holt
Do you, like Bob Seger, sometimes feel that you are nothing more than a number? Me too. That’s because to many people, that’s exactly what we are. And if there’s one thing that computers are good at, it’s assigning numbers — to orders, to accounts, to invoices, to transactions, and of course, to people. Since we have to make the computer assign numbers, we may as well learn the modern way to do it.
In my earliest days of programming, I would store the last assigned of a series of numbers in a data file. (The S/34 and S/36 …
Read more -
Guru: Read a Data Area As a One-Row Table with SQL, Take Two
August 26, 2019 Ted Holt
Fifteen years ago, reader W.G. asked me about the possibility of treating a data area as a one-row table (a physical file with one record) in an SQL query. The question intrigued me because in my System/36 days, I had often wished that I could access the local data area (LDA) as a one-record data file in a query.
Today, thanks to Scott Forstie and his team at IBM, I update my response to W.G. with more information. It’s not that the technique I presented in 2004 is outdated — it’s as relevant as ever — but that the fine …
Read more -
Guru: One Way To Deal With Two Null Formats
July 8, 2019 Ted Holt
Hey, Ted:
We are building a new system and want to use modern programming and database techniques. I have had quite a time trying to get nulls to act right. It gets confusing fast because RPG handles them differently than the way embedded SQL does. When using SQL for I/O, how do we handle the two null formats?
— Brian
There are several ways to go about the “problem” of nulls. Let me give you one simple method, but keep in mind that it’s not the only way.
First, let’s create a table and put some data into it.
create
… Read more -
Guru: When %SCAN Isn’t Sufficient
June 24, 2019 Ted Holt
The RPG %SCAN built-in function is wonderful! I can still remember having to look for a string within a string using RPG II on System/36. What an ordeal that was! Yet in some situations %SCAN can’t do all I need it to do. In those cases, I rely on the power of SQL.
One case where SQL comes in handy is when I need a case-insensitive scan. Instead of RPG’s %SCAN function, I use SQL’s LOCATE and UPPER functions, like this:
dcl-s Description char(48); dcl-s pos int (10); exec sql set :pos = locate ('HAMMER', upper(:Description));If Description has the …
Read more -
Guru: Use SQL to Generate Random Data
June 17, 2019 Ted Holt
Suppose I needed to generate a large database table with random data in order to adequately test the performance of an SQL query. Suppose that, for security reasons, I was not allowed to copy the production version of the table. Suppose that I needed a way to generate a lot — and I do mean a lot! — of random data. Suppose this scenario is not mere supposition.
Before an SQL query goes into production, it should be tested against a production-like dataset. Running a query against a test dataset of 25 rows (records) can produce unpleasant surprises when it’s …
Read more -
Guru: Dealing With RPG Errors and Embedded SQL
April 8, 2019 Ted Holt
Hey, Ted! I’m having trouble using some of the new techniques I learned at the RPG and DB2 Summit. Below is a screen shot of a program I am writing. I cannot figure out why the compiler doesn’t like it. Can you see anything that would be causing the declarations to fail?
— Mike
I glanced over Mike’s code and noticed that he used a correlation name in the SELECT and WHERE clauses, but did not define that correlation name for any of the tables, like this:
SELECT x.onefield, x.twofield, x.redfield, x.bluefield FROM MYTABLE WHERE x.onefield = :TestValue;
He …
Read more -
Guru: Why A Function Was There But Could Not Be Found
March 25, 2019 Ted Holt
Hey, Ted:
I am stuck on trying to create a function in RPG to use in SQL. I based it on your FMTDATE function, which I successfully installed and is working great! I have been trying to get this function working for five hours and I am at my wits’ end. Hopefully, you will notice something right away.
–Andrew
The message that Andrew was receiving was SQL0204 (HISFUNCT in *LIBL type *N not found). (I have replaced the name of Andrew’s function with HISFUNC.) Yet the function existed and the service program existed. There was nothing wrong with Andrew’s RPG …
Read more -
Guru: Why And How Not to Use The Aretha Franklin I/O Method
March 4, 2019 Ted Holt
The Aretha Franklin I/O Method is still used heavily in RPG shops even though a better method has existed for decades. In the following paragraphs, I explain the Aretha Franklin I/O Method, tell you why you should not use it, and show you the superior method.
First, let me give credit where credit is due. Although I had been using the Aretha Franklin I/O Method since my System/34 days, I never knew it by that name. Then Dan Cruikshank (now retired) of IBM informed me of this terminology. Here’s how it works:
Assume an RPG program that needs data from …
Read more -
Guru: Easy Date Difference
January 14, 2019 Ted Holt
Hey, Ted:
The dates in our database are stored as seven-digit packed-decimal values in the common CYYMMDD format. In 2018 I wrote an SQL query that reported the number of days between two dates, but it quit calculating properly as soon as it started using 2019 dates. Can you tell me the proper way to find the difference between two dates in days?
–Becki
I don’t know if “the” proper way exists or not, Becki, but I can show you how to do the required calculation. SQL has some handy built-in functions that address your problem.
Read more