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.
June 15, 2020 Paul Tuohy
The DB2 for i Enhancements in IBM i 7.3 Technology Refresh 8 / IBM i 7.4 Technology Refresh 2 just made dynamic SQL, embedded in RPG, a lot easier to use. It includes an enhancement entitled USING SUBSET on EXECUTE and OPEN with Extended indicators. This enhancement provides a simple and effective solution to a problem that I first highlighted in an article I wrote back in September of 2015. (See A First Look At SQL Descriptors.)
The “problem” we are dealing with is how to handle a variable number of host variables being used in a dynamic …Read more
March 4, 2020 Paul Tuohy
Author’s Note: This article was originally published in November 2015. This was one of the first DB2 for i Services that I used in anger. I have used a lot since.
One of the frustrating things about being a speaker at conferences is that when you want to attend another session with a topic that piques your interest, it invariably clashes with when you are speaking. But every now and again, the scheduling gods work in your favor. Such was the case when I spoke at the excellent International i-Power 2015 conference at Wyboston Lakes Executive Centre in the UK. …Read more
February 17, 2020 Paul Tuohy
In September 2019, Ted Holt published an article that demonstrated how an identity column can be used to generate a unique key in a table. In this article, I want to expand on that theme and examine another use of an identity column — replacing complex keys.
Using an identity column in place of a complex key makes for much faster joins between tables, as you are joining based on two numbers as opposed to values of multiple columns. Identity columns also make for joins that are easier to comprehend, since they are based on just one column. This approach …Read more
February 5, 2020 Paul Tuohy
Author’s Note: This article was originally published in October 2014. This stored procedure is something I use a lot and has saved me from accidentally deleting dependent views more than once. Basically, I call it before I ever drop a view.
SQLs Data Definition Language (DDL) offers many great features, one of which is the ability to define a view of a view. This can lead to simple or complex structures making use of views of views of views of . . . you get the idea.
But one of the difficulties with this technique is that, once created, it …Read more
January 13, 2020 Paul Tuohy
In this article, I want to share with you an SQL scalar function that I happen to have been using quite a bit recently. At times, when using an SQL select statement, you may want to format a number or date. Something along the same lines as using the %EDITC or %EDITW built in functions in RPG or the EDTCDE or EDTWRD keywords in DDS. In SQL we can use the VARCHAR_FORMAT or TO_CHAR (they are synonyms for each other – both work exactly the same way) scalar function to provide similar functionality.
Since they are synonyms for each other, …Read more
January 8, 2020 Paul Tuohy
Author’s Note: This article was originally published in April 2009. The use of DDL and embedded SQL have come a long way since then but the basic premise of the article still applies. I have removed the embedded SQL example using a SELECT * since this is a style that I no longer recommend (from the point of view of self-documenting code, possible performance gains and breaking old habits of thinking in records). I also changed the example of reformatting a numeric date column to use a DATES table as opposed to functions (a faster and better approach). I removed …Read more
October 7, 2019 Paul Tuohy
Before getting into the detail in this article, I want it to be clear that I do NOT (in any way) advocate the direct editing of data in a production database. But when it comes to a test database, then the ability to directly edit data is invaluable.
Back in the days of System i Navigator, you could right click on a table, select the Edit option and a window would open containing the contents of the table. You could directly edit the contents of any cell. Rows could be inserted or deleted using the Rows option on the menu. …Read more
August 19, 2019 Paul Tuohy
In this article I am going to show you how to configure RDi for dark mode. Dark mode is where the background of an application is changed from white to black. Some say that dark mode makes text (and especially code) easier to read. Others say it’s more difficult to read. Personally, I am a convert, but I know other developers who hate it. Maybe you should give it a try and see which you prefer.
Recently, there has been a lot of debate about dark mode (mostly prompted by Apple introducing it as an option in its operating systems), …Read more
August 14, 2019 Paul Tuohy
As companies look to modernize their applications, commitment control can play an integral role. This set of three articles about commitment control was originally published in March of 2009. (See Related Stories below.) The content of the articles has been updated for free-form RPG. In this article, I will take a closer look at how commitment control works by looking at the journal entries for commitment control. I will also discuss the LCKLVL and CMTSCOPE parameters on the STRCMTCTL command.
Commitment Control And Journals
Commitment control is dependent upon the use of a journal. A journal is used in conjunction …Read more
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