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.
November 29, 2021 Paul Tuohy
By now, you should be well aware that Run SQL Scripts provides content assist (promoting) for Select statements (see Guru: ACS 184.108.40.206 Content Assist Includes Prompt For SQL! ). But did you know that you can also use content assist for stored procedures, table functions and parameters? Let’s see how it works, using some of the Integrated File System (IFS) procedures and functions provided by IBM i Services.
Prompting a Stored Procedure
Open Run SQL Scripts, type in:
Then press F4 (or Ctrl+Space). You will be presented with a list of all the stored procedures in the schema …Read more
July 26, 2021 Paul Tuohy
Journaling is an invaluable tool that is used for data recovery, data replication, commitment control and, of course, auditing. But getting at the audit information in an easy-to-use manner can be cumbersome. In this article I want to introduce you to a stored procedure that will create an audit table for any table/physical file and a corresponding view that can be used for easy auditing of changes.
For example, if I am auditing the EMPLOYEE table (I will be using the EMPLOYEE table in the standard Db2 Sample Database) for a change to the SALARY column, I would use the …Read more
May 24, 2021 Paul Tuohy
Way back in October 2014, in the article Find A View Of A View Of A View. . ., I detailed a stored procedure that, given the name of a table or a view, provides the full list of dependent views and all of their dependents and all of their dependents, etc. This is a stored procedure that has served me well in the world of DDL, where it is common to have views of views of views.
In this article I will describe a follow-on stored procedure that will generate the DDL for a given table/view and all …Read more
February 22, 2021 Paul Tuohy
In this tip I would like to touch on two items, in relation to stored procedures, that may have escaped your notice: prompting stored procedures and/or parameters (in Run SQL Scripts) and passing parameters by name.
I must admit that, as I have gotten older, my ability to remember the names and parameters for stored procedures has, shall we say, decreased. At this stage, I am lucky if I can remember which library/schema one of my stored procedures is in!
A case in point. I recently received an e-mail about a stored procedure I had written about (back in 2015) …Read more
October 5, 2020 Paul Tuohy
The ability to overload subprocedures in RPG is something I had been waiting for a long, long time. IBM finally made it available through a Technology Refresh (7.4, TR1 or 7.3 TR7). If you are not familiar with the term, overloading (in RPG) is the ability to create multiple subprocedures of the same name with different implementations.
Let’s have a look at how overloading might benefit us when it comes to writing programs and subprocedures. This is a portion of a prototype copy member that, amongst others, contains the prototypes of these three subprocedures:
dcl-pr format_from_Date varChar(10) extProc(*dclCase); dateIn date(*ISO)… Read more
July 14, 2020 Paul Tuohy
Using mixed case makes source code easier to read and easier to debug. I programmed in all upper case for many, many years, so I don’t recoil in horror when I see something in all uppercase. However, it’s a bit more difficult for developers who aren’t quite as long in the tooth as I am. To them, all uppercase is pretty abhorrent and bad (and I don’t mean bad as in good).
There is a potential case issue with subprocedure names: there are at least three places on the system where the system shows subprocedure names in uppercase, regardless of …Read more
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