Saturday, July 20, 2019

Ocean’s TechCon 2019 in 6 days

In six days I will be giving my presentation SQL at Ocean UG’s TechCon 2019. I have my presentation finished, and I am ready to present it on Friday (July 26) at 1:00 PM in room 201. If you are attending this conference I hope you will consider attending my presentation.

If you are in So Cal there is still time to register to attend the best User Group conference in the West.

I hope to see many of you there!

Wednesday, July 17, 2019

Easiest way to retrieve the program's name in a CL program

this is the easiest way to get the cl program name

It has always been a bit of a pain to get the name of the CL current program using the Receive Message command, RCVMSG. Therefore, I was excited when I received an example program showing a much simpler way of getting the same information by using a Machine Interface program.

The program I am going to show needs to be created as a CLLE member, and compiled using the Create Bound CL program , CRTBNDCL. As the call procedure command, CALLPRC, is not permitted in CLP source members.

My CLLE program will be calling the MI program _MATPGMNM, it is important to notice that the program name starts with an underscore ( _ ). I cannot use a CALL command, I must use the CALLPRC command instead.

My first example is the simplest program I could write to call this MI program, and have the current program name and library returned.

Wednesday, July 10, 2019

Which users are authorized to these files and tables?

systabaut, system table authority, sql view

This post started as one idea, and the more I played it morphed into something else.

The starting point was the SYSTABAUTH view. This view displays the authority of all DDS PF and LF files, SQL DDL tables, views and indexes, in all libraries, except QTEMP. It contains the same information that is shown when I use the Display Object Authority command, DSPOBJAUT. As it is a view I can retrieve the results from SQL statements and manipulate it any way I desire.

In these examples I will be using a few of the view's columns:

  1. GRANTEE:  the user profile that has been authorized to the object
  2. SYSTEM_TABLE_SCHEMA:  the system name of the library that contains the object
  3. SYSTEM_TABLE_NAME:  the system name of the object
  4. PRIVILEGE_TYPE:  the various authorities granted to the user profile to this object

There are seven different types of privilege that can be given to an object:

Wednesday, July 3, 2019

Checking for Firmware updates

check for firmware updates

You might want to share this post with the System Admin of the IBM i you use, as this will probably more of more interest to him or her than to a developer type of person.

Wikipedia defines firmware as:

Firmware is a specific class of computer software that provides the low-level control for the device's specific hardware.

Wikipedia: Firmware

Despite the term "Firmware Currency" being widely used by many different hardware platforms I have been unable to find a definition. From what I can ascertain "firmware currency" appears to be the equivalent of firmware release.

With the latest Technology Refresh for IBM i, TR6, a new view has been added to allow us to compared the firmware fix level installed on the IBM i partition to the recommended fix level from the remote Fix Level Recommendation Tool, FLRT. To be able to use this view the IBM i partition being used must be able to access the internet and be able to reach the FLRT website:

Wednesday, June 26, 2019

It is now easier searching message files

new sql view for 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_INFO 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:

Monday, June 24, 2019

Friday, June 21, 2019

New release, IBM i 7.4, day

ibm i release 7.4 is available today

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:

Wednesday, June 19, 2019

New table function to break apart values in columns and fields

suing string able function to break long strings into smaller parts

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.

Monday, June 17, 2019

Online presentation is this week

This Thursday, June 20, at 11 AM PDT I will be giving an online presentation with New Generation Software (NGS). My part of the presentation will be about using SQL views to make it easier to get data from your IBM i database.

If you would like to watch the presentation live you will need to register on NGS's website here.

If you cannot attend the presentation at the given time, NGS will be recording the presentation and uploading it to their web site. You will still need to register to watch the recording.

I am looking forward to doing this, and I hope you will be able to attend the live event.

If you want to know what time 11 AM PDT is in your time zone click here.

Thursday, June 13, 2019

New subfields added to Program Status Data Structure

two new subfields added to the new 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.

Wednesday, June 12, 2019

Using SAMEPOS in data structures

using keyword samepos to position subfields 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.

Monday, June 10, 2019

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:

Wednesday, June 5, 2019

Using Data Areas with SQL

retrieving data from data areas using 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.

Thursday, May 30, 2019

Updated executive guide for IBM i

executive guided updated for IBM i 7.4

The latest version of IBM's "Executive guide to the strategy and roadmap for the IBM i integrated operating environment for Power Systems" has been updated for IBM i 7.4.

This is a document you should consider sharing with senior management to help them realize that this platform, PowerSystems, and operating system, IBM i, is not the "same old 400". It is something way better.

You can download the document by clicking here.

Wednesday, May 29, 2019

Using SQL to retrieve data from spooled files.

spooled_file_data sql table function to extract data from a spool file

One of the additions to IBM i with release 7.3 TR6 was a Db2 for i (SQL) table function that allows me to retrieve the contents of any spool file in my IBM i. I am sure that there are some people who are thinking "So what, I can do that with DSPSPLF"

Yes, I can view the contents of a spool file, but I cannot copy data from it. Why would I want to copy data from a spool file?

I am sure we all have reports in our ERP applications that the users would prefer as a spreadsheet. You are reluctant to change the ERP program as by doing so it will invalidate the support contract. Therefore, you copy the spool file to a physical file, and then parse the report's columns into fields, that are then written to the output file.

Using this new table function it becomes, in my opinion, a whole lot easier.

Friday, May 24, 2019

IBM i: A platform for innovators, by innovators


IBM i 7.4 is here! We are excited for this release and looking forward to the future of the IBM i platform as you continue to innovate with us. Here’s to 30+ years.

Wednesday, May 22, 2019

Using Check BiFs in CL

check and check bifs in cl

It has been several been several months since I last wrote a post about CL programming, therefore, I thought it would be a good excuse to share something I was using in a CL program I wrote a couple or so weeks ago. I am sure many have used the Check, %CHECK, and the Check reverse, %CHECKR build in functions, BiFs, in RPG to check a character string for the first place a certain character(s) is not. In the CL program I was writing I needed the same functionality.

IBM has been adding BiFs to CL that are similar to the ones to be found in RPG. Fortunately there are %CHECK and %CHECKR BiFs in CL too. The general format of them is similar to the RPG equivalent:

%CHECK(<test values> <variable> <starting position>)

%CHECKR(<test values> <variable> <starting position>)

I found I could use these BiFs in my CL in two ways:

Wednesday, May 15, 2019

Get information about a job, including current SQL statement

get_job_info to retrieve sql statement

I knew of the GET_JOB_INFO table function, but having played with it I did not see a reason why I would use it, I preferred the ACTIVE_JOB_INFO table function.

It was not until someone pointed out to me that I could see the current SQL statement being executed in a job that my interested with GET_JOB_INFO was piqued, especially as this would allow me to see what those data base server jobs are doing.

I decided to compare what GET_JOB_INFO would show me if I executed the following statement via two ways:

  1. Using Operations Navigator's "Run SQL Scripts" client
  2. Using the STRSQL command

Friday, May 10, 2019

Today is TR6 day

ibm i 7.3 tr6 is out today

Today, Friday May 10, 2019, the new Technology Refresh for IBM i 7.3 is available!

When it was announced last month I went and picked what I thought my favorites would be. You can read about them here.

The PTFs for TR6 are:

At present I cannot find any mention if this PTF included the Db2 for i, RPG, etc enhancements too.

I will update this post as I find more information.

These are what i thought look to be the most interesting enhancements with TR6:

We have to wait until Friday June 21, 2019 for the the new release, IBM i 7.4 .

Wednesday, May 8, 2019

Parsing SQL statements using a table function

parse_statement sql table function

Someone brought to my attention a Db2 for i table function that parses the names of the tables and columns used in string passed to it. I have to admit I had not heard of this, therefore, I decided to test out what kinds of information it could return.

The PARSE_STATEMENT appears to have been introduced as part of the IBM i release 7.2 . It takes a string that contains a SQL statement and returns the columns, objects, etc. contained within. The basic syntax is:

SELECT * FROM TABLE(<sql string>)) AS A

For example:

Monday, May 6, 2019

Time to download ACS

access client solutions or acs for ibm i

April 30 marked the end of IBM's support for one piece of IBM i software we have all used, Client Access. Its replacement is Access Client Solutions, ACS. It does everything that Client Access did, including 5250 emulation.

Why have I mentioned this?

I often get asked which 5250 emulator people should to attach to free IBM i partitions like RZKH's free IBM i partition PUB400.COM. The problem has been that the best emulators were not free.

ACS can be downloaded for free. And once installed its 5250 emulator can easily be activated to connect to servers like PUB400. Now you can have a quality 5250 emulator that does so much more than those others you have used in the past.

Wednesday, May 1, 2019

Conditioning display file size

24x80 *ds3 and 27x132 *ds4 display size

In the past couple of week I have been asked by several people how to condition whether a display file is shown in *DS3 (24 rows x 80 columns) or *DS4 (27 x 132) format. Rather than writing the same message to each one, I have decided to write this post so I can direct them to it.

These days I doubt if any of us IBM i developers are still using "dumb" workstations, we are all using PCs. In the settings of our 5250 emulator there is a configuration setting so we can set our emulated session to have a screen size of either 24x80 or 27x132. Personally I always configure my sessions to be 27x132.

You can see, and change, this setting depending on the emulator application you use.

Wednesday, April 24, 2019

More about IBM i version 7.4 and 7.3 TR6

more new features in ibm i 7.4

After all of yesterday’s excitement with the announcement of a new release of IBM i 7.4, and a Technology Refresh for 7.3 TR6, I have had a chance to review the documentation and these are the things that caught my eye:

The links below are to the relevant pages in the KnowledgeCenter.

SQL: New

Tuesday, April 23, 2019

IBM i version 7.4 is announced

Update at 5:00AM (following day)

I went through and picked out what I thought be my favorites here

Update at 10:09AM

Today is announcement day as IBM has announced a new release of IBM i, 7.4, and a new Technology Refresh for IBM i 7.3 .

Since my first post this is what I have found:

The new release, 7.4, will only be supported on the PowerSystem servers with the Power8 and Power9 processors.

The availability dates for these are:

Wednesday, April 17, 2019

Discover size of library using SQL

get library size using sql

Someone asked me if there was a way, using SQL, to determine the size of a library?

This is a trick question as few people realize that the size of a library and the total size of all the objects within are different.

The questioner clarified that they wanted to know the total size of all of the objects in the library. But I gave him examples showing the difference between the two.

He was using the Display Library command, DSPLIB and was trying to find a way to get the same total from this command in a way it could be retrieved programmatically.

In the days before all the cool Db2 for i views, table functions, etc. I would have used the Display Object Description command, DSPOBJD. To get the size of the library I would have used the following:

Monday, April 15, 2019

Presenting at Ocean UG

Tomorrow, Tuesday April 16, I will be one of four presenters at the Ocean Users' Group meeting. This is the first time I will be presenting to an IBM i user group, and I am looking forward to it.

If you can attend the event is at the National University in Costa Mesa. Dinner is at 5:30 PM, with the presentations starting at 6:30 PM.

Wednesday, April 10, 2019

Validating SQL statements

qsqschks api used to validate sql statements

This post is about an API I stumbled across when looking for another. The Syntax Check SQL Statement API, QSQCHKS, will validate any SQL statement passed to it. I can see this being useful when I build a SQL statement in a variable before executing it. If I was to validate the statement it would allow me identify any errors I may have made.

When I show my code, below, you might think it looks complicated, with several strangely names data structures, which is why I have placed it in its own procedure with just one parameter passed to it and one returned. K.I.S.S. .

I have created two procedures to achieve my goal: to return a character variable containing the SQL error message's text. To this end I have created two procedures, the first in RPG, the second in CL. I could have combined them into a single service program, but as this is an example I left them as modules, RPGMOD001 and CLMOD001, to be bound to the calling program at program creation (compile) time.

Thursday, April 4, 2019

Happy St Isidore of Seville day

Today is the day of the Catholic church's patron saint of computers, computer programmers, and the internet. St Isidore of Seville was a sixth century scholar and bishop who was given this special role in 1997 by Pope John Paul II.

Therefore, if you are having one of those days where every is just going to pieces it might be time to ask for divine help with a quick prayer to St Isidore.

You can learn more about this saint clicking this link here.

Wednesday, April 3, 2019

Using FTP with IFS files

ftp with files in ifs

In previous posts I have written about using FTP to copy files from one IBM i partition to another. Almost all of the examples I gave were for transferring of a file, or files, in what I call the "IBM i environment", good old libraries and files.

Earlier this week one of my colleagues came to me and asked if there was a way to use FTP to copy a file in the IFS of one IBM i to the IFS of another partition. I looked back at the posts I had written and realized that I did not have a good example of how to do this. I thought I would share what we played with so he could understand what to do.

Before we get started I need to explain that FTP with IBM i has two naming conventions for the file's path:

Wednesday, March 27, 2019

Delaying a job for a fraction of a second

pause a program for a fraction of a second

Someone asked me if there was a way to pause a job for a fraction of a second. Their colleague had advised them to create a Do-loop that would be performed a certain number of times and that would be "good enough". I was very pleased to learn that the person who asked this question was not satisfied with that answer, and correctly thought that there must be a better way.

I am sure we are all know of the Delay Job command, DLYJOB, but it will only delay a job for whole seconds and not fractions. Fortunately IBM i has two external C functions that can be used to delay a job:

Wednesday, March 20, 2019

API to check if debug is active

determine if debug running

There are some APIs I stumble across and I am left wondering why they were created? The API I will be writing about today, QteRetrieveDebugAttribute, is one of those. This API is used to determine what the debug attribute values are for a job when it is being debugged. I am not going to list which debug attributes they are as it is not relevant to what I am trying to determine. If you are interested in learning what they are click on the link to IBM's documentation at the bottom of this post.

In this example I am just interest to know if the job is being debugged.

I am going to have a procedure within a module call the API, this way I can either bind it into the calling program or included it in a service program, which is my preference. Let me start with the module:

Thursday, March 14, 2019

IBM i 7.4 section now in KnowledgeCenter

ibm knowledge center page for coming ibm i release 7.4

In January I found signs that a new release of IBM i would happen this year. This week I found another sign when I discovered that the KnowledgeCenter now has a section for IBM i 7.4.

Alas, it is just a "place holder", with no information is within it, but it is another sign of the pending new release.

If you are interested in visiting the new section click here.

Wednesday, March 13, 2019

Using SQL to check if a batch job is active

sql to find if job is active in subsystem

A colleague asked me if there was an easy way, using SQL, to tell if a job was active in a subsystem. This subsystem, I am going to call it TESTSBS, should contain two jobs that remain active all day, copying and sending data between various IBM i partitions. During the day-end process the jobs are ended, and then resubmitted after the backups.

Recently there have been problems with these jobs. If one of these jobs errored the system operators would answer the message with "C", which would end the job and the data would not be transferred until someone else noticed. Or the jobs would not end during the day-end process, therefore, after the backups completed two new versions of the job would be submitted resulting in four jobs, two of each.

My colleague had looked into ways of determining which jobs were active in the subsystem. During the day if one of the jobs was missing it could be resubmitted. After the backup finished only if the job was not active would it be submitted. All of the methods she had come up with to determine if the jobs were active were, in her opinion, too complicated. Which is why she came to me.

Tuesday, March 12, 2019

Does end of Google+ mean the end of this website?

Over the past week I have received several communications asking if the end of Google+ means that this site will also be closing.

This site is hosted on another Google product, Blogger. Google's commitment to the Blogger platform continues unabated. There have been some minor changes to Blogger because of Google+'s demise, but none of these will effect your viewing pleasure of this website.

If you have included me in your Google+ circle and want to continue that relationship feel free to send me a Facebook friend request.

Wednesday, March 6, 2019

Copying part of an existing spool file to a new one

copying part of one spool file to a new one

I was asked this week how to send the last few pages of a report to a remote output queue. The queue had been configured in a way that even though I could change the range of pages to print, number of copies, etc. despite this a single copy of the entire report would be printed. Such a waste of paper when the report was 450 pages.

The way I overcame this is to create a new spool file, copying data from the original. In a previous post I have shown how to copy multiple spool files and have them print as one. The spool files are copied to a physical file, then the data from the physical file is copied back to a printer file. If I only want a few pages I just need to find the relative record number of the start and end of the section I want to print, and then enter that data when I use the Copy File command, CPYF. For the new spool file to look like the old I also need to know the page width of the original spool file, the characters and lines per inch, CPI and LPI. I can find these myself by looking at the original.

It is not a big deal to do this manually, but my philosophy is if I need to do it more than a few times then it is best to write a program to do it for me. This example program is based upon a program I wrote to do this, make a new spool file that contains a subset of some of the pages from the original spool file, that can then be sent to the remote output queue.

Wednesday, February 27, 2019

Finding really big objects in my libraries quickly

quickly finding size of objects in library using object_statistics

The shout goes out: "The IBM i is at 92% of disk capacity! Delete as much test and work stuff as you can!"

We don't have time to run the best way to find the largest objects on this IBM i. At this point I am just concerned with the objects in my personal, work and test libraries.

I don't want to use the Display object Description command, DSPOBJD, as I will have to build a file, to then query, and that will only add to the amount of used disk space.

Fortunately Db2 for i provides me with a solution I can get data from immediately. In a prior post written about using the OBJECT_STATISTICS table function to find objects that have not been used for a long time. I can use the same table function to identify find the largest objects in my personal, work, and test libraries.

Friday, February 22, 2019

Another sign a TR for IBM i 7.3 is imminent

IBM has started teasing us with information about a new Technology Refresh.

Yesterday a new page was published in the "IBM i Technology Update" wiki for TR6 for IBM i version 7.3. At present the page is just a place holder with the words "stay tuned..." on it. You can visit it here.

What I find interesting is that there is no mention of an equivalent TR for IBM i 7.2 . This would be TR10.

Wednesday, February 20, 2019

Capturing system statistics using SQL

system_status_info sql view rather than wrksyssts command

I thought this question would be a good reason to introduce another Db2 for i view that I find useful when monitoring IBM i:

I would like to collect the percentage CPU used and percentage system ASP used (from WRKSYSSTS) every day at 00:00 hours and save it in an outfile or print it to an spool file or send the values as a message to a user.

I could dump the contents of WRKSYSSTS to spool file, copy that to a data file, and then extract the information I want from the data file, or I could use an API. But why would I bother to go through all that effort to get the same information I can get from the Db2 for i view: SYSYTEM_STATUS_INFO

What information do I want? On the WRKSYSSTS display I only want the following:

Wednesday, February 13, 2019

Returning rows where the last few characters are not numeric

checking last 3 characters if not number

Sometimes I am asked a question by a colleague I think would be a good example to share in this blog. This week I was asked was there a way in a SQL statement to only return the records from a file where the last three positions are not numeric. For example:

  • A2CDEF = Include
  • ABC4EF = Exclude

There is an added complication as the string I need to test is contained within a ten long character field, but it can be of any length 1 - 10.

I quickly made a test file, TESTFILE, that I can use for these examples. The file contains the following records:

Wednesday, February 6, 2019

Screen at a time subfile using SQL

screen at a time subfile written using sql

In my last post I gave an example of how to write a "screen at a time" subfile in RPG. When I finished writing that post I thought to myself how would it be possible to do the same using SQL.

There are two ways I could read, Fetch, data from the input file:

  1. Single row fetch
  2. Multiple row fetch

The single row Fetch is almost as inefficient as a RPG read operation, retrieving one record at a time from the file. The multiple row fetch is more efficient and the method I am using in this example.

The data file this program uses, PERSONP, is the same as in my last post, therefore, I am not going to describe it. If you care to refresh your memory then you should go to that post.

I made three changes to the display.

Monday, February 4, 2019

Presenting at OCEAN

I will be one of five presenters at the April 16 OCEAN user group meeting. I have been given 10-15 minutes, and have decided to spend that time giving some examples of what I consider are ways to get the most from SQL.

The meeting starts at 5:30 PM, and am I to assume as my name is first on the list I am the first person presenting?

Details of April's meeting.

I hope I will see you there!

Friday, February 1, 2019

Let's make February IBM i user group month

You regular readers know that I believe that we should all get involved with our local IBM i user groups.

To help you find your nearest group I have a list of all the user groups I have found on a page in this website:

I review and update the list with the following information on a regular basis:

  • Websites
  • Facebook groups
  • LinkedIn groups
  • Twitter accounts

I am sure this is not a complete list of all the IBM i user groups in the world. I need your help to add to and revise this list. If you know of any user groups not on the list, need their information updated, or any of their social media accounts please use the Contact Form, right, to send me the information you have.

For example, in India there are many of IBM i folks. Are there really no user groups there?

Let's make February IBM i user group month!

Wednesday, January 30, 2019

Another type of subfile: screen at a time

screen at a time subfile

All of the examples of subfiles I have given in previous posts have been "load all" subfiles. The subfile size is at the maximum, 9,999 records, and the entire subfile is loaded all at once. When loaded the display file controls the scrolling up and down within it, without any need for code in the program.

If give a "position to" field when a value is entered in it the subfile is reloaded starting at the nearest matching record and continuing onto the end of the subfile or available records from the input file. When I have presented this to users they have understood that if they enter "Hutchinson" then the subfile will start at the closest match. If they wanted the name "Hughes", rather than "Hutchinson", they would change the value in the "position to" field rather than expect to be able to page up through subfile records less than the value "Hutchinson" until they reached "Hughes".

In my experience of working with users, in many different companies, they understood and have been satisfied with this functionality. When I have given examples in this blog I receive messages telling me that this is not acceptable, the users must be able to page up to values less than "Hutchinson". I have until now ignored these complaints as they are from IT people, not from the people who use these subfile programs every day.

Today I relent, and am giving my version of a program that would allow people to page up to records before that entered in a "position to". To accomplish this I need to use a different type of subfile, a "screen at a time" subfile. This type of subfile will only load the same number of records that are displayed on the screen. If either the page up or page down keys is pressed the program has to reload the subfile with data.

Sunday, January 27, 2019

More proof the new release of IBM i is coming

I mentioned earlier this year that there were signs that there would be a new release of IBM i announced this year. Images from IBM showed arrows to indicate that IBM i Next would happen in 2019.

Arrow shows IBM i next coming in 2019

I was curious whether IBM i Next would be another version 7 release, 7.4, or a totally new release, 8.1?

Poking around in the IBM KnowledgeCenter I found the answer, there is now a section of the site for IBM i 7.4. It does not, as yet, contain any information, it is more of a "place holder".

Wednesday, January 23, 2019

Padding columns using SQL

padding fields using sql

On cold morning in So Cal (we do get them) I was asked: "Is there any way to pad the values in FIELD1 in TESTFILE with zeroes?".

FIELD1 is a 20 long alphanumeric field in TESTFILE, despite its size only the first six characters are ever used.


My mission was to add leading zeroes to the values that were not six long to make them that length.

Wednesday, January 16, 2019

Create SQL tables, views, and indexes with long names

create long and short names for sql tables, indexes, and views and their columns

I received a question asking me how to create a table with a long name that also had a good short name, and the long column names within the table would have meaningful short names too.

I can easily create a table with a long name and long column names:

04  ) ;

If I use PDM to look for the object I just generated it is not clear that this is the table from its generated short, or system, name.

Thursday, January 10, 2019

Re-enable disabled IFS user id

enable disabled netserver ifs user id or profile

When I am mapping an IFS folder to a Windows network drive I know that there are times where I will perform a typo on my password and the IBM i operating system will block me. This is where it gets interesting, I have disabled the user id I use trying to perform this connection, but I can still signon to the same IBM i.

When I create a user profile the command appears to create two:

  1. What I call the "IBM i" that I use to signon to a 5250 ("green screen") session.
  2. A NetServer user profile, that is used when I use the NetServer functions like mapping a Windows network drive to an IFS share.

Wednesday, January 9, 2019

Create and share IFS folder with Windows

creating ifs folder and sharing to microsoft windows

In my last post I wrote about making it easier to copy IBM i files to an IFS folder. I have my personal IFS folder set up as a Microsoft Windows network drive, which allows me to open the mapped folder in Windows Explorer, and just drag-n-drop files from the mapped network drive into folders on my PC. Here I am going to describe how you can do the same.

I am going to use Client Access's Operations Navigator, I could use IBM Navigator for i instead, but I have found that not many sites have not enabled it. To be able to do what I am describing you are going to need the full version of Operation Navigator, and the appropriate authority to create folders and shares in the IBM i partition you use. If you do not have the full version of Operations Navigator and/or you do not have the necessary authorities feel free to share this with your System Operation or Tech Support person who can perform these steps for you.

This process can be divided into three steps:

Tuesday, January 8, 2019

Making copy a file to the IFS easier

automate copy of file to ifs

Often I find it easier to analyze the contents of a file using Microsoft's Excel than using the IBM i based tools. I take the file, or table, and copy it to my folder in the IFS, and then copy it from there to my PC. A colleague asked how did I do this so quickly, and having showed him how I do, he recommended that this would be a good subject for a post in this blog.

I am surprised how few developers I talk to build their own set of programs to automate tasks they perform every day. Too many times I see these people type in command names, prompt with F4, and fill in the same parameters time and again to do the same task. I admit I don't want to do that, if I need to perform the same task more than just a few times I will create a program containing all the commands I need. Then I can call the program with just a few parameters, and the program will do everything. If I want to copy a file to the IFS I just need the file and library, the rest will always be the same.

Wednesday, January 2, 2019

Creating a screen with two side-by-side subfiles

2 subfiles next to each other

I have written about subfiles, and even a screen with two subfiles on it. When I was asked about a screen with two subfiles, side-by-side, I was intrigued how to do it.

The first problem to overcome is that if I code two subfiles, one to occupy the left side of the screen and the other the right, whichever subfile's control record I write last will overlay the other.

After using Google I could find reference to an example of using DDS windows to contain the subfiles, but the links to the article only returned a "page not found". With that hint I created the following.

Let me start by saying that this display file is a "stripped down" version. I have removed a lot of the things I put in display files so not to clutter this example. Let me start with the file level keywords and a record format I have called HEADER.

Tuesday, January 1, 2019

Welcome to 2019

A new year has started, and I am always excited to look back on the old year and forward to what the new year will bring.

The past few years have been an exciting time for IBM i community. Just in the life of this blog I have seen RPG undergo a radical make over, and numerous wonderful things added to Db2 for i, or SQL.

You feel the same as the most popular articles from 2018 have been:

  1. Creating a XML file
  2. Processing simple XML using XML-INTO
  3. Using RPG data structures with SQL insert and update
  4. Displaying more than one subfile at a time
  5. Copying any data to and from a file in the IFS

Have you liked others? If so which ones?