It is now easier searching message files

I have always found it a bother to search for IBM i message ids. Which message file contains the particular message id I want? What messages could I use for a date validation error? Etc. I always had wished for an easier way to perform searches like this.

Fortunately the latest Technology Refresh, IBM i 7.3 TR6, has a new Db2 for i view to make my searches for messages so much easier. The view MESSAGE_FILE_DATA returns a row for each message from all the message files in the IBM i partition I am using. I am not going to describe what the columns are here, as I think their names explain what they contain. For a full list of all the columns contained in this view click on the link to IBM's documentation at the bottom of this post.

Everything you can see with the Display Message Description command, DSPMSGD, is in this view. Using this view I do not have to give the message file's name when searching for a particular message id. For example if I am looking for the description for the message "RSC0082" I would have used the DSPMSGD command:

Thursday's video now online

The video recording of the presentation I made with New Generation Software, Inc. is now online, you can watch it by clicking here.

New release, IBM i 7.4, day

The new release of IBM i is out today!

If your Power server has a POWER8 or POWER9 chip, then you are one of the fortunate ones who can install the new release.

You can read the announcement document for IBM i 7.4 here.

Most of the new programming features I have been writing about in IBM i 7.3 TR6 are in 7.4 too.

The biggest thing to happen to Db2 for i for years, Db2 Mirror for i, is only available for this new release. If you are one of those moving to 7.4 and you start using Db2 Mirror for i I am interest to hear your thoughts about it.

For more information on what is available in the new release, and 7.3 TR6, visit these links:

• My initial findings on the day of the announcement
• My pick for the biggest things in IBM i 7.4

New table function to break apart values in columns and fields

A couple of years, and a few Technical Refreshes ago, the LISTAGG SQL built in function was added to Db2 for i. Until the latest TR there was not some easy way to do the opposite, take a string from one column and break it into separate results. I am not saying it was not possible to do this before, it has just got a whole lot easier with the introduction of the SPLIT table function in the lastest IBM i 7.3 TR6.

The syntax of this new table function is as follows:

SPLIT(input list or column,separator character)

SPLIT returns two columns in its results:

1. ELEMENT:  the values extracted from the "input list". This is a CLOB variable that is 2 gigabytes in size.
2. ORDINAL_POSITION:  Not ORDINAL as is given in the IBM documentation for SPLIT. This is the relative position of the value returned in ELEMENT from the original "input list".

Here is an example using an "input list", or a string to you and I, of names.

New subfields added to Program Status Data Structure

The latest Technology Refresh for IBM i 7.3, TR6, has seen two new subfields added to RPG's Program Data Structure. This data strucutre provides me with a wealth information about the status of the program while it is running, and when it errors.

I always add the Program Status Data Structure, PSDS, to all of my RPG programs. I can dump the program and learn a lot of what happened from the information contains within the PSDS.

Rather than manually entering the same data structure into every program, I have my PSDS in a member I just copy, or include, it into the source members of others.

Using SAMEPOS in data structures

Having written about a couple of the Db2 for i (SQL) additions that were made in the latest Technology Refresh, TR6, to IBM i 7.3 I thought I would write about the first of the two new additions to the RPG programming language, the SAMEPOS keyword used in data structures.

We have all created data structures where we have needed to overlay some subfields with another subfield. The way I am use to doing it is to determine at which position of the data structure I wish to start my new subfield, and use the POS to denote where this subfield starts.

01 dcl-ds *n ; 02 SubField1 char(1) ; 03 SubField2 char(1) ; 04 SubField3 char(1) ; 05 SubField4 char(1) ; 06 SubField5 char(3) pos(2) ; 07 end-ds ;

Subfield5, line 6, will overlay Subfield2, Subfield3, and Subfield4.

The SAMEPOS keyword, line 6 below, makes it easier as all I have to give is the data structure subfield I want to start my overlay.

RPGPGM.COM 6th birthday

I know this is going to sound glib, but RPGPGM.COM’s anniversary always creeps up and surprises me. I cannot believe that I have been writing this blog about all my favorite IBM i things for six years.

In past twelve months I wrote the 500th post for this blog. I still worry about running out of ideas, but I have fortunate to have lots of new material as IBM keeps adding new things to IBM i, via the twice yearly Technology Refreshes and a couple of new releases.

Each anniversary I pick what I think have been five of the most interesting things I have written about in the past twelve months. My picks from the last year are:

Using Data Areas with SQL

The latest technology refresh, IBM i 7.3 TR6, and the new release. IBM i 7.4, brought us a new view and table function that allows us to retrieve information from data areas. The closest thing we have had to this before is the Retrieve Data Area command, RTVDTAARA, but these gives us more than just the value held in the data area. The only down side with these being a view and table function is I cannot update the data area using them.

They both have the same name, DATA_AREA_INFO, and returned columns have the same names. The only difference is that the view has two additional columns. If I wanted to retrieve the information for just one data area I would use the table function. The view will list all data areas that fit the selection criteria. I could still get the information for the one data area using the view, but it is faster using the table function.