DB2 web requests

A lot of the new features on the IBM i are around advances in the DB2 database functionality.  One such useful function is the ability to make web requests using SQL. The upshot of this is that you can now embed SQL into your RPG programs to consume web services.

These functions work by retrieving the the data returned into variables that are defined by the new SQLTYPE keywords. They include CLOB and BLOB types which result in data structures being created.

The following SQL will return the header and response for a web service call using the POST method to the endpoint, passing the header and payload.

select responseHttpHeader ,responseMsg
from table(systools.httppostclobverbose

Here is a link to the IBM document that has some deeper explanations to what else you can get up to with it.  Have fun!

debugging in HD

Those that debug free format RPGLE will probably have to strain their F19 and F20 keys going left and right to see the whole line of code. It’s quite annoying but a solution exists to extend the screen to 27 x 132 resolution.

Run the following CL command:


To remove:


shell scripting

Short post today. Trying a few things out with QSH and creating shell scripts in the IFS. First of all the shell script needs to reference the correct bin directory as such:


Note there are other script utilities you can reference above like “ksh” and “sh” but if you intend to run CL commands, via the SYSTEM call, then QSH is what you need.

Then you need to make the script executable by running the following.

chmod 755 <script.sh>

Now one thing that I was playing with was copying source files on the IFS to native source physical files and doing this from QSH and ultimately via shell scripts. The cp command would not work as the target destination was not recognised as a directory so I reverted back to CL system commands like CPYFRMSTMF and only QSH understands that via the SYSTEM command.

yum for IBM i

Yum is package manager that has been introduced to the IBM i. I won’t go into what it can do for you as there are plenty of articles out there. This blog is around my experiences of getting it installed which wasn’t straightforward – mainly due to my inability to follow the official instructions here:


I initially followed the online install instructions (without ACS) but had problems FTPing some of the files directly from the IBM repository, so then went to the offline install instructions. I tried to run the QSH commands separately but it only half installed and was left scratching my head.  Eventually just ran the command exactly as per instructions (step 4 below) and voila!  I’ve reproduced the instructions below for my own benefit really!

1. Use an FTP client from your PC to:

User - anonymous
Password - anonymous@example.com

2. Go to the folder:


3. Download the following as binary files to your PC:


4. FTP those files from your PC into /tmp on the IFS of your IBM i

5. Execute the QSH command

QSH CMD(‘touch -C 819 /tmp/bootstrap.log; /QOpenSys/usr/bin/ksh /tmp/bootstrap.sh > /tmp/bootstrap.log 2>&1’)

6. Check /tmp/bootstrap.log for any errors


IBM i 30th Anniversary

2018 marks the 30th Anniversary of the IBM i operating system! It was way back in 1988 when the AS/400 was first introduced to the stage, just as I was heading off to secondary school.

When I was reading up on what was going on I came across this promotional video from IBM featuring a young person I used to work with…

I worked with Liam Allan when he was on work experience and I have since seen him grow and turn into a real shining star in the IBM community!

Well done Liam and happy birthday IBM i 🙂

SQLRPGLE – the modern way

We’ve all been told to use embedded SQL in our RPGLE programs – it’s the modern way!  I’ve heard a lot about all the advancements in DB2 so where possible I’ve adopted SQLRPGLE as much as possible.  To be honest I do find some of the syntax a bit strange, particularly for the more advanced tools available to manage XML and web services, but it can be very powerful if used correctly!

Here is a code snippet for a simple update program, replacing all the main op-codes with SQL commands.

ctl-opt dftactgrp(*no) actgrp(*new) ; 
// Variables 
 dcl-s myfield char(10); 
// Set SQL options 
 exec sql 
 set option 
 commit = *none, datfmt = *ISO; 
// setLL *loval file1 
 exec sql 
 declare mycursor cursor for 
 select * from file1 
 for update of field1; 
 exec sql 
 open mycursor;

// read file1 
 exec sql 
 fetch mycursor into :myfield; 
// dow not %eof(file1) 
 dow sqlcod <> 100; 
// field1 = 'changed' 
// update file1r (field1) 
 exec sql 
 update file1
 set field1 = 'changed' 
 where current of mycursor; 
// read file1 
 exec sql 
 fetch mycursor into :myfield;

 close cursor; 


RPGLE Date Conversion cheat sheet

It is generally quite frustrating converting dates from the different formats into a bunch of other formats in RPGLE, but this cheat sheet has served me well over the years.  Not sure the genius who originally came up with it but you are a legend whoever you are!

H option(*nodebugio)

D @charA S 8 inz('04/12/01')
D @charB S 10 inz('12/02/2004')
D @charC S 8 inz('12/03/04')

D @dateA S d inz(D'2004-12-04')

D @numA S 6 0 inz(041205)
D @numB S 7 0 inz(1041206)
D @numC S 8 0 inz(20041207)
D @numD S 6 0 inz(120804)
D @numE S 8 0 inz(12092004)


// character to character...
 @charB = %char(%date(@charA:*ymd/):*usa/); // 'yy/mm/dd' to 'mm/dd/ccyy'
 @charC = %char(%date(@charA:*ymd/):*mdy/); // 'yy/mm/dd' to 'mm/dd/yy'
 @charA = %char(%date(@charB:*usa/):*ymd/); // 'mm/dd/ccyy' to 'yy/mm/dd'
 @charC = %char(%date(@charB:*usa/):*mdy/); // 'mm/dd/ccyy' to 'mm/dd/yy'
 @charA = %char(%date(@charC:*mdy/):*ymd/); // 'mm/dd/yy' to 'yy/mm/dd'
 @charB = %char(%date(@charC:*mdy/):*usa/); // 'mm/dd/yy' to 'mm/dd/ccyy'

// character to date...
 @dateA = %date(@charA:*ymd/); // 'yy/mm/dd' to D'ccyy-mm-dd'
 @dateA = %date(@charB:*usa/); // 'mm/dd/ccyy' to D'ccyy-mm-dd'
 @dateA = %date(@charC:*mdy/); // 'mm/dd/yy' to D'ccyy-mm-dd'

// character to numeric...
 @numA = %dec(%char(%date(@charA:*ymd/):*ymd0):6:0); // 'yy/mm/dd' to yymmdd
 @numB = %dec(%char(%date(@charA:*ymd/):*cymd0):7:0); // 'yy/mm/dd' to cyymmdd
 @numC = %dec(%char(%date(@charA:*ymd/):*iso0):7:0); // 'yy/mm/dd' to ccyymmdd
 @numD = %dec(%char(%date(@charA:*ymd/):*mdy0):7:0); // 'yy/mm/dd' to mmddyy
 @numE = %dec(%char(%date(@charA:*ymd/):*usa0):7:0); // 'yy/mm/dd' to mmddyyyy
 @numA = %dec(%char(%date(@charB:*usa/):*ymd0):6:0); // 'mm/dd/ccyy' to yymmdd
 @numB = %dec(%char(%date(@charB:*usa/):*cymd0):7:0); // 'mm/dd/ccyy' to cyymmdd
 @numC = %dec(%char(%date(@charB:*usa/):*iso0):7:0); // 'mm/dd/ccyy' to ccyymmdd
 @numD = %dec(%char(%date(@charB:*usa/):*mdy0):7:0); // 'mm/dd/ccyy' to mmddyy
 @numE = %dec(%char(%date(@charB:*usa/):*usa0):7:0); // 'mm/dd/ccyy' to mmddyyyy
 @numA = %dec(%char(%date(@charC:*mdy/):*ymd0):6:0); // 'mm/dd/yy' to yymmdd
 @numB = %dec(%char(%date(@charC:*mdy/):*cymd0):7:0); // 'mm/dd/yy' to cyymmdd
 @numC = %dec(%char(%date(@charC:*mdy/):*iso0):7:0); // 'mm/dd/yy' to ccyymmdd
 @numD = %dec(%char(%date(@charC:*mdy/):*mdy0):7:0); // 'mm/dd/yy' to mmddyy
 @numE = %dec(%char(%date(@charC:*mdy/):*usa0):7:0); // 'mm/dd/yy' to mmddyyyy

// date to character...
 @charA = %char(@dateA:*ymd/); // D'ccyy-mm-dd' to 'yy/mm/dd'
 @charB = %char(@dateA:*usa/); // D'ccyy-mm-dd' to 'mm/dd/ccyy'
 @charC = %char(@dateA:*mdy/); // D'ccyy-mm-dd' to 'mm/dd/yy'

// date to numeric...
 @numA = %dec(%char(@dateA:*ymd/):6:0); // D'ccyy-mm-dd' to yymmdd
 @numB = %dec(%char(@dateA:*cymd/):7:0); // D'ccyy-mm-dd' to cyymmdd
 @numC = %dec(%char(@dateA:*iso-):8:0); // D'ccyy-mm-dd' to ccyymmdd
 @numD = %dec(%char(@dateA:*mdy/):6:0); // D'ccyy-mm-dd' to mmddyy
 @numE = %dec(%char(@dateA:*usa/):8:0); // D'ccyy-mm-dd' to mmddccyy

// numeric to character...
 @charA = %char(%date(@numA:*ymd):*ymd/); // yymmdd to 'yy/mm/dd'
 @charB = %char(%date(@numA:*ymd):*usa/); // yymmdd to 'mm/dd/ccyy'

 @charC = %char(%date(@numA:*ymd):*mdy/); // yymmdd to 'mm/dd/yy'
 @charA = %char(%date(@numB:*cymd):*ymd/); // cyymmdd to 'yy/mm/dd'
 @charB = %char(%date(@numB:*cymd):*usa/); // cyymmdd to 'mm/dd/ccyy'
 @charC = %char(%date(@numB:*cymd):*mdy/); // cyymmdd to 'mm/dd/yy'
 @charA = %char(%date(@numC:*iso):*ymd/); // D'ccyy-mm-dd' to 'yy/mm/dd'
 @charB = %char(%date(@numC:*iso):*usa/); // D'ccyy-mm-dd' to 'mm/dd/ccyy'
 @charC = %char(%date(@numC:*iso):*mdy/); // D'ccyy-mm-dd' to 'mm/dd/yy'
 @charA = %char(%date(@numD:*mdy):*ymd/); // mmddyy to 'yy/mm/dd'
 @charB = %char(%date(@numD:*mdy):*usa/); // mmddyy to 'mm/dd/ccyy'
 @charC = %char(%date(@numD:*mdy):*mdy/); // mmddyy to 'mm/dd/yy'
 @charA = %char(%date(@numE:*usa):*ymd/); // mmddccyy to 'yy/mm/dd'
 @charB = %char(%date(@numE:*usa):*usa/); // mmddccyy to 'mm/dd/ccyy'
 @charC = %char(%date(@numE:*usa):*mdy/); // mmddccyy to 'mm/dd/yy'

// numeric to date...
 @dateA = %date(@numA:*ymd); // yymmdd to D'ccyy-mm-dd'
 @dateA = %date(@numB:*cymd); // cyymmdd to D'ccyy-mm-dd'
 @dateA = %date(@numC:*iso); // ccyymmdd' to D'ccyy-mm-dd'
 @dateA = %date(@numD:*mdy); // mmddyy to D'ccyy-mm-dd'
 @dateA = %date(@numE:*usa); // mmddccyy to D'ccyy-mm-dd'

// numeric to numeric...
 @numB = %dec(%char(%date(@numA:*ymd):*cymd0):7:0); // yymmdd to cyymmdd
 @numC = %dec(%char(%date(@numA:*ymd):*iso0):8:0); // yymmdd to ccyymmdd

 @numD = %dec(%char(%date(@numA:*ymd):*mdy0):6:0); // yymmdd to mmddyy
 @numE = %dec(%char(%date(@numA:*ymd):*usa0):8:0); // yymmdd to mmddccyy
 @numA = %dec(%char(%date(@numB:*cymd):*ymd0):6:0); // cyymmdd to yymmdd
 @numC = %dec(%char(%date(@numB:*cymd):*iso0):8:0); // cyymmdd to ccyymmdd
 @numD = %dec(%char(%date(@numB:*cymd):*mdy0):6:0); // cyymmdd to mmddyy
 @numE = %dec(%char(%date(@numB:*cymd):*usa0):8:0); // cyymmdd to mmddccyy
 @numA = %dec(%char(%date(@numC:*iso):*ymd0):6:0); // ccyymmdd to yymmdd
 @numB = %dec(%char(%date(@numC:*iso):*cymd0):7:0); // ccyymmdd to cyymmdd
 @numD = %dec(%char(%date(@numC:*iso):*mdy0):6:0); // ccyymmdd to mmddyy
 @numE = %dec(%char(%date(@numC:*iso):*usa0):8:0); // ccyymmdd to mmddccyy
 @numA = %dec(%char(%date(@numD:*mdy):*ymd0):6:0); // mmddyy to yymmdd
 @numB = %dec(%char(%date(@numD:*mdy):*cymd0):7:0); // mmddyy to cyymmdd
 @numC = %dec(%char(%date(@numD:*mdy):*iso0):8:0); // mmddyy to ccyymmdd
 @numE = %dec(%char(%date(@numD:*mdy):*usa0):8:0); // mmddyy to mmddccyy
 @numA = %dec(%char(%date(@numE:*usa):*ymd0):6:0); // mmddccyy to yymmdd
 @numB = %dec(%char(%date(@numE:*usa):*cymd0):7:0); // mmddccyy to cyymmdd
 @numC = %dec(%char(%date(@numE:*usa):*iso0):8:0); // mmddccyy to ccyymmdd
 @numD = %dec(%char(%date(@numE:*usa):*mdy0):6:0); // mmddccyy to mmddyy

*inlr = *on;

IBMi FTP with FileZilla

A good way to access files on the IBM i is via an FTP client.  I like to use a popular open source program called FileZilla.

The IBM i’s native file system is flat with all objects contained in libraries. Objects can be files, programs and other things native to the IBM i, such as out queues, message queues etc. It also supports a more common directory based system in its IFS – Integrated File System. There are plenty of more in depth article about the IFS on the internet!

To use FileZilla with your IBM i, first of all set up a new site.  I’m using pub400.com as an example.

Then in the Advanced tab, instead of starting in your home IFS folder, you could change the default to your named library.  All libraries have a “.lib” file type, files are “.file” and file members are “.mbr”.  All libraries are contained in the QSYS.lib library which is in the root directory of the IFS.

Now save and connect.

Sometimes, depending on how the IBM i FTP server is set up, you need to specify the namefmt you want to use, if the server does not detect it automatically.  To do this you’ll need to add the following text to the sitemanager.xml file at the end of the server set up.

 <Command>SITE NAMEFMT 1</Command>
 <Command>SITE LISTFMT 1</Command>

This xml file can usually be found here:


Add those lines within the server tag of the IBM i site , ensuring all the elements are within that tag.  Make sure it is within the server close tag otherwise those lines will keep disappearing once you fire up FileZilla.

The contents of the file should look like this:

Lastly if you’re transferring source code then make sure you have the transfer setting set to ascii.

And away you go!



Green screen 5250 terminal emulator

To get the 100% authentic IBM i experience you have to download a green screen terminal emulator.  Way back when, and even when I started, the only way to interface to an old school AS/400 were through a green screen terminal or a green screen emulator on your PC.  For more info here’s a link to the wiki IBM 5250 page.

Most IBM i professionals are familiar with IBM i Access client which has a terminal emulator and a bunch of other utilities.  However, as with most IBM software, this is not free but luckily there are free alternatives out there.  Here is a list that are compatible with Windows.



Paid alternatives include:


http://www.ericom.com/tn5250.asp – Powerterm – 30 day free trial only

Currently, at home, I am using first one on the list which is written in java.  It’s simple to download and install.  Once done, set up your connection to your server.  Below are the settings to the pub400 free IBM i resource.

PUB400 – free IBM i computing

Most people usually come across the IBM i through work.  In the “real” work it’s pretty hard to get access to one unless you know what you’re looking for.  You can usually find the odd iSeries on eBay for under a grand, but it’s unlikely to be running the latest OS release.

So where do we get the next generation of programmers and tech savvy kids interested? One answer is to provide free access to this platform and one of the very few places you can do this is at www.pub400.com.

This resource is kindly provided by a German IBM i hosting company.  Once you’ve signed up you get 3 libraries (the native OS file system directory) and an IFS directory (akin to the directory tree file system most people are used to) to play with. The security settings are a little limiting so you cannot restore any save files for example (think zip files on your PC) but you get FTP access and there are various links off to free resources that you can download to facilitate your coding. More on those free resources later.

To get started, you sign up through a simple form, where you will get your user id and password via email. Before you download anything more there is a simple and easy way to access your account and that is through your PC’s humble command prompt.

Type telnet www.pub400.com and login with your details:

Next time we’ll look at a few free terminal emulators that can upgrade your  terminal experience.