Paul Tuohy
-
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 -
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