Paul Tuohy
-
Guru: Generate SQL for Dependents
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 -
Guru: Prompting Stored Procedures
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 -
Guru: Overloading Subprocedures
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 -
Guru: The Case for Mixed-Case Procedure Names
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 -
Guru: SUBSET on EXECUTE and OPEN
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 -
Guru Classic: Tuning SQL Environments On i
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 -
Guru: Using Identity Columns For Complex Keys
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 -
Guru Classic: Find a View of a View of a View. . .
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 -
Guru: Formatting Numbers and Dates/Times/Timestamps in SQL
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 -
Guru Classic: Don’t Ignore the View
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