Problem. Solved.
IBM recently completed the migration of their online documentation from the IBM Knowledge Center to IBM Documentation.
The tile based navigation takes a bit of getting used to, but it’s not too bad once you do. But if you’re in a hurry, all of the IBM i documentation starts here.
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 is null, the result is zero. If the search-string is found, the result is a number from 1 to the actual length of the source-string. If the optional start is specified, it indicates the character position in the source-string at which the search is to begin.
The format of the function is LOCATE_IN_STRING(source_string, search_string, start, instance)
If the optional start is specified, it indicates the character position in the source-string at which the search is to begin. If start is specified, an optional instance number can also be specified. The instance argument is used to determine the specific occurrence of search-string within source-string. Each unique instance can include any of the characters in a previous instance, but not all characters in a previous instance.
What this means is that not only can you find strings containing a search text, but you can also determine exactly where in the source string that text is.
INSTR can be used as a synonym for LOCATE_IN_STRING. I have used this shorter form in the following example.
select
INSTR('TEST TEXT IS A TEST', 'TEST') a,
INSTR('TEST TEXT IS A TEST', 'TEST', 1, 2) b,
INSTR('TEST TEXT IS A TEST', 'TEST', -1) c,
INSTR('TEST TEXT IS A TEST', 'TEST', -1, 2) d,
INSTR('TEST TEXT IS A TEST', 'TEST', 5) e,
INSTR('TEST TEXT IS A TEST', 'TEST', -5) f
from SYSIBM.SYSDUMMY1
Returns:
A B C D E F 1 16 16 1 16 1
Column A looks for the first instance of ‘TEST’ in the source string and finds it in position 1. Column B is looking for the second instance of the same string and finds it in position 16. All pretty straightforward so far.
With column C, things get a bit more interesting. Starting at position -1 tells the function to search from right to left, starting at the rightmost position in the source string. And in column D, I’m looking for the second instance of the search string, starting from the right.
And finally, in columns E and F, I am starting the search five positions in.
LOCATE_IN_STRING was introduced in 7.1 and provides an effective method of searching within strings without having to resort to regular expressions.
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 ends.
Now, however, the RPG Cycle is no longer used and all code is (or should be) broken out into discrete procedures. This means that we should also be manually opening and closing files as and when needed. The impact of this is that we need to ensure that any open files are closed when we exit a procedure.
The ON-EXIT opcode helps here, but it’s still a bit of a grind if you have to manually check and close every file that may still be open. It turns out, however, that you don’t need to as the CLOSE operation allows you to close all of the globally defined files at once.
Here’s an example. Run it in debug and watch those files magically close themselves when the program ends.
ctl-opt dftactgrp(*no) actgrp(*new) main(Main);
dcl-f LSM101P rename(LSM101P: LSM101R) usropn;
dcl-f LSM102P rename(LSM102P: LSM102R) usropn;
dcl-proc Main;
dcl-pi *n end-pi;
if not %open(LSM101P);
open LSM101P;
endif;
if not %open(LSM102P);
open LSM102P;
endif;
// Do some processing here
read LSM101P;
dsply DESC101;
return;
on-exit;
close *all;
end-proc;
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 authority to use the profile in question, but if you do, the submitted job will then run with all the rights of that user. Doing this can also neatly solve potential object ownership issues.
Here’s an example. I need to duplicate a logical file in the production environment. The CRTDUPOBJ command will do this for me, but that leaves me a s the owner of the new object which can cause several object authority issues down the line.
Fortunately, I can do this:
SBMJOB CMD(CRTDUPOBJ OBJ(TheFile) FROMLIB(OldLib) OBJTYPE(*FILE) TOLIB(NewLib)) USER(QPGMR)
The CRTDUPOBJ command is executed under the QPGMR profile and the resulting object is owned by QPGMR.
SBMJOB is a remarkably flexible command and it’s well worth poking around the parameters to see just how much it is capable of.
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 description and attributes of a data queue. Examples include the number of entries currently on the data queue, the text description of the data queue, whether the queue includes sender ID information, and whether the data queue is keyed.
The attributes of a distributed data management (DDM) data queue can be retrieved with this API.
So here’s an example of the QMHQRDQD api in action.
// --------------------------------------------------------------------
// Program : DSPDTAQSIZ
// Description : Retrieve data queue size
// --------------------------------------------------------------------
ctl-opt main(Main) dftactgrp(*no) actgrp(*new) bnddir('QC2LE');
// --------------------------------------------------------------------
// Prototype declarations
// --------------------------------------------------------------------
dcl-pr Main extpgm('DSPDTAQSIZ');
DTAQ Char(10) const;
DTAQLIB char(10) const;
end-pr;
dcl-pr GetDtaqD extpgm('QMHQRDQD');
RtnVariable char(2000) Options(*VarSize);
RtnVarLen int(10) const;
Format char(8) const;
QDTAQ char(20) const;
end-pr;
// --------------------------------------------------------------------
// Main Procedure
// --------------------------------------------------------------------
dcl-proc Main;
dcl-pi Main;
DTAQ Char(10) const;
DTAQLIB char(10) const;
end-pi;
// ----------------------------------------------------------------
// Data structures
// ----------------------------------------------------------------
dcl-ds F1 qualified inz;
BytesRtn int(10);
BytesAvail int(10) inz(%size(F1));
Max_Len int(10);
Key_Len int(10);
Q_Seq char(1);
Sender_ID char(1);
Force_Write char(1);
TextDesc char(50);
DtaQ_Type char(1);
Auto_Rcl char(1);
Reserved1 char(1);
Cur_Msgs int(10);
CurEntry_Cap int(10);
DtaQName char(10);
DtaQLib char(10);
Max_Entry int(10);
Init_Entry int(10);
end-ds;
// ----------------------------------------------------------------
// Standalone fields
// ----------------------------------------------------------------
dcl-s QualDtaQ char(20);
dcl-s OutText char(30);
// ----------------------------------------------------------------
// Procedure mainline
// ----------------------------------------------------------------
// Qualified Data queue name is Data Queue Name + Library
QualDtaQ = DTAQ + DTAQLIB;
// Retrieve the data queue description
GetDtaQD(F1: %size(F1): 'RDQD0100' : QualDtaQ );
OutText = 'Current Messages: ' + %trim(%char(F1.Cur_Msgs));
dsply OutText;
end-proc;
// --------------------------------------------------------------------
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 API.
The Retrieve User Information (QSYRUSRI) API provides information about a user profile. This API provides information similar to the Retrieve User Profile (RTVUSRPRF) command or the Display User Profile (DSPUSRPRF) command when *BASIC is specified for the type parameter.
The following sample program makes use of the fact that, if a user profile has been deleted, the API returns blanks in all for the user profile name, previous sign-on date and time and all other text fields.
// --------------------------------------------------------------------
// Program : DSPUSRI
// Description : Display User Profile information
// : Experiments with the QSYRUSRI API
// --------------------------------------------------------------------
ctl-opt main(Main) dftactgrp(*no) actgrp(*new) bnddir('QC2LE');
// --------------------------------------------------------------------
// Prototype declarations
// --------------------------------------------------------------------
dcl-pr Main extpgm('DSPUSRI');
UserID char(10) const;
end-pr;
dcl-pr RtvUsrInf extpgm('QSYRUSRI');
RtnVariable char(2000) options(*varsize);
RtnVarLen int(10) const;
APIFMT char(8) const;
UserID char(10) const;
Error char(1) const;
end-pr;
// --------------------------------------------------------------------
// Main Procedure
// --------------------------------------------------------------------
dcl-proc Main;
dcl-pi Main;
UserID char(10) const;
end-pi;
// ----------------------------------------------------------------
// Data structures
// ----------------------------------------------------------------
dcl-ds F1 qualified inz;
BytesRtn int(10);
BytesAvail int(10) inz(%size(F1));
UserProfile char(10) inz;
LastSignon char(13) inz;
res1 char(1) inz;
BadSignons int(10) inz;
Status char(10) inz;
PwdChange char(8) inz;
NoPassword char(1) inz;
res2 char(1) inz;
PasswordExpInt int(10) inz;
PasswordExpires char(8) inz;
DaysToPassExp int(10) inz;
PasswordExpired char(1) inz;
DspSignOn char(10) inz;
LocalPassword char(1) inz;
end-ds;
// ----------------------------------------------------------------
// Standalone fields
// ----------------------------------------------------------------
dcl-s OutText char(50);
// ----------------------------------------------------------------
// Procedure mainline
// ----------------------------------------------------------------
// Retrieve the user profile
RtvUsrInf(F1: %size(F1): 'USRI0100': UserID: ' ');
if F1.UserProfile = *blank and F1.LastSignon = *blank;
OutText = %trim(UserID) + ' has gone';
else;
OutText = %trim(UserID) + ' is still here';
endif;
dsply OutText;
end-proc;
// --------------------------------------------------------------------
You can call this program with the user ID as a parameter and it will display a message indicating whether or not the user profile exists. Extending it to do something useful is a pretty straightforward process.
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 on. Over the years, the architecture has changed; the range of available languages has grown; the available languages have been enhanced and modernised and the uses to which the platform can be put has expanded and continues to do so. And all of this without ever breaking a business critical application.
The System: The i on Power and its predecessors have delivered — and continue to deliver — an IT environment on which you can rely.
Long may it continue.
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 of the characters you might want to trim.
Here’s an example. Imagine you have a text representation of a value and, for some reason, some of these values have Pound, Dollar or Euro signs at either the start or the end of the string. Here’s our table:
CURRENCYVALUE $400 £250 300€
So to trim the left hand currency symbols, all we need to do is this:
select ltrim(CURRENCYVALUE, '$£') from QTEMP.TEST
And to remove that right hand Euro symbol (along with any trailing spaces), we can do this:
select rtrim(CURRENCYVALUE, '€ ') from QTEMP.TEST
Of course, this only becomes really useful when we put it all together so that this:
select rtrim(ltrim(CURRENCYVALUE, '$£'), '€ ') as NUMBERS from QTEMP.TEST
… returns this:
NUMBERS 400 250 300
Admittedly this example is a bit of an artificial one, but being able to easily strip multiple characters from the beginning and end of a string does simplify the process of cleaning up data received from external sources.
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 2
Until recently, I have tended to use a CASE statement to check the column values before attempting to divide. It looks something like this:
select
COLUMN1, COLUMN2,
case when COLUMN2 = 0 then null else COLUMN1/COLUMN2 end as DIVIDED
from div0
… and the output will look something like this:
COLUMN1 COLUMN2 DIVIDED
5 0 -
6 2 3.00000000000000000000000000
This is fine in as far as it goes, but when COLUMN2 is replaced with a calculation, it can all get very messy very quickly.
It turns out that the NULLIF function provides a better approach.
The NULLIF function returns a null value if the arguments compare equal, otherwise it returns the value of the first argument.
What this means is that I can simplify my earlier query to look like this:
select
COLUMN1, COLUMN2,
COLUMN1 / NULLIF(COLUMN2, 0) as DIVIDED
from div0
And the output remains the same.
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 about is the value explicitly returned by the procedure and this enforced simplicity makes life much, much easier for whoever ends up maintaining the application.
But, my colleague wanted to know, what do you do if you want your procedure to return multiple values? It doesn’t make much sense, for example, to call a separate procedure for each line of a customer address.
The answer is to use a data structure.
As an example, here’s a main procedure that, among other things, retrieves a customer address. As should be clear, I am using a data structure (ADR) to hold the address and using the procedure getAddress to populate this data structure.
p Main b
d Main pi
d ADR ds qualified
d Street 50a inz
d House 5s 0 inz
d Postcode 4s 0 inz
d City 50a inz
/free
// Some loop reading customers
// ...
// Retrieve the customer address
ADR = GetAddress(CustomerNumber);
// Continue
// ...
return;
/end-free
p Main e
And this is what the getAddress procedure looks like:
p getAddress b
d getAddress pi likeds(ADR)
d Customer 10p 0
d ADR ds qualified
d Street 50a inz
d House 5s 0 inz
d Postcode 4s 0 inz
d City 50a inz
/free
// Populate the ADR fields
ADR.Street = 'SomeStreet';
...
// And return the data structure
return ADR;
/end-free
p getHistory e
As you can see, as long as the ADR data structure is locally defined in both procedures you can easily pass the contents of the data structure from one procedure to the other.
I know I am using fixed format definitions here, but the box we were looking at while discussing this is only on 7.1.