Wednesday, January 29, 2014

Translating hexadecimal values using XLATE

XLATE translate hexadecimal hex rpg

As I have mentioned in previous posts there are several processes I have written and responsible for that takes data from a CSV (Comma Separated Values) files and updates files on the IBM i (AS400). One vendor has trouble sticking to the agreed standard of not having double quotes ( " ) in the description fields to denote inches.

The double quotes are interpreted as the start of an alphanumeric field which does not end until another double quote in encountered. This results in the extra fields being created at the end of the record or a loss of several records which are combined into one big field. I prevented this from happening by using the %XLATE built in function in RPGLE/RPG IV to replace all occurrences of the double quote with a space.

But this week they sent a right double quotation mark ( ), which were translated to some strange hexadecimal characters by the ASCII to EBCDIC translation table.

How can I replace these hexadecimal characters?

Sunday, January 26, 2014

Version 7.2 coming soon

operating system release upgrade 7.2 v7r2

Last week I learned from an IBM support person that the new release of operating system for the IBM i is imminent.

He told me that IBM i 7.2 will be released in Spring 2014, at a yet to be decided date.

By modifying the URL of the IBM i 7.1 website I have found an empty one at: Perhaps this will be the help site for the new release.

Wednesday, January 22, 2014

Fun with Logical Files

logical file lf sst rename trntbl

This is not going to be an article on how to code Logical Files. It is about how you can use some of the less well known keywords/functions to do some pretty cool, and fun, stuff with the fields within them.

Before I start coding Logical files I need a Physical file. In this article I am going to use the following file:

01 A          R TESTPFR 
02 A            PFLD1          3A
03 A            PFLD2          2A
04 A            PFLD3         30A 
05 A            PFLD4          5P 0 
06 A            PFLD5         10A 
07 A            PFLD6           L 

I know I will not get any awards for originality for my choice of file and field names.

Monday, January 20, 2014

User Group: Common France

user group common france

Comme quelqu'un qui croit en la valeur des groupes d'utilisateurs d' IBM i, j'étais heureux de recevoir un message de Julie Gaubert m'informant de Common France de sites web.

Leur site est à l'adresse

Et ils ont un forum à

J'ai ajouté ces liens pour sur ce site IBM i User Groups page. Vous trouverez un lien vers la page des groupes d'utilisateurs en haut de chaque page de ce site.

Merci Julie.

Si vous connaissez un groupe d'utilisateurs, peu importe où il est dans le monde et qui n'est pas sur la page groupes utilisateurs Utilisez le Contact form pour m'envoyer ses détails.

Thursday, January 16, 2014

New to IBM i from developerWorks

developer works new to ibm i

IBM has updated their developerWorks web site to include a New to IBM i zone.

This is an excellent place to start learning about the IBM i with links to many other parts of their developerWorks web site.

I have added this link to the Links to other sites section, on the right, or you can go there by clicking here.

Tuesday, January 14, 2014

Add key to SQL table in QTEMP

create table alter table create index key unique

In my last post I discussed how you could Create a SQL table on the fly, and Manuel Moreno posed the question if it was possible to create a table like this with a key?

I consider this an excellent question, therefore, I created this post in response to his question.

I will be using the same scenario as I did in the original post. There I create a SQL table in QTEMP by using the CREATE TABLE AS SQL statement:

                  FROM INFILE
                  WHERE SOURCE = '04')
          WITH DATA

Wednesday, January 8, 2014

Creating a SQL table "on the fly"

sql create table

I am rarely asked to create reports anymore. More frequently I am asked to create extracts of data into output files. The users then upload the data from the output files into Microsoft's Excel, and "slice-and-dice" the data to generate the format they desire.

The traditional approach works well. The programmer creates:

  • An output file to contain the extracted data.
  • A program, usually RPGLE/RPG IV to extract the desired data from the input files.

In this post I am going to show an example, of what I consider, a simpler way to extract data from the input files and create an output file "on the fly", i.e. without a pre-existing output file, using SQL.