Ted Holt
-
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 -
Guru: Addressing A Legitimate Question
December 10, 2018 Ted Holt
This is the last Monday issue of The Four Hundred for 2018. My, how time flies! I like to do something different at year end. In previous years I have solved Sudoku puzzles, found my way through mazes, solved the peg game, and more. This year I wish to honor a request that has come from various people and to address what they consider to be a legitimate question.
As I wrote recently, the question I hear occasionally goes something like this: “Why bother with service programs? Why not use dynamic calls?” Rather than insult the …
Read more -
Guru: More About Merge
November 5, 2018 Ted Holt
I often read back through articles that have appeared in this august publication to look for errors and omissions. Such an expedition recently made me aware that I have not told you as much as I would like to about the SQL MERGE statement. Today I am pleased to provide more information.
First I want to be sure that everybody understands is that you can add conditions to the WHEN MATCHED and WHEN NOT MATCHED expressions. That means that you do not have to treat all matched or unmatched rows in the same way. Look at this example:
merge .
… Read more -
Guru: Learn %PARMS! Solve Two CL Problems!
October 8, 2018 Ted Holt
Those wonderful people at IBM have done it yet again! They have gladdened my existence with new CL functionality that solves two problems, and I will never have to face those problems again. Let me tell you about the new %PARMS built-in function.
The %PARMS function returns the number of parameters that are passed into a CL procedure (i.e. a CL program or a CL module). In the past, I have monitored for message MCH3601. That works in some situations, but not in all. The %PARMS function gives me an unambiguous way to know whether a parameter was passed or …
Read more -
Guru: The Binding Directory Is The Key
October 1, 2018 Ted Holt
Hey, Ted:
We license a software package to handle email on our IBM i system. When I write an RPG program that uses those routines, I can’t compile with the usual Create Bound RPG Program (CRTBNDRPG) command. Instead I have to use a two-step process — Create RPG Module (CRTRPGMOD) followed by Create Program (CRTPGM). Can you explain why this is? Even better, can you tell me if there is a way that I’m not aware of to compile with CRTBNDRPG?— David
David continued, “I use CRTBNDCL and CRTBNDRPG, as they get me what I need, otherwise I am …
Read more -
Guru: Data-Centrism, Step Zero
September 10, 2018 Ted Holt
I hear a lot these days about the need for data-centric information systems. That is as it should be. The proper way to support an organization is to remove logic from application programs and put it into the database through such devices as constraints and triggers. However, before many shops can take the first step in that direction, they need to take what I call step zero.
Step zero in data-centric computing is to remove hard-coded data values from programs and put them into the database. Just as the database manager should enforce business rules (e.g. we don’t ship to …
Read more -
Guru: Refactoring RPG – GOTO
August 27, 2018 Ted Holt
When I first learned COBOL, I coded loops the way all the programmers in my shop did — with GO TO. Paragraph names were labels, not routines. Then I took a class in COBOL and learned structured programming. I’ve never looked back. I wish other people felt the same way, because I don’t like to work on GOTO-laden programs.
Injudicious use of branching — in RPG that would be the GOTO and CABxx op codes — is a major reason I refactor. GOTO plays havoc with program “logic”, a word I hesitate to use in this context. The minute someone …
Read more