Wednesday, June 25, 2014

Resetting the identity column in a SQL table

reset identity column sql db2 for i

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.

Monday, June 23, 2014

AS400 launch event video

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:

Friday, June 20, 2014

From IBM: IBM i 7.2 and IBM POWER8

ibm i 7.2 power 8

This is a great article from IBM describing how great the new Power8 servers are with IBM i 7.2. The most telling conclusion is:

POWER8 servers are up to 50% faster than comparable POWER7 models for commercial workloads.

Article as a web page here.

Article as a PDF file here.

User Group: CMIBMUG

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

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.

Wednesday, June 18, 2014

RTVCLSRC makes a comeback

clp clle rtvclsrc retreive source

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.

Monday, June 16, 2014

Infocenter replaced by Knowledge Center

infocenter knowledge center

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.

Thursday, June 12, 2014

How to clear a file when it is in use

sql delete insert clrpfm

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?

Tuesday, June 10, 2014

RPGPGM.COM’s first birthday

1st anniversary

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.

Monday, June 9, 2014

TR8 PTF now available

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:

Thursday, June 5, 2014

Another User Group found: MRMUG

mrmug midrange michiana users group

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

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.

Tuesday, June 3, 2014

How to right justify a alphanumeric field with leading zeroes

%trim %xlate %editc

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:

  • Convert the field to numeric and then convert it back to alphanumeric with the edit code X.
  • Use an array to check and move each element, no example code was given.

While both would work, I came up with what I consider an easier/more efficient way.