Paul Tuohy
-
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 -
Guru: Edit Result Sets in Run SQL Scripts
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 -
Guru: Going Dark In RDi
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 -
Guru Classic: Looking for Commitment, Part 3
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 -
Guru Classic: Looking For Commitment, Part 2
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.
A Database
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 -
Guru Classic: Looking For Commitment, Part 1
June 19, 2019 Paul Tuohy
Author’s Note: This set of three articles was originally published in March of 2009. (See links in Related Stories below). As companies look to modernizing their applications, commitment control can play an integral role. In my next three Guru Classic articles, I will be updating the content of these articles for free-form RPG.
In this article, I will discuss what commitment control is, why you may want to use it, and the basic requirements for commitment control. In subsequent articles, I will look more closely at how commitment control works, different ways in which it can be implemented, …
Read more -
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