Ted Holt
-
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 -
Guru: Make It Easy On Someone Else
August 20, 2018 Ted Holt
“Make it easy on yourself,” warbled Dionne Warwick when I was just a pup. That may be good advice when severing a romantic relationship, but not when programming computers. Instead of making it easy on ourselves, we who develop and support applications need to make it easy on the people we serve.
Recently I worked on a project in which users had to enter time values into various Web pages. The original specifications stated that users would enter time values in a format we commonly use in the United States: two-digit hour, a colon, two-digit minute, a space, …
Read more -
Guru: Beefing Up The Job Log, Take Two
August 6, 2018 Ted Holt
In Tracing Routines Explain Why The Computer Did What It Did, I wrote about the usefulness of writing information about program execution to determine why a program run gave certain results. Today I want to present a simpler method than the tracing routines. The tracing routines are not obsolete, but they are powerful and I have found them at times to be overkill.
Just a word about terminology. Since I wrote that article five and a half years ago, my reading has led me to a different understanding of the terms tracing and logging. I’ve since decided that …
Read more -
Guru: Refactoring RPG – Indicators
July 30, 2018 Ted Holt
Occasionally I hear someone comment about how terrible indicators are. I don’t think they’re bad. Indicator-laden RPG helped me graduate debt-free with a computer science degree and housed, clothed, and fed my family for several years. I prefer to say that indicators were good for their time, but now we have better programming techniques that I much prefer to use.
Refactoring code to reduce or even eliminate the use of predefined indicators (not indicator variables) can pay off big in benefits. The fewer indicators a program uses, the easier it tends to be to read, understand, modify, and debug that …
Read more -
Guru: Table Value Constructors Build Tables On The Fly
June 25, 2018 Ted Holt
Because I wish to be as valuable and productive as possible to the people who pay me to program their computers, I continually search the Web for new ideas and techniques. Doing so often leads me to sites that cater to other computing platforms. Today I want to share with you some SQL techniques that I learned from Microsoft SQL Server professionals.
These techniques are based on the Table Value Constructor (TVC), which is a group of data values, usually literals, organized into rows and columns. In its simplest form, a table value constructor is literal tabular data that is …
Read more -
Guru: How To Override Query Options
May 21, 2018 Ted Holt
QAQQINI is a physical file with which you can control certain behaviors of the DB2 for i query engine. For a list of the query options that you can change, visit the IBM Knowledge Center. Normally you don’t have to use it, as the engineers of IBM have done a great job designing an optimizer that does a great job.
At times you may want to change a query attribute for a certain job. There is no need to build many QAQQINI files to handle all possibilities. Instead, you can temporarily override query attributes within a job. DB2 for …
Read more