Notes
Searching strings in SQL with LOCATE_IN_STRING
I’ve been somewhat remiss in maintaining this blog over the past few months, but the LOCATE_IN_STRING scalar function really is worth mentioning. The LOCATE_IN_STRING function returns the starting position of a string (called the search-string) within another string (called the source-string). If the search-string is not found and neither argument Read more…
Notes
Close *ALL the files on-exit
I have previously mentioned the ON-EXIT operation code, and now it’s the turn of something I should have known about years ago. Back in the old days, handling files was simple. All of the files you declared would be opened when the program starts and closed again when the program Read more…
Notes
Submitting a job as a different user
We are all familiar with the SBMJOB command, but it’s rare that anyone looks beyond the first few parameters. Until you need to. One overlooked parameter is USER which allows you to specify the name of the user profile for the job being submitted. Obviously, you need to have enough Read more…
Notes
Querying data queues with QMHQRDQD
Someone recently asked me if I knew of an API that could be used to retrieve the number of entries in a data queue without pulling the entries from the data queue. I didn’t, but a quick search turned up QMHQRDQD.: The Retrieve Data Queue Description (QMHQRDQD) API retrieves the Read more…
Notes
Using QSYRUSRI to check whether a user profile exists
Sometimes you have a table that captures user profiles. Obviously, if the user profile is deleted, there is nothing to delete these table entries. And sometimes, auditors can see this as a problem. There are several ways of handling this, one of which is to make use of the QSYRUSRI Read more…
Uncategorised
30 years of The System
On June 21st 1988, IBM launched the AS/400. This was replaced in 2001 by the iSeries which, in turn, was followed by the IBM i on Power. For 30 years, IBM have managed to deliver a robust platform that delivers exceptional security and a business resilience that you can rely Read more…
Notes
LTRIM, RTRIM and Trim Expressions
I have to admit that, when trimming strings with SQL, I tend to just use the TRIM function to strip off all leading and trailing spaces. But the LTRIM and RTRIM functions really do deserve more attention because they have been recently enhanced to support trim expressions. That is, all Read more…
Notes
Handling divide by zero in SQL
Division by zero is something that, sooner or later, we all have to capture. For the rest of this post, I am using a small temporary table that I have imaginatively named DIV0. It has two columns, COLUMN1 and COLUMN2 and it looks like this: COLUMN1 COLUMN2 5 0 6 Read more…
Notes
Returning data structures from Procedures
This post stems from recent conversation on the subject of prototyped parameters. I always pass parameters as read only (using the const keyword) in order to ensure that no unexpected changes in the called procedure can impact the calling procedure. With this approach, the only value you need to worry Read more…