Wednesday, October 18, 2017

Replacing parts of strings using regexp

regexp replace to replace parts of strings and fields

The fifth installment of my examples of the using regular expressions added to Db2 for i as part of IBM i 7.3 TR2 and 7.2 TR6 is going to be about REGEXP_REPLACE. I can already replace parts of strings using SQL's REPLACE and RPG's %SCANRPL, but REGEXP_REPLACE allows me to use regular expressions to find the pattern I wish to replace.

The syntax for the REGEXP_REPLACE is:

  REGEXP_REPLACE(source-string, search-pattern, replacement-string,
                 start-position, occurrence, flags)

Wednesday, October 11, 2017

Using Regexp to substring

regular expression, regexe, to substring

In this third installment of the regular expression functions added to Db2 for i is going to be about REGEXP_SUBSTR. The SQL SUBSTRING and RPG %SUBST built in function need a fixed start and number of positions. The REGEXP_SUBST allows me to use regular expressions patterns to find and substring the pattern I desire.

All of these Regular Expressions functions were added to Db2 for i as part of the 7.3 TR2 and 7.2 TR6 updates. Rather than repeat what I have in the earlier posts, I will refer you to them for more details.

The format for this function is:

  REGEXP_SUBSTR(source-string, search-pattern, starting-position,
                occurrence, flags, group)

Wednesday, October 4, 2017

More information about the new TRs

more links about ibmi 7.3 tr3 7.2 tr7

I saw this on Twitter this morning. Quote from Tim Rowe, business architect for application development on IBM i:

"We will keep updating RPG as long as we keep updating IBM i and we have a long road map"

A day after the announcement of the latest Technology Refreshes for IBM i 7.3 and 7.2 a few more links are coming out from various IBM sources which I consider worthy of sharing:

Tuesday, October 3, 2017

IBM i 7.3 TR3 and 7.2 TR7 announced

ibmi 7.3 tr3 and ibmi 7.2 tr 7 announced

In a blog post published this morning Steve Will, chief architect of IBM i, announced the new Technology Refreshes for IBM i 7.3, TR3, and 7.2, TR7. I am not going to give a long list of links to all the new and enhanced features and functions, the Enhancements pages, listed below, do that. What I will mention are the things that caught my eye.

As with earlier TRs there are many additions and changes to Db2 for i (SQL). These will keep me busy over the next few months writing about them for this blog.

Monday, October 2, 2017

New Technology Refreshes information found

IBMi 7.3 TR3 and 7.2 TR7 announced by IBM

Ahead of the announcement of the new Technology Refreshes I found the following pages listing some of the information of what is included within them.

I assume that the official announcement will be made tomorrow at the COMMON Fall conference. When it is made I will write another post including all the relevant information and links to the various articles and pages.

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.

Wednesday, August 30, 2017

Using derived columns to sort files

using sql views and derived columns to sort the data in the results

My employer uses "descriptive" part numbers for the parts they manufacturer. Without going into too much information, "descriptive" part numbers contain information about the item within the part number, it could contain characters that describe the material used to make it and/or characters to denote its end use. I am often asked to create a report for certain collections of parts. These "collections" are identified by a range of characters in certain positions of the part number, and the sort order desired may not be in a simple sort order, I will explain what I mean by that later.

The "descriptive" part numbers I will be using in these example consists of three parts:

Wednesday, August 23, 2017

REGEXP like added to SQL

regular expression regex like

I can remember many years ago having a conversation with a non-AS400 (yes, it was that long ago) person about the programming environment we worked with. He scoffed at the AS400 stating he could never work on it as it did not support Regular Expressions. I am not going to describe in too much detail what Regular Expressions, sometimes called regex or regexp, are as others have done a good job doing that. To give a very simple explanation: it is a sequence of characters that define a search pattern, which is then used to find or find and replace parts of strings. If you are interested in learning more check the Wikipedia page about regular expressions here.

Within the Db2 for i PTFs that accompanied IBM i TR2 and 7.2 TR6 a number of regular expressions functions were introduced to SQL. I am not going to describe them all in one post, as it would be too long, as I feel they merit their own posts. To start this post is about the REGEXP_LIKE. I am just going to give a simple overview of how to use this, if you want more details or want to learn how to make complex statements I suggest you visit the page on IBM's KnowledgeCenter, link at the bottom of this post, or use your favorite search engine to search for "REGEXP_LIKE". The posts about REGEXP_LIKE for Oracle's PL/SQL I found helpful.

Tuesday, August 22, 2017

Finding the minimum value across several columns

minimum value of more than one column

Sometimes I find something that I think is cool, it may be something small, like this, but I feel it is worth sharing.

I was asked about how to find the minimum value from multiple columns. SQL offers us the MIN function for returning the smallest value from a column. I can use that to get the smallest value from multiple columns, and the smallest value from all of those columns.

I have a file with three fields containing the following numbers:

Wednesday, August 16, 2017

Sometimes a List API is better than the SQL View

use list api and user space to get data about spool files

Sometimes it is just "better" to use a list API than it is to use the SQL View that "replaced" it. This week has given me an example of this. I was asked to create a program that would allow users to email themselves reports. I knew I would have to present the users with a list of spool files from which they could select the one they would want to email.

"No problem", I thought, "I will just use the Output Queue Entries SQL View."

I created a View over the OUTPUT_QUEUE_ENTIRES View, and then created a program using a multiple row fetch to get more than one row from the View. In the "test" IBM i partition the program ran a bit slow the first time I ran it, then ran faster each time after as the DB2 query engine created a temporary access path for me. Being a "test" partition there are not many output queues or spool files when compared to the "live" partition. I moved the objects I created to "live" and called the program there. It took more than 20 minutes to load the subfile with the list of user's spool files. With several hundred users and a very large number of spool files (many thousands, does anyone ever delete spool files?) in the "live" partition it was searching through all of them to find the few spool files belonging to me. The time taken made this method unacceptable. Back to the "drawing board".

Tuesday, August 15, 2017

We are not impostors

value of self taught programmers

Last week I found a wonderful article by Roger Pence in IBM Systems magazine.

It started with the title "Your are not an impostor", and continued with the subheading "Self-taught RPG programmers bring valuable skills to enterprise programming teams". As one of those self-taught programmers it caught my eye, and he made some good points about what would make me feel like an impostor, and what I can do to overcome it. It confirmed my opinion that there is a place in every organization for the self-taught and motivated programmers, alongside the formally educated graduates.

You can read the article on IBM Systems online magazine here.

If, like me, that site does not always load, I have placed a copy here (PDF file).

Alas, when I tried to go to the URL given for "The Impostor's Handbook: A primer for self-taught programmers", bit.ly/2ivK5lt, it went to a 404 page not found error page. If you have the correct URL for this page I would be grateful if you would post it in the comments section below.

Wednesday, August 9, 2017

Resetting the value of a variable

using reset operation code to set value back to initialization value

One of my colleagues was reading the post about initializing variables when defining them and asked a question that I had not considered when writing that post.

"What if I change the value in the variable," he asked, "can I change it back to its original value?"

Fortunately there is in RPG an operation code that will do this. The RESET operation resets the value of any variable or record format back to the value it was at the initialization phase of the program. It is not new operation, I have used it for as long as I have programmed on the AS400 - IBM i.

Thursday, August 3, 2017

Types of saves

types of saves

I have Steve Pitcher to thank for sharing this image so that I could post it here. It is from IBM's KnowledgeCenter showing the different types of saves that can be performed using the various save commands, on the right side, and the options on the SAVE menu, on the left.

list of the types of saves and what they save

Wednesday, August 2, 2017

Checking Software License expiration dates using SQL

checking softwatre license expiration dates using sql

Included in the latest Technical Refreshes for IBM i 7.3 and 7.2 is a SQL View and Procedure to help us monitor the licenses of the software on our IBM i.

IBM uses Software License Keys with certain IBM i products to ensure that I have paid the appropriate license fees to use these products on an IBM i. I know of several third party IBM i software vendors who also use this method to stop the unauthorized copying of their software to unlicensed servers. It is all standard stuff to prevent the distribution of unauthorized software.

Thursday, July 27, 2017

IBM i is more than an AS400

why ibm i  is not as400

I frequently get messages asking why does IBM call it IBM i when it is just an AS/400, or even System/38, by another name. Talking to other authors and IBM-ers I find that I am not alone to receive communications like this.

Steve Will, chief architect for IBM i, has felt compelled to dispel this fallacy. Earlier this month he made an online presentation, sponsored by HelpSystems, explaining how IBM i differs and is a lot more than AS/400. The video can be watched on the sponsor's website here.

The video lasts just over an hour, and, in my opinion, is worth watching to learn the evolution of the operating system to become the thoroughly modern IBM i.

Wednesday, July 26, 2017

Position cursor into field on screens

position cursor on a display file record format

The germ for this post's subject came from a two part question I was asked:

  1. How can I know which field the cursor is in when Enter is pressed?
  2. How can I position a cursor on some field without using indicators? No indicators are available in that very old screen.

Fortunately there are ways to do this by adding a few keywords and fields to the display file's DDS. In these examples I am going to use one display file with three screens, each showing a way to do what has been asked, and the RPG code needed too (and I am going to cheat on the second part).

Monday, July 24, 2017

DB2 name change

db2 family renamed

Last week at OCEAN User Group's Technical Conference I attended a couple of sessions by Scott Forstie, IBM's Business Architect for DB2 for i. I noticed in his presentation he had used "Db2" rather than "DB2".

When I asked him about this he explained that last week (Monday July 17, 2017) the DB2 family had been officially renamed "Db2". The Db2 family is:

  • Db2 for i
  • Db2 for z/OS (mainframe)
  • Db2 for Linux, Unix and Windows

I found that that Db2 page on IBM's website has already been changed, see here.

Now I need to remember to use the new name when writing about it in this blog.

Wednesday, July 19, 2017

Finding the number of times a character is in a field using SQL

using sql to count the number of times a character is in a field

I needed to determine, first, if a certain character was present in a file's field, and then how many times it occurs. Using RPG's file input I would not be able to do this without reading and checking every record's field. Could this be done easier in SQL? Yes.

I am going to give examples of various ways of being able to tell if a certain character is in a field in a file, using increasing complex methods I found. At first I will just give the SQL Select statement for each, and at the end the SQLRPGLE code I created to do this in a program.

Wednesday, July 12, 2017

Updating one row at a time using SQL

update current row/record using sql

I have been asked to write some more about using SQL embedded in RPG programs. One question I have been asked by several people is how to update a row from a file that had been previously Fetched, just like READ and UPDATE using RPG's native database access. I thought it would be useful to show examples using both RPG native database access and SQL.

First I need a file, as this is for example purposes it very simple, just two fields and one key field:

Wednesday, July 5, 2017

SQL null byte map as a 2-dimensional array

using a 2 dimensional array for sql null byte map in rpg

Nulls are something we are all going to have to get use to handling as we move from DDS files to SQL tables, or start using data from DB2 of i or other non-IBM i databases. Recently I was working on a project with a SQL Table with more than 300 columns. If there was no data in a particular field it was null. If you think about it null is a better alternative than blank or zero as those values could be valid values for the contents of that column.

I have written about handling null in SQL in an earlier post, but in this scenario the method I described to define the null byte map was not practical. I am not going to list 300+ subfields in a data structure array for the null indicators for the matching data structure array I would be moving the SQL Table's data into.

Thursday, June 29, 2017

RPGPGM.COM four years old

Wow! I find it hard to believe that I have been writing this blog for four years.

We have been fortunate to share this journey in an exciting time for the IBM i operating system. In all my years of working with this operating system, and is predecessors, there seems to be so much more functionality added every year than there was in the past. During these four years I have written almost 400 posts and pages, trying to share my enthusiasm for IBM i and what it can do.

Wednesday, June 28, 2017

Using relative record number in SQL

sql rrn function to retrieve relative record number from file

I have written about using the Relative Record Number, RRN, in RPG and said that I could not think of a reason why I would ever need to use RRN in one of my programs. Well, I have to take that statement back as I recently found a really good reason for using it. I needed a quick way to retrieve the last record from several different "flat files", and check if it started with the characters "END:". I could do it in RPG, but what is the fun with doing that when I could do the same using SQL?

What is my definition of what a "flat file" is? See here.

I am not describe these "flat files" as it is not really relevant to what I am going to show. I will say that I had a variety of these files from several sources, the layout of the contents is different, and the maximum record lengths are different to. They all have the following in common:

Wednesday, June 21, 2017

Happy birthday AS400

happy 29th birthday as400

Today is the 29th anniversary of the launch of the AS400. It was June 21, 1988, when IBM announced their latest midsize server. You can see a video recording of the announcement here.

At its launch the AS400 server and its OS400 operating were ground breaking.

Tuesday, June 20, 2017

IBM VP reaffirms commitment to RPG and IBM i

ibm vp reaffirms future of rpg

In my opinion it is always good when IBM publishes something about their future commitment to PowerSystems servers, IBM i operating system, and the RPG programming language. Steve Pitcher sent me a copy of a letter, from June 9 2017, by Steve Sibley, the Vice President of IBM Cognitive Systems, that hits this trifecta.

At the start of his letter Steve leaves us in no doubt IBM's regard for RPG:

Wednesday, June 14, 2017

SQL Views for Authorization Lists

views for authorization lists to view user authorities and objects belonging to them

Many companies that use IBM i secure their objects using Authorization Lists. The Authorization List is its own object type, *AUTL. If you have not used or heard of them before I found this good description of them in IBM's KnowledgeCenter.

Like a group profile, an authorization list allows you to group objects with similar security requirements and associate the group with a list of users and user authorities.

Authorization lists provide an efficient way to manage the authority to similar objects on the system and aid in the recovery of security information.

I am not going to discuss in this post how to create Authorization Lists, how to add objects to the lists, or anything similar. I am just going to describe two new SQL Views that were added in the latest IBM i Technical Refreshes, 7.3 TR2 and 7.2 TR6:

Tuesday, June 13, 2017

Using SQL to load data into a test instance

using 3 part sql path to update test files

Today's post is written by David Taylor.

From time to time, we need to load data from a production system to a development system. To make the process more complex, the data often resides in more than one file. Using SQL, we finally found a way to load the data into multiple files from the same cursor. We include in the SELECT clause any fields from any of the files in the data set to refine the cursor. Once we have the right data set, we can use the final version for the multiple file loads. Keep in mind, we need the data from all three files for the process test to work correctly. We need not just any set of records; we need the data that matches the relationships and limits defined in the cursor. In the samples each file ends with a letter. That same letter is the first character for the field names. For example, FILEA has fields like AKEYFLD1, AFIELD1, and so forth.

Wednesday, June 7, 2017

Repositioning file pointer after EOF in CL

stop end of file from happening in a cl program

I am always grateful for the feedback I receive from you, the readers of this web site. There are many times you mention ways of doing things that either did not think of or was unaware of. Today is a good example. I have Andrew Norton to thank for bringing to my attention a command that is a better solution for the scenario I described in the previous version of this post.

Those of us who have programmed in CL have found that when read a file when the end of file has been encountered I thought there was no way I could use the file again in the same CL program. In RPG I could use the Set Lower Limits operation, SETLL operation to reposition the file pointer. But in CL I was stuck.

Tuesday, June 6, 2017

Display files in CL

how to code dspf in clp or clle

I could give this a subtitle: "How to put a bad date in a date field", but this is not the reason of this post, just an accidental discovery. Having previously written about how to use database files in a CL program I have to mention using display files in CL.

You cannot use CL for anything as complex as a subfile. But you can for what I call "report submission screens", you know the type: a screen is presented to the user, they enter the selection criteria they desire and press Enter, and a program is submitted to batch to generate either a paper report or an email attachment. My example display file has two record formats, and is very simple:

Wednesday, May 31, 2017

Using OPNQRYF for file I/O in CL

opnqryf and cpyfrmqry examples

In my last post about the various way I could perform file I/O in CL code I deliberately omitted mention of the Open Query file command, OPNQRYF, as I wanted to cover it in its own post. I am sure everyone who has been programming on the IBM i or its predecessors have encountered and used this command.

I used it too, but not anymore. In the days before embedded SQL become efficient and fast within RPG programs I would often use OPNQRYF. This command allowed me to select fields and records, sort the data in a different order to the files' access paths, etc. thereby reducing the amount of checking and processing the following RPG program would need to perform. But it was always slow as it used the Classic Query Engine, CQE, to access the IBM i's database. In version 7.2 OPNQRYF was moved from using CQE to the faster and more efficient, SQL Query Engine, SQE. While this did improve its speed and performance it is still more efficient, and in my opinion easier and clearer, to do the equivalent using SQL embedded in RPG.

I am going to be giving examples of how to use OPNQRYF to do some of the things I talked about in yesterday's post.

Tuesday, May 30, 2017

Read, write, and update a file in CL

equivalent of rpg read, write, update, setll, chain in cl

Someone messaged me telling me that there were not, in their opinion, many good articles about CL programming, and asked if I would write some. Personally I think the majority of IBM i developers under utilize this language, keeping it just as a simple control language. I use CL programs and procedures a lot, and wrote about my thoughts on the need to modernize your CL in a post last year.

Where to start? Coincidentally I received an email from another asking about reading, writing, and updating a file just using CL. Let's start with data base access. In this post I will use the RPG file operation's name and then describe how to do the equivalent in CL.

Wednesday, May 24, 2017

Determining the screen size

determine screen size if it is *ds3 24x80 or *ds4 27x132

Most of us are aware that TN5250 sessions (displays) on the IBM i can be configured in two sizes:

  • 24 x 80 commonly known as *DS3
  • 27 x 132 commonly known as *DS4

Most of the time IBM i operating system handles this for us, only displaying "wide" screens when my session is *DS4 and the information to be displayed is wider than 80 columns.

Tuesday, May 23, 2017

My favorite CL shortcuts

using cl shortcuts rather than long hand code

If I am going to write and give examples of programming in CL I need to start by explaining some of the CL language shortcuts I use. Perhaps "shortcuts" are not the right word, maybe "shorthand". Oh well, I am sure you will work out what I mean as you read this.

I use these shortcuts extensively in my CL code as for a couple of reasons:

  1. I think it is easier for someone else to understand the code I have written.
  2. It is just quicker to write, the faster I can write code the more I can get done.

I am going to start with my favorite three.

Wednesday, May 17, 2017

Defining LDA in free format RPG

how to define the lda in a free format rpg program

It has been pointed out that this web site lacks a good description of how to retrieve the data from the Local Data Area, LDA, in the post about defining variables in free format RPG. I agree, which is why I have written this about retrieving and updating the LDA.

I am going assume that we all know what the LDA is. If you do not then you can read IBM's definition of what it is.

There are two common ways to bring in and update the data from the LDA in fixed format RPG.

Monday, May 15, 2017

Why is my RPG program not writing to the file?

rpg output buffer

I have been asked this question a lot recently:

When I am in debug my RPG program does not write to the file. What is wrong?

I have decided to write this post so I can, in the future, refer people here.

Let me give a very simple example RPG program so I can explain what is going on.

Wednesday, May 10, 2017

Using SQL for message queue data

new view for viewing messages in message queues

One of the new enhancements that came with the latest TRS for IBM i 7.3 and 7.2 is a new SQL View: MESSAGE_QUEUE_INFO. This view returns one line for each message in a message queue, in a similar manner to what is returned by the Display Messages command, DSPMSG.

This will allow me to have a way to quickly, and easily, search a message queue for a message. I must have *USE authority to the message queue, and *EXECUTE authority to the library it is in. One thing to remember is that message queues do get full, and when they do messages from them will be deleted. Also certain message queues are cleared when the IBM i partition is IPL-ed. If you are looking for a message that may have been issued sometime ago you might have better luck searching the History log via SQL.

Wednesday, May 3, 2017

Viewing object authority using a SQL view

getting authority data from object_privileges view rather than dspobjaut or edtobjaut

One of the new SQL Views added with the latest Technical Refreshes for IBM i, 7.3 TR2 and 7.2 TR6, is going to be very useful to me. Once a year, at audit time, I am asked to produce a list of all objects within the production library list that are not adequately secured. With the new Object Privileges SQL view I can generate all of the information I need in minutes, using a few SQL statements. This is going to save me a lot of time and effort.

The Object Privileges view, OBJECT_PRIVILEGES, like many of the new Views is to be found in the library QSYS2. The information it contains is the same as the information I can generate using in the Display Object Authority command, DSPOBJAUT. Rather than list all of the columns I will list them when I use them in various SQL statements. If you want to see a full list of all the columns visit the link I have provided a link to the IBM documentation for this View at the bottom of this post.

Tuesday, May 2, 2017

Second parameter added to SQL's LTRIM and RTRIM

2nd parameter added to sql trim built in functions rtrim and ltrim

As part of the goodies that came with the recent Technical Refreshes, TR, was the addition of a second parameter to SQL's Left trim, LTRIM, and Right trim, RTRIM, built in functions. The second parameter has the same functionality as it does in CL and RPG's trim built in functions, when used trimming particular character from either the left or right of the string.

The syntax of these SQL functions is remarkably similar to that of the CL built in function:

Wednesday, April 26, 2017

Using SQL to aggregate columns into one returned result

aggregating multiple values into one result set using sql

There are times when I have want an easy way to put values from a file, or table, "horizontally" into one column rather than vertically with more than one row.

"Vertical" "Horizontal"
TYPE        THING
ANIMAL     ELK
ANIMAL     HAWK
ANIMAL     MEERKAT
ANIMAL     ORCA
TYPE       THINGS
ANIMAL     ELK, HAWK, MEERKAT, ORCA, SEA L
FRUIT      APPLE, FUJI, BLACKBERRY, GRAPEF
VEGETABLE  SQUASH

Wednesday, April 19, 2017

Min and Max built in functions added to RPG

using the rpg min and max built in functions

A pair of introductions to RPG as part of IBM i 7.3 TR2, and 7.2 TR6, are the minimum and maximum built in functions, %MIN and %MAX. The maximum built in function returns the greatest value in a list of variables and/or values. The minimum returns the lowest value from a list of variables and/or values. Otherwise the way they are defined and the rules governing them are the same.

To use these functions there must be at least two values, and they must be of the same type. What I mean is that all of the values must be some type of numeric, character, date, time, or timestamp. I cannot mix types in the values. Pointers cannot be used.

They are so simple I can go straight to examples of using them. Let me start with a variety of numeric data types:

Thursday, April 13, 2017

Change to SQLRPGLE compile listings

type of rpg used in sqlrpgle compile listing changed to free format

Something I have noticed since the installation of IBM i 7.3 TR2 and 7.2 TR6 on the partitions I use, is a change to the compile listings of SQLRPGLE programs.

The process to create an object from a SQLRPGLE source member is a multi-step process:

  1. The syntax of the SQL within the source member is checked to ensure it is valid.

Wednesday, April 12, 2017

Nested data structures in RPG

nested data structures with other data structures

I am grateful to the folks at RZKH for applying the PTFs for the latest TR for IBM i 7.3, TR2. I started looking at the new features and functions added to RPG as part of this TR and decided to write about those first (Note: Same functions are included in 7.2 TR6 too). There were three additions, the one that first caught my eye was the ability to nest data structures.

Nested data structures are only available when defining data structures in free format RPG. If you are still using fixed format definitions this is another sign from IBM that it is time to move to free format RPG.

Inserting a nested data structure is pretty much as I would have expected, see below.

Tuesday, April 11, 2017

End of support for IBM i 7.1 announced

Steve Will, chief architect for the IBM i operating system, announced in a tweet today that the end of support, EOS, for release 7.1 will be on April 30, 2018.

Tweet from Steve Will: One of the FAQs I receive now has an official answer: IBMi 7.1 EOS 30 April 2018