Paul Tuohy, IBM Champion and author of “Re-engineering RPG Legacy Applications” and “The Programmer's Guide to iSeries Navigator,” is a prominent consultant and educator for application modernization and development technologies on the IBM Midrange. He currently holds positions as CEO of ComCon, a consultancy firm based in Dublin, Ireland, and as partner at System i Developer, the organizers of the RPG & DB2 Summit conference. Previously, he worked as IT Manager for Kodak Ireland Ltd. and Technical Director of Precision Software Ltd. In addition to hosting and speaking at the RPG & DB2 Summit, Paul has been an award-winning speaker at COMMON, COMMON Europe Congress and other conferences throughout the world. His articles frequently appear in The Four Hundred and other leading IBM i publications.
July 17, 2019 Paul Tuohy
As companies look to modernize their applications, commitment control can play an integral role. This set of three articles was originally published in March 2009. The content of the articles has been updated for free form RPG.
In this article I will take a look at the basic rules and coding requirements for using commitment control within RPG programs.
This is the SQL used to create a schema named COMMIT, which contains two related tables called HEADER and DETAILS.
(A) CREATE SCHEMA "COMMIT" ; (B) CREATE TABLE COMMIT/HEADER ( "KEY" CHAR(2) CCSID 37 NOT NULL DEFAULT '' ,… Read more
June 19, 2019 Paul Tuohy
Author’s Note: This set of three articles was originally published in March of 2009. (See links in Related Stories below). As companies look to modernizing their applications, commitment control can play an integral role. In my next three Guru Classic articles, I will be updating the content of these articles for free-form RPG.
In this article, I will discuss what commitment control is, why you may want to use it, and the basic requirements for commitment control. In subsequent articles, I will look more closely at how commitment control works, different ways in which it can be implemented, …Read more
March 18, 2019 Paul Tuohy
One of the questions I have been asked a lot at conferences is “How do you figure out x in SQL?” In this article, I will discuss four things I use a lot when playing with SQL in Run SQL Scripts: VALUES, SYSIBM.SYSDUMMY1, global variables, and the system catalog.
When I am trying to figure out how an SQL function works, my first port of call is the VALUES statement. VALUES derives a result directly from an expression. For example, the following statement:
Would generate the following result set:
You can specify more than one value in …Read more
March 11, 2019 Paul Tuohy
The longer you have been using Rational Developer for i (RDi), the longer the list of filters you are trying to manage. Maybe you have started to get clever with the naming of filters and/or you spend a lot of time using drag and drop to try and keep your filters in some kind of order.
The good news is that RDi does provide a means of grouping filters — Filter Pools.
This is a (very) cut down example of filters in my Remote Systems view. Let’s see how we can use Filter Pools to make this list more manageable. …Read more
March 6, 2019 Paul Tuohy
Author’s Note: This article was originally published in February 2013. I recently had to hunt out this article when visiting a client. The content is even more relevant as the use of SQL stored procedures continues to grow.
One of the great things about writing articles and tips is that people will drop you a note to ask a question or tell you how much they liked (or disliked) what you wrote. But even better is when someone drops you a line to let you know they have taken what you wrote and extended it.
Such was the case with …Read more
February 18, 2019 Paul Tuohy
Over the last few years, it has become more common to store XML or JSON in a column in a table. Whereas SQL provides all the necessary functions to construct/deconstruct XML or JSON from/to relational data, it does not provide an easy means to change the contents of an element. In this article, I am going to demonstrate a technique for changing the contents of an XML element using an SQL stored procedure.
Just to provide some background, I was recently working on a project where DB2 XML Extender functionality was being replaced with the standard XML functions. The project …Read more
February 13, 2019 Paul Tuohy
Author’s Note: This article was originally published in November 2013. This has always been one of my favorite techniques for data modernization. I only wish I had thought of it back in the days of Y2K! The content of the article has been updated for free form RPG and some of the coding enhancements that have been introduced into RPG since 2013.
Recently, during a modernization project, I have been making use of the Allow Repeated Change (ALWREPCHG) option with before triggers. ALWREPCHG allows a before trigger to make changes to the record being inserted or updated, and that lets …Read more
January 16, 2019 Paul Tuohy
Author’s Note: This article was originally published in October 2011 and recently came to mind when I had a discussion with a programmer bemoaning the fact that he could not (so he thought) have a recursive process in RPG. The content of the article has been updated for free form RPG and some of the coding enhancements that have been introduced, into RPG, since 2009.
In programming terms, recursion is the process whereby a function may call itself. Traditionally, this is something we are not used to in RPG. Programs and subroutines cannot call themselves. Or if you did somehow …Read more
September 24, 2018 Paul Tuohy
In my previous article I discussed the use of the standard SOUNDEX and DIFFERENCE functions for phonetic processing. I also identified the major problems with SOUNDEX (based on U.S. English and the first character is static) and DIFFERENCE (loose because it is based on SOUNDEX).
In this article I will discuss how to tackle these problems by writing a customized phonetic function to use in place of or in conjunction with SOUNDEX. The good news is that you do not have to become an expert in phonetics — others have already done the job for you. There are a number …Read more
September 17, 2018 Paul Tuohy
In my next two articles I am going to discuss the use of phonetic functions in SQL. You can use phonetic functions to select or order rows based on the phonetic sound of a string as opposed to the actual characters in the string. The obvious use of phonetic functions is with names, but they can be used with any string columns.
I must admit that this touches on one of my pet peeves — the spelling of my surname. I have lost count of the number of times I have had to spell my name two, three, or four …Read more