Thursday, September 28, 2017

Expecting new Technology Refreshes next week

With the COMMON user group Fall conference happening next week I am sure that I am not the only person expecting an announcement about new Technology Refreshes for IBM i releases 7.3 and 7.2.

Alas, I do not have any "inside information" on what will be included in these TRs. But I have found "place holder" pages, which at the time of publication of this post are empty.

I have to admit I am geeking out waiting to see what new features are going to be released, and then come up with reasons how I can use them.

If you are interested in what will be in these TRs keeping checking this blog, because as soon as I find any information about them I will share it here, on RPGPGM.COM.

Wednesday, September 27, 2017

Index Advisor helps performance

using index advisor build sql indexes to speed up your sql

As we all start using more SQL to get data from our tables and files, there are things we can do to ensure that our programs are not that "monster" that takes down a system. One of them is to ensure we use the best access path to get that data. Rather than hunt for the "best" logical file/index to match our key, we should always build our Selects, etc, over the physical file/table and allow the Db2 for i engine to find the best access path for us. Personally, I think that is remarkable that the Db2 for i engine will find the best logical file/index to use. If there is not an index that matches what I need, it builds a temporary access path. This temporary access will be reused, within the same job, whenever I run that SQL statement. You may have noticed if you execute a complex SQL statement many time it will get faster the more times it is run, within the same job.

Building temporary access paths can still result in a "monster" being created. The best way to improve the performance of the "monster" is to make sure that there are indexes that match what I need. Rather than having to identify all the indexes myself, I can use Index Advisor and have Db2 for i recommend the index I should build.

Wednesday, September 20, 2017

REGEXP for searching in strings

regexe instr to find instances of character in search string

Continuing my posts about the regular expressions introduced as part of IBM i Technical Refreshes to 7.2, TR6, and 7.3, TR2, I have struggled to come up with a good brief description that would suffice for this post's title. IBM's documentation describes this function as:

returns the starting position or the position after the end of the matched substring

After playing with this function for awhile I would describe it as: "returns the position where the searched pattern occurs, whether first or nth occurrence, in the source string". And I would have to admit that description is better than IBM's, but it not a good description either. It is probably best I start explaining how to use this function and you will be able to decide how you would describe it.

The format for this function is as follows:

Monday, September 18, 2017

Db2 for i poster

Scott Forstie, Db2 of i Team Leader, posted on twitter:

Are you bummed out that you don't have #Db2fori posters? Cheer up, you can have the pdf

The link to download the poster is here.

I have already printed mine and stuck it on the wall of my office.

Thursday, September 14, 2017

Writing to a User Space, update

get data from user space using pointer

After publishing my previous post several people contacted me regarding the method I had used to write data to and retrieve data from a User Space. In my example code I have used the QUSCHGUS API to write, and the QUSRTVUS API to retrieve. The messages asked why I had taken that approach versus using a pointer?

Either approach is valid, as they both perform the same function. But in the age of modern RPG code using pointers can be considered the better way to go. As I was remiss in my previous post I intend to make amends with this one.

One thing to be aware of if you are using your own User Spaces, rather than ones created by a list API, is your own does not contain the header information for the User Space. I will need to determine the equivalent data myself.

Wednesday, September 13, 2017

Writing to a User Space

using api to write to user space

I have written in the past about using User Spaces with List APIs. These earlier examples showed how I can retrieve data from a User Space, but how would I write or update my own User Space?

A User Space is like a data area, only bigger. While the maximum size of a data area is 2,000 bytes, a User Space can be up to 16,776,704 bytes. And I must use APIs to put and retrieve data in and out of a User Space.

Why would I use a User Space? They can be anything I want them to be.

  • Passing large number of parameters between programs
  • Share data between more than more than one program, I could use it to share data between more than one procedure
  • Snapshot of a file record (or table row)
  • I know of one software vendor that stores SQL statements in User Spaces that are later executed

Wednesday, September 6, 2017

REGEXP for count

regexe expression count used for counting characters in a string

With the latest IBM i Technical Refreshes to 7.2, TR6, and 7.3, TR2, a number regular expressions functions were added to Db2 for i (SQL). In this post I am going to describe the REGEXP_COUNT function.

Regular readers of this blog will know that I have already written about the REGEXP_LIKE. Rather than repeat a lot of the things I wrote about in the post I am just going to refer you to it at various time in this one.

The format for this function is as follows:

  REGEXP_COUNT(source-string, search-pattern, start-position, 
               regexp-flags)

Tuesday, September 5, 2017

Glimpses to the future releases of IBM i

timeline of current and future releases of ibmi

Whenever I have seen this image in IBM's presentations I have wanted to get a copy to post in this blog. Why? It shows IBM's plans for the life of the current releases and the timeline for future releases of the IBM i operating system.

I am not so naive to think these dates are fixed, we all realize that the release dates of future releases, and the end of support for current releases, can change. The exception is IBM i 7.1, its end of support is set for April 30, 2018.

I still expect Technology Refreshes to be released twice a year. I do expect a new TR to be announced at the COMMON Fall conference, next month.