Paul Tuohy
-
Guru: When Playing With SQL
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.
VALUES
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:
values upper('paul');Would generate the following result set:
You can specify more than one value in …
Read more -
Guru: Manage Filters in RDi
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 -
Guru Classic: What’s That Name, Again?
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 -
Guru: Change XML Elements in SQL
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 -
Guru Classic: Triggers – Allow Repeated Change
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 -
Guru Classic: Call Again And Again And Again . . .
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 -
Guru: Phonetic Functions In SQL, Part 1
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 -
Guru: Handling Constraint Violations In RPG
September 5, 2018 Paul Tuohy
Author’s Note: The contents of this article were originally published as two separate articles – Handling Constraint Violations in RPG and Handling Constraints Revisited. 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.
Constraints have been around for a long time but apparently have not quite made it into every programmer’s tool kit. This is partly explained by the fact that implementing constraints in an existing application can be tricky, but it doesn’t explain why constraints are not used extensively in new …
Read more -
Guru: Getting the Message, Part 2
August 29, 2018 Paul Tuohy
Author’s Note: This article was originally published in October 2009. Since then, I have worked on many modernization projects with many clients and, in every one of those projects, we have used some form of the contents of this (and the following) article. 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. The original articles also showed examples of direct calls to RPG subprocedures from PHP. Given that we now have many languages (Node.js, Python etc.) that interact with RPG, I changed the mechanism …
Read more