One of the first things I do when I first signon to an IBM i is to check what release of operating system it is running. Armed with that information I then know what version programming tools and language features I will need to use.
Fortunately there is a quick way and a not so quick way, that takes just a few more key strokes.
| Server name | Operating system |
Release | Available date |
End of Support date |
| AS400 (Application System/400, 1988-2000) |
OS400 (1988-2006) |
Rel 1 | August 26, 1988 | ? |
| R01M00 | ? | ? | ||
| R01M01 | ? | ? | ||
| R01M02 | November 25, 1988 | ? | ||
| R01M03 | ? | ? | ||
| V2R1 | May 24, 1991 | June 30, 1994 |
In several previous posts where I have used the DSPFFD command, see here and here, or the QUSLFLD API, see here, to retrieve the Column Heading of fields in a file someone has commented that I should retrieve the column headings using the SYSCOLUMNS file. In the scenarios described in those post using SYSCOLUMNS would not have worked as it only contains data for files not in QTEMP.
SYSCOLUMNS resides in the library QSYS2, and is a SQL view built over the physical file QABDIFLD. It contains the field/column information for all the files and tables in every library on your IBM i, except those in QTEMP. I am not going to list all the columns in SYSCOLUMNS as the IBM documentation does a good job doing it here.
I am going to show two scenarios how I could use SYSCOLUMNS:
The discussion of whether to use the alias names for files’ fields has become a lengthy here amongst the programming staff. The major argument against has been that there does not appear to be an easy way to perform input or output to record formats in display and printer files. I needed to find a simpler way to define the alias and its uses.
Fortunately IBM came to my aid with PTFs to make the using of alias simple in IBM i 7.1 and 7.2. In my opinion this is the way alias handling should have been from the start.
Node.js was promised as part of Technical Refresh 9 for IBM i 7.1 and TR1 for 7.2. It was released yesterday, December 15 2014, as a new License Program Option.
Node.js is an open source runtime environment for server side and network applications. Its applications are written in JavaScript using Google's V8 JavaScript engine to execute code. Its runtime can execute on OS X, Microsoft, Free BSD, Linux (which can be hosted on a PowerSystems server along with IBM i) and now IBM i.
I have added a link to COMMON Luxembourg’s website to this site's IBM i user groups page.
Their site is at www.common.lu (you do need the www)
If you know of a user group that is not listed on the user groups page please use the Contact form, on right, to send me its details.
After being teased last week with a video of Steve Will, Chief Architect for the IBM i, talking about IBM i 7.2 and TR1 the first Redbook for 7.2 was been released yesterday afternoon.
It can be download from IBM's Redbook website here (17.3 MB).
It has chapters covering the following subjects:
At 416 pages it is going to take a lot of bedtime reading to get through it.
I often need to find out if a particular PTF has been applied to the IBM i I work upon. Most of the time I can use the DSPPTF command to display the PTFs, but there is one instance of IBM i I am not authorized to that command. I think the head IBM i operator is tired of receiving emails from me asking him to check if a certain PTF has been loaded and applied. I needed to find another way I could display a PTF and whether it had been applied on my own.
Fortunately there is a SQL view I can search for this information. According to the IBM documentation this view has been available since IBM i 6.1. The only servers I have access to are running 7.1 and 7.2, so I cannot vouch for whether this will work in 6.1.
I received a request from the engineers: One solvent they use has been place on the "restricted list" by the state and could no longer be used. Could I create a program to replace the solvent's code in their files with the replacement solvent's code?
It gave me a reason to use the %SCANRPL, 'Scan and replace character', built in function that was introduced in IBM i 7.1, and made me think how this could be accomplished with earlier releases too.
The original contents of this page have become obsolete, go to this page for up-to-date information.
I have a couple of communications asking about how to "correctly" code a data structure to receive the data from and update a data area in the latest, all free, RPG. One of person even sent me their code, which I will be using for the basis for my examples.
While I am not sure if there is just one "correct" way to do this I will be giving my version of the following three scenarios:
I am not going to describe the basics of how to code Data Structures in free format in this post, as I covered this in Defining variables in RPG all free.
The original contents of this page have become obsolete, go to this page for up-to-date information.
In my earlier post Subroutines versus Subprocedures I described some of the advantages of using subprocedures rather than subroutines. In it showed how you could use, what I call "open" subprocedures. An "open" subprocedure does not have a procedure interface specifications, therefore all of the variables defined in the main line/body are available in the subprocedures, and if you change the contents of one of those variables the changed value is seen in the main line/body.
The other type of subprocedure I call "closed". The subprocedure can be passed parameters and can return a single variable, which can be data structure with multiple subfields. A "closed" subprocedure is should be placed in a separate module, therefore, they are now available for multiple program to use. As the main line/body can only change the variables they pass to the subprocedure and the subprocedure can only return one pre-defined variable there is no accidentally changing to the values of the variables.
Many people commented on that post explaining the advantages of the "closed" subprocedure. I do agree and below I will describe how I code them and their advantages, including one potential gotcha.
I was recently browsing a French IBM i website when I found links to two User Groups in France:
They share at forum at www.volubis.fr/forum/
I have added these links to this site's IBM i user groups page. You will find a link to the user groups page at the top of every page of this website.
Are there other user groups/clubs in France or anywhere else in the world? If you know of any not on the user groups page please use the Contact form to send me their details.
I was at a presentation given by Partner/400's Susan Gartner and Jon Paris where, amongst many things, they discussed replacing subroutines in your RPG code with subprocedures.
Having spent the last few weeks using subprocedures in place of subroutines in any new programs I have written, I have been won over to the subprocedure side of the argument.
Let me clarify this post is not about taking code from an existing program and placing it in an external procedure. It is about the differences I found using in-line subprocedures when compared to in-line subroutines. So what were the major differences I found?
I am writing this post in response to several comments that have been made on posts in this blog, discussions threads I have seen in Facebook, and discussions I have had with work colleagues.
The Create Duplicate Object command, CRTDUPOBJ, is used to create a duplicate any object, not just files. But in this post I am just going to cover what I call "data files", physical and logical files.
The myths I have recently encountered are:
There have been no updates to the IBM i Virtual User Group since May of this year, and I have been concern that the group had "died".
After several unsuccessful attempts to make contact with the VUG via their web page, I decided to email Steve Will, Chief Architect of the IBM i operating system, and ask him the status of the group. Below is his reply.
Simon, I got the chance to ask this question to Alison Butterill. She tells me the person who was responsible was an IBMer who is no longer with IBM. She's in the process of looking for a leader -- it could be someone outside IBM, too, since this is really intended to be a User Group.
It’s good news for all of us, and I hope the VUG will be active again soon once a new leader is found.
I received a message from KaBrito about the post Defining SQL tables using a reference file.
Question: How would you do that in some type of a Source Control Management application?
Promote the table to production and then run the Alter table command?
With the move from DDS files to SQL DDL tables this is going to become a frequent asked question.
The web page that was originally linked to no longer exists, therefore, rendering this page obsolete.
Addendum: There is a newer post that includes the new ZLIB save algorithm, you can read it here.
Something that was added to the SAVOBJ and SAVLIB commands in IBM i release 6.1 V5R4 was the ability to compress data more than before when saving to a save file. Previously the only options that were available for the Data Compression parameter was *DEV, *NO, and *YES. With 6.1 V5R4 three new compressions levels were added:
Note: These three values are not valid when saving to tape.
What is the benefit of using any of these Data Compression values?
The new Technical Updates for the latest two releases of IBM i, 7.1 and 7.2, will be available on November 11, 2014.
TR9 for 7.1 includes updates to:
Yesterday I showed how to create SQL tables using a reference file. Tommaso Arcieri message me with another approach, defining a table based on another file or table.
In this example I am going to use the physical file TESTFILE as the original reference file.
A R TESTFILER A FLD001 10 A FLD002 3P 0 A FLD003 10 A FLD004 5P 0 A FLD005 9P 2 A FLD006 7P 2 A FLD007 1 A FLD008 1 A K FLD001 |
This just a small file of only eight fields, so it would not be a big deal to enter each field name. But for a field with many more fields it would be a pain to do so. Fortunately the approach Tommaso showed me should be used if the original reference file has only one field or hundreds.
I am old enough to have been working with IBM midrange computers before the introduction of the AS400 in 1987, see 25th anniversary of IBM i (AS400). Twenty six years ago I was a programmer on the System/36 using RPG II. I can remember the first day I started programming on the AS400 how I was blown-away by the relational database, which was not present on the System/36. The files I created could refer back to a reference master file, and I would no longer have to worry about ensuring that all the invoice number fields were the same size, etc.
With the modernization drive to replace DDS files with SQL tables how do I do the same as a reference file in SQL. All the examples I have seen show the DDL (Data Definition Language) for the SQL tables where the data type and size of the columns (fields) have been defined. In my opinion this is a step back from the beauty of the relational database.
So how can I use a reference file in SQL?
I could use the method I described in the post Creating a SQL table on the fly, but there must be a better way.
IBM has published a new Redpaper today called Tools and Solutions for Modernizing Your IBM i Applications, it is a companion to the earlier Redbook Modernize IBM i Applications from the Database up to the User Interface and Everything in Between, from July 2014.
It describes independent software vendor (ISV) and Business Partner tools that can be used to modernize your IBM i applications.
It includes the following chapters:
You can download it, as a PDF, here.
You can find a link to its companion Redbook here.
After reading the post Easy way to convert CSV file to DDS file Martin Coates emailed me with the following question:
How do you map a folder in the IFS and share is on the pc? I am an As400 “purist” but more and more I find programming requests overflowing into the pc domain.
In this post I will discuss how to create a folder in the IFS, and then how to share it as a folder that can be accessed by a Windows PC. If you already know how to create folders in the IFS feel free to skip to the second part.
This post is inspired by a situation that I encountered a couple of weeks ago that left me flabbergasted. It started with a phone call from a department head complaining that another department was eating into his department's time to run a report. At first I was confused, and after talking to three department heads I discovered that each one has a time they can run this report. Department A can run the report with any selection criteria they desire from 8:00 – 8:59 AM, Department B can do the same from 9:00 – 9:59 AM, and Department C from 10:00 – 10:59 AM. Anyone can run the report in the afternoon, but only after calling the other departments to make sure they are not running the report at the same time.
Why was this an issue? In this job the programmer who had created it used a work file that was in a production library. Therefore, only one person could run the report at a time to ensure that only one department's data was included in the report. If two people ran the report at the same time the work file could contain mixed data from both jobs. I also found that when the job completed the work file was not cleared, still containing all of the data from the last run, wasting disk space.
The programmer who created these programs has retired so I was not able to ask the question I wanted to: Why?
Looking at library list for the jobs from this application I found that QTEMP was in the library below all of the production libraries. Could this be the reason why?
I was recently asked the following question, via Facebook:
How we can read a particular member of physical file thru SQL EXEC in RPGLESQL program ?
Others suggested using "Override with Data Base File" (OVRDBF) command, which could be used. But there is a way to do this just in SQL.
RCAC stands for "Row and Column Access Control", which is one of the features introduced in release 7.2 to DB2 for i (SQL).
Since the release announcement I have seen many questions asking what it is and how to implement it. IBM must have seen the same and have created a new Redbook called: Row and Column Access Support in DB2 for IBM i. You can download it here.
To accompany the release of the Redbook is this brief video of Mike Cain, one of the members of the DB2 for i Center of Excellence team, giving an overview of RCAC.
In the past two posts about this subject I discussed how to manually copy files from one IBM i to another using FTP, File Transfer Protocol. In this post I am going to demonstrate how to create a program to start FTP, execute FTP subcommands, and then produce a printed log of what happened.
The program I will show is very simple and can be used for any types FTP subcommands. In this post I will use the example I discussed in Part 2, to copy a number of files from one library on QSRV1 to another library on QSRV2, and then call the program on QSRV2 to process the copied files.
Having gone through a lot of the basics of using FTP, File Transfer Protocol, on an IBM i in the post FTP i-to-i part 1, in this post I will describe how I can transfer physical files, using FTP, to another instance of IBM i, whether it be another partition or another server.
In this scenario I have a job on QSRV1 that generates physical files that need to be transferred to QSRV2 to be processed. If this was a real job I would automate the FTP transfer, I will explain how to do it in Part 3. But in this post I am going describe how I would do it manually, one FTP subcommand at a time.
FTP, File Transfer Protocol, is probably the most widely used way to transfer objects between computers. It should come as no surprise to you that FTP has been available since the AS400 era.
I work in an environment with 11 IBM i partitions and servers, and I can spend a lot of time moving objects between. To move objects between partitions on the same server I use the SAVRSTOBJ and SAVRSTLIB commands. To move objects from one server to another, especially if they are in different geographical locations, I use FTP.
Why FTP rather than SNDNETF or SAVRSTOBJ? In my experience when moving very large amounts of data between servers it can be several hours quicker to FTP the file rather than use the other methods.
This post is going to first of several posts explaining how to FTP a file or files from one IBM i server to another. This method is not secure and I only do this within my company's network or while part of a VPN. I do not recommend doing this in an unsecure environment, i.e. over the internet while not being part of a VPN, as it can be "sniffed" and the data compromised. If you do want to send data via an unsecure environment then use SFTP, Secure FTP.
In May I posted two examples of how to retrieve the Column Headings for fields in files to be used as the column headings in a CSV file in the IFS. The first example, Adding Column Headings to a file in the IFS, I used DSPFFD. I was sent an alternative method that I gave in a later post, Adding Column Headings to a file in the IFS, continued, which used SQL to extract the information from the SYSCOLUMNS table.
Shortly after the second post I received a message from David Gomes. In it he explained a third method:
Whenever there is an API available to get the data, you should be using that and not running a CL command to produce a output file to read. In this situation I would recommend using the List Fields API (QUSLFLD). This will allow you to retrieve the Field name, Column or Text descriptions for the field. Using the X-Ref tables like SYSCOLUMNS is useless if the file is in QTEMP.
As I have explained how to use a List API, like QUSLFLD, that outputs to a User Space in the post User Space introduction in this post I am just going to explain how to use QUSLFLD.
After publishing yesterday's post User Spaces introduction Sam posted a comment:
It seems you forgot one important thing: Change User Space Attribute API
Automatic extendibility. Whether or not the user space is automatically extended by the system when the end of the space is encountered.
0 The user space is not automatically extendible.
1 The user space is automatically extendible.
While I write each post I test the code I give as examples. I did not encounter an error while testing that the User Space was full. But that could be that I did not fill the 128k size I made the User Space in my example.
Perhaps I needed to use a file with more fields? Or what would happen if I created the User Space with a size of 1 byte?
User Space is used by many IBM APIs to hold the generated results. It is similar to a data area, but can be a lot larger and will expand in size as more data is added to it. What I consider to be a drawback for using them is that they can only be accessed by API. As I want to talk about some APIs in future posts I thought it would be a good idea to give this introduction to using User Spaces first.
In this example I will be using the following User Space APIs to:
In this example I am going to use the List Fields API, QUSLFLD, to generate a list of the fields in a file. I just chose this API at almost random to show how to use the APIs in a very simple manner to demonstrate how they work. You will probably want to add more complicated code to your use of these APIs to cope with errors, etc.
The United Kingdom based user group the National iSystems User Group, NiSUG, changed their name to match the IBM i operating system and become the i-UG.
They have also created a new web site at i-ug.co.uk, which I have replaced their old NiSUG web site with on the IBM i user group page. There is a link to the user group page at the top of every page of this web site.
Feel free to check out the user groups listed, if you know of one that is not on the list please use the Contact form to send me its details. If you find one near you I encourage you to make contact with them and attend their meetings.
I am sure we have all been given a Microsoft Excel spreadsheet and told to upload the data from it into IBM i files. The first thing I do with the Excel spreadsheet is to save is as a CSV file. CSV stands for comma-separated values, the data elements, or columns, can be of varying length and are separated with a comma. It could be described as an Excel file without all the special formatting. If you have a Windows PC and you right-click on a CSV file, select "Open with", and then "Notepad" the file will look like this:
First,Second,,"4,444",5.5 First field,Second field,Third field,4.4,"5,555,555" |
In conversations with other IBM i folks I am finding that the "Send file to host" function of Client Access is being shut off on their IBM is as it is rightly regarded as a security risk. What can be used as an alternative?
Sometimes Googling uncovers things I missed when they were originally released. This is an example: the white paper An executive guide to IBM's strategy and roadmap for its intergraded operating environment for Powers Systems was published in May 2014. In this 16 page document IBM explains their commitment to the IBM i.
In the introduction Doug Balog, General manager IBM Power Systems, explains Big Blue's commitment:
I want to assure you that IBM is committed to the IBM i platform as a vital component of our Power Systems portfolio. IBM i is key to our broader strategy of investing in enterprise systems and storage.
There are many times when I want/need to have an interactive program submit a job to batch, and the interactive program would be so small it seems a waste to have such a program. For example the interactive program presents the user with a confirmation screen and then submits a program to batch that does all the processing. Perhaps the solution would be to have both the interactive and batch commands in the same program and it somehow know when it is running interactively or in batch.
Fortunately the 'Retrieve Job Attributes', RTVJOBA, command allows to retrieve which environment (interactive or batch) the job is running in. Combine this with subroutines in CL, see Subroutine in CL, and I can create a program that can run different commands in both environments.
When programming in RPGLE it is possible not to pass all the parameters between programs or procedures, using *OMIT in the calling code and %PARMS in the called code. The same functionality is not available in CL.
I needed to make a modification to an existing CL program, which is called from many other programs. If it was called from one program I needed to pass a parameter to it, if it was called by the others I did not need to use the parameter as a default value is used. I needed to make this quickly, before today’s night run. It would take too long to:
W ramach mojego celu, aby wyświetlić wszystkie IBM i grupy użytkowników na świecie miałem przyjemność znaleźć strona Common Polska.
Ich miejsce jest common.org.pl
Dodałem link do strony IBM i user groups.
Nie ważne gdzie jesteś w świecie, sprawdź, czy jest grupa użytkowników w pobliżu. A jeśli wiesz, grupy, która nie znajduje się na liście Contact Form, na prawo, aby wysłać mi swoje dane.
In December, 2013, I wrote a post about using the SNDSMTPEMM command to send emails from the IBM i, Email IFS files. It has become the most popular post on this blog, and the one that has generated the most interest.
Two of the most commonly asked questions has been:
IBM has published a draft of a Redbook called Modernize IBM i Applications from the Database up to the User Interface and Everything in Between. It primarily focuses on modernizing the traditional "green screen" UI to web and mobile.
Modernization is a broad term when applied to applications. It is more than a single event. It is a sequence of actions. But even more, it is a process of rethinking how to approach the creation and maintenance of applications. There are tangible deliveries when it comes to modernization, the most notable being a modern user interface (UI), such as a web browser or being able to access applications from a mobile device. The UI, however, is only the beginning. There are many more aspects to modernization.
It includes chapters featuring the following programming languages:
You can download it, as a PDF, here.
I am sure most of us are familiar with the Windows based Operation Navigator tool, and have found that there are certain tasks that are either easier to perform using it or there are tasks within it that there is no "green screen" equivalent. My experience with using it on many different PCs and versions of Windows is that it is slow, a resource hog, and if I do too much with it goes into a "Not responding" status. Therefore, I was intrigued to learn that you can now have access to same navigator-like tools using a web browser.
The browser based IBM Navigator for i was introduced as part of version 6.1, and is available in 7.1 and 7.2 . I have been able to use it with the following browsers:
As I do not have an Apple computer I cannot say whether you can use the Navigator on an Apple.
I was recently asked the following question:
I generate an auto incremental key in a table through create statement. But I am unable to reset the key back to 1 after a CLRPFM.
Any suggestions?
For those who are not familiar with the "auto incremental" identity column/field in a SQL table let me describe how to code it, and then how to reset it.
Yesterday, June 21, 2014, marked the 26th anniversary of the launch of the AS400. Torbjorn Appehl marked this milestone by publishing this video of the highlights of the launch event in the United Kingdom.
You can learn more about the launch of the AS400, and its evolution into the IBM i, in these posts:
I recently received an email informing me of the IBM Users Group of Central Missouri, CMIBMUG, who meet in Ashland. Ashland was chosen as the host city as it is midway between Columbia and Jefferson City.
You can check their website at cmibmug.org.
This link has been added to the IBM i user groups page. You will find a link to the user groups page at the top of every page of this website.
Check out which user group is closest to you.
If you know of a group which is not on the list use the Contact form, on right, to send me its details.
I am sure I am not the only person who has come across a CLP program, not been able to find its source, and been thankful for the RTVCLSRC command to be able to retrieve its source. By all means the code retrieved was not pretty, but it was a lot better than having nothing.
When the CLLE create program (CRTBNDCL) and module (CRTCLMOD) commands were introduced the "Allow RTVCLSRC" parameter was not included. With IBM i 7.1 the parameter has been reintroduced for both commands.
Over the past few weeks IBM has replaced the Infocenter documentation web sites with Knowledge Center sites for the recent releases of IBM i. While IBM i versions 6.1, 7.1, and 7.2 have release specific Knowledge Centers, i5/OS V5R4 has a generic/non-release specific version. Older releases Infocenters remain unchanged.
Fortunately IBM has redirected all the Infocenter pages to their equivalent pages in the Knowledge Center. If you find a link on this web site that does not redirect please let me know using the Contact form on the right.
The links to the various home pages are:
I have also changed the links in the Links to useful sites on the right too.
Recently a colleague came to me asking if I knew of a way to clear a file that was in use. She had tried waiting for the file to become "free", unused, so it could be cleared. It was continuously being used by various IBM i jobs and intranet applications.
I think all IBM i developers soon work out you cannot use the CLRPFM command to clear a file when it is open in another program. Even if you create a program to delete the records from the file you still cannot delete any ones that are locked, used by another job.
So what to do?
Today is the first anniversary of this blog. It has been a labor of love writing about what I consider to be the best sever, Power System, and operating system, IBM i, for business. And yes I am proud to be biased, as I have worked with the AS400 - IBM i and its predecessors for over 28 years.
This blog started simply, but thanks to you the number of readers has grown by leaps and bounds. You have visited from 151 countries, on all continents.
The group PTF for IBM i 7.1 Technology Refresh 8, TR8, can now be ordered from the IBM website here.
Unlike the previous technology release, TR7, this one is only available for IBM i version 7.1.
If you are interested in what is in this TR you ought to read these posts:
This week I came across the web site for the Mid-Range Michiana Users Group, MRMUG, of northern Indiana. Their president, Craig Nelson, informed me that they meet monthly in Mishawaka, which is part of the South Bend metropolis.
You can check their website at mrmug.org.
I have also added this link to the IBM i user groups page. You will find a link to the user groups page at the top of every page of this website.
Feel free to check out which user group is closest to you.
If you know of a group which is not on the user groups page use the Contact form, on right, to send me its details.
I was asked a question, via Facebook, on how I would take an alphanumeric field that contains '1 ' (1-blank-blank) and convert it to '001'.
Before I had time to answer the question others had posted their ideas:
While both would work, I came up with what I consider an easier/more efficient way.
I received a message from Gary Patterson in response to a comment I made on my post Adding Column Headings to a file in the IFS. I said that "I did try the SQL SYSCOLUMNS at first. The problem was with the way SQL interprets the Column Headings. Rather than break them out into 3 fields, it interprets them, as 1 very long field. The very long field was just too unwieldy to use."
In his message Gary explained "SYSCOLUMNS COLUMN_HEADING is a 60 byte column containing up to three rows of column heading information. Each row is exactly 20 bytes long and is populated by either the DDS COLHDG keyword, or the SQL LABEL ON DDL statement." And included an example of the SQL code to "build a column heading row dynamically".
In an earlier post I showed how it was possible to copy a file to the IFS in CSV format using CPYTOIMPF command. Unless the recipient of the file knows what the columns are they are going to come back to you asking what they are.
So what can you do to provide column headings?