Translating hexadecimal values using XLATE

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?

Version 7.2 coming soon

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: http://pic.dhe.ibm.com/infocenter/iseries/v7r2m0/index.jsp. Perhaps this will be the help site for the new release.

Fun with Logical Files

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:

A..........T.Name++++++RLen++TDpB......Functions 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.

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 http://rse.common-users.fr.

Et ils ont un forum à http://forum.commonfr.org/forum/index.php.

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.

New to IBM i from developerWorks

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.

Add key to SQL table in QTEMP

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:

CREATE TABLE QTEMP/OUTFILE AS (SELECT SOURCE,ORDNO,CUSNO,PART,SPEC,ORQTY,DUEDT FROM INFILE WHERE SOURCE = '04') WITH DATA

Creating a SQL table "on the fly"

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.