Paul Tuohy
-
Guru: Content Assist Plus in Run SQL Scripts
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 1.1.8.3 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:
call qsys2.
Then press F4 (or Ctrl+Space). You will be presented with a list of all the stored procedures in the schema …
Read more -
Guru: Getting Meaningful Audit Information from a Journal
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 -
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