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!

Putty for IBM i

Putty is an excellent way to access the IBM i via SSH and beats the PASE shell hands down.

  • To enable this on the IBM i server then type on the command line
  • Download and set up Putty by adding a connection to the IBM i address over SSH on port 22

installing node.js

If you installed yum as per a previous blog post then installing node.js to your IBM i is a cinch.

  • Open a PASE shell via the command line
  • Install node.js 10 – you can install multiple versions in parallel
yum install nodejs10
  • To allow you to call the node commands from your directories, add the open source command directory to your PATH environment variable
export PATH
  • Verify the commands now work by checking the version you installed
node -v
  • If you have multiple versions of node.js installed you can use the following command to select which one will be active
nodever list

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.


I have done a little more reading and it appears QSH is the old Unix shell type environment that was built using native IBM i objects originally to provide Java a command line environment like all the other implementations. Because of the way it was built it is very comfortable running any SYSTEM commands.

QP2TERM or QP2SHELL is a more recent Unix shell that was ported from the AIX operation system. This is what has underpinned all the recent work to bring in software like PHP and Node.js to the IBM i and should be used to administer that type of programming language.

They are both different flavours of basically the same thing but depending on what you’re trying to do they have their advantages and disadvantages. However if you are looking for a pure shell environment on the IBM i, I think it’s best to install OpenSSH use Putty or similar.

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;

IBM i 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. NAMEFMT 1 allows you to specify IFS directories in the advanced tab.

 <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!