Removing duplicate records from a file

How to remove duplicate records from a file? is a question I have been asked by so many different people I feel that it must be on one of those interview questions lists. What I mean by duplicate records is where there is more than one record in a file were values in all are the same as another record. I do not mean records with duplicate keys, as that is whole other "can of worms".

In a well built database each "master" file or table should have a unique key, which prevents duplicate keys and records:

Steve Will talks of future TRs and releases

In the past few months I have written about sightings suggesting that a new release, IBM i 7.3, might be coming:

• Is IBM i 7.3 already out?
• More proof IBM i 7.3 is coming

Steve Will, IBM i's Chief Architect, wrote a post on the "You and i" blog titled "Signs and Portents for IBM i Announcements". In it he talked, in general terms, about Technology Refreshes and future releases:

Retrieving the job's library list using SQL

Before IBM i 7.2 if I wanted to retrieve the current job's library list I would have to use either the Retrieve Job Attributes command, RTVJOBA, or the Retrieve Job Information API, QUSRJOBI. As part of "librarian services" a new View was introduced, LIBRARY_LIST_INFO, and as its name suggests it allows me to retrieve information about my job's library list.

Like all the other new Views and Table Functions LIBRARY_LIST_INFO resides in the library QSYS2. It is possibly the smallest View I have written about as it has only six columns (fields):

Submit Job passing packed decimals

Every once in a while I get several people asking me the same question. As the examples accompanying tend to be the same I assume that this question has become part of some organization's interview question list. Recently it has been questions about using packed decimal variables with the Submit Job command, SBMJOB.

Most of us when we started on the IBM i (or one of its earlier forms) tried a SBMJOB with a packed decimal variable. The submitted job produced the following error message: "MCH1202: Decimal data error". After trying several different ways we admitted defeat, convert the packed decimal variable to character and used that in the SBMJOB.

Journal information using SQL

It is Monday morning and I receive an email from my superior asking about the journals on the production IBM i. How can there be so many? How many are active? How much space do they take up? How many are remote journals? I need a quick and easy way to get to information about the journals. There is a Retrieve Journal Information API I could use. Fortunately there is a SQL View, added in IBM i 7.2, that does the basically the same thing as the API, but it is a heck of a lot easier to get the information from.

Those of you who are regular readers of this blog will not be surprised to learn that the JOURNAL_INFO View is located in the library QSYS2. It contains a lot more information that I have been asked to provide. Rather than list all of the columns I am going to refer you to the relevant page in the IBM's KnowledgeCenter here.

Read an IFS file using RPG

 

Update

Before using what is described below consider using SQL to read IFS files.

 

---

 

I had a situation where I had a text file in an IFS folder and I needed to read its contents. I could have copied the file from the IFS into an IBM i library and then read it there, but I decided to investigate how I could read the file directly from the IFS using RPG. Fortunately there are three APIs which will allow me to do this.

First I need to give credit to a friend of mine, who wishes to remain anonymous, for providing a program that my example is based upon.

I created a text file using Windows' Notepad. At the end of each line I pressed Enter to create variable length records, see below, and copied the file to my folder in the IFS.

More proof IBM i 7.3 is coming

On December 17 I found a reference to IBM i release 7.3 on an IBM website, you can see it here. This weekend I found further proof that a new release is coming.

In the IBM developerWorks website there are now sections for IBM i 7.3 and 7.2 TR4 enhancements, which were created on January 13.

Using SQL for object's statistics

I have mentioned in a previous post how you could find out pretty much anything you wanted to know about a file or SQL table by using the SQL View SYSTABLES. But what about other object types?

I have found the OBJECT_STATISTICS table function useful for most of the information I want to know about objects in a library. Table functions return columns in way that resembles getting columns from a View or Table.

OBJECT_STATISTICS has three parameters:

Retrieving the program's name in a CL program

 

The original contents of this page have become obsolete, go to this page for up-to-date information.

 

Finding record locks using SQL

One of the new SQL Views introduced as part of IBM i 7.2 TR3 and 7.1 TR11 was one that allows us to check for record locks. Now I can execute a SQL query and return all of the jobs locking the file I inquired about. No longer do I need to use APIs or the CL command Display Member Record Locks, DSPRCDLCK.

The new View, RECORD_LOCK_INFO, is found in the library QSYS2. It returns one row of every lock in your IBM i. This is why I should be careful how I use this View. I am sure there are many thousands of record locks in any medium to large size applications at any one time, most of them are harmless as the record or row is locked, updated and released. Every once in a while something goes "bump" and a tries to lock a record/row that is already locked by another. An error message occurs and I have to work out what the lock is and how to release it, in a timely manner, before I get more record locks to the same record/row.

Happy New Year

At the start of a new year I am always filled with a feeling of excitement for what the new year will bring us, and a feeling of gratitude for all the good stuff that happened last year.

Last year's most popular posts covered a good variety of subjects:

1. Change date from one format to another using SQL
2. Char built in function added to CL
3. IBM i 7.1 TR10 and 7.2 TR2 announced at Common
4. Trim in CL and its Second Parameter
5. Getting the number of rows affected by SQL statement

We also have the following IBM i anniversaries to look forward to in 2016:

Searching for strings in source files, using RPG

In my previous post, Searching for strings in source files, I included a sample program written in CL. The day it was published I received a message asking me: "Could that all be written in RPGLE instead of CL?"

The answer is: of course it can be. I thought this would be a good excuse to show how I could write a RPGLE program to do this, which would include subjects I have written about before. The RPGLE program will function in the same way the CL one does. The program is passed three parameters: