01 - Programming (EN)01a - RPG (EN)02a - SQL (EN)05 - Web Services (EN)

How to use REST API on IBM i: the Open Banking example

The IBM i system, being a robust and versatile business server, can easily connect to the world of REST APIs, enriching the applications it hosts with features taken from the cloud.
In today’s example we will see how to read bank current account data, which the PDS2 European regulation has made usable also by third parties, management software included.
To do this we will use the following tools.

  • SQL functions HttpPostClob And HttpGetCLob , which allow you to make HTTP POST and GET calls using normal SQL statements. (Roberto De Pedrini spoke about it here ).
  • The SQL function Json_Table which converts a JSON document into a DB2 for i table.
  • Obviously the ILE RPG language in the modern free format.

Getting a JWT token with a POST

REST APIs are, by definition, stateless, i.e. the state of the communication between client and server is not stored on the server between the various requests. Each request therefore appears “new” to the server and must therefore contain the information necessary for client authentication. One of the techniques often used for this purpose is to send the user/password pair in an initial authentication call, which returns a token signed by the server (JSON Web Token or JWT extension ) with a time limit, in which the identity of the owner is certified. The token is then transmitted in all subsequent calls (until it expires) without the need to re-present credentials.
Also the service of A-CUBE for interacting with current accounts , for which it is possible to request test credentials, provides for the authentication via JWT . Let’s see the sample program that makes the connection .

Against this JSON response returned by the login POST:

{ "token": "a very long encrypted token" }

we define a DS useful for extracting its field tokens

dcl-ds jsonData qualified;
token varchar(2048);

We then compose the variables that will be useful for making the call, taking the user and password from environment variables:

WebServiceUrl = 'https://common-sandbox.api.acubeapi.com/' +

WebServiceHeader = '<httpHeader>-
<header name="content-type" value="application/json" />-

Email = %str(getenv('ACUBE_EMAIL'));
Password = %str(getenv('ACUBE_PASSWORD'));

WebServiceBody = '{"email": "' + Email + '", "password": "' + Password + '"}';

Now we come to the most important part. Thanks to the help of the SQL language encapsulated in our RPG program, we can:

1) make an HTTPPOST to the service with the function Systools.HttpPostClob

Select * from
Json_Table(Systools.HttpPostClob(:WebServiceUrl, :WebServiceHeader, :WebServiceBody),
Columns(Token VarChar(2048) Path '$.token'));

2) transform the JSON response into a relational table with the function Json_Table :

Select * from Json_Table(Systools.HttpPostClob(:WebServiceUrl, :WebServiceHeader, :WebServiceBody), '$' Columns(Token VarChar(2048) Path '$.token'));

Expressions with the $ represent the path in the JSON structure from which to start to extract the data:
$ is the current point, $.fieldname the field fieldname contained in the current object. Here you can do some tests with
these that are said JSON Path .

3) Read the contents of the table thus obtained and place it in the DS.

Exec sql
Fetch Next From Csr01 into :jsonData

4) Put the token in an environment variable to make it available to later called programs.

putenv('ACUBE_TOKEN=' + jsonData.token);

Register the owner of an account (Business Registry)

The sample program which registers a new owner of an account (a Business Registry) offers us the possibility of using another interesting SQL function.

The REST API for creating a Business Registry requires you to POST with a larger JSON payload than the previous one. To handle it easily we can write it to the IFS in a stream file similar to This , then read it from SQL with the function IFS_READ . This function takes as parameters the file path (in our case taken from an environment variable), the maximum line size and the line delimiter. In the example we indicate IT IS NOT as a delimiter because we want to read the whole file as one line. The content of the file thus obtained is then inserted into a variable which will be the body of our POST to the API.

JsonInputFile = %str(getenv('ACUBE_IFS_JSON'));

Declare File Cursor For
From Table(IFS_READ(:JsonInputFile, 2048, 'NONE' )) As IFS;
Exec SQL Open File;

Exec SQL Fetch Next From File Into :WebServiceBody;

Another interesting part is the composition of the header which includes the JWT token placed in an environment variable by the previous program.

Tokens = %str(getenv('ACUBE_TOKEN'));

WebServiceHeader = '<httpHeader> ' +
' <header name="authorization" value="bearer ' + Tokens + '" /> ' +
' <header name="accept" value="application/json" /> ' +
' <header name="content-type" value="application/json" /> ' +

Connect an account and list its movements

The program connect.rpgle produces a URL to which to connect to signal to the bank that you agree to read the current account data. Its operation is very similar to the previous ones.

The last example program finally allows the reading current account data . As an example, I have decided to print this information, but obviously the data can be used in any way.


In this article we have seen how easy it is to use REST APIs from IBM i (allow me, the evolution of our beloved AS400), exploiting the power of the SQL functions of IBM DB2 for i.

All sample sources are present in this Github repo .

Thanks go to the managers of the free hosting site PUB400 , through which I compiled the programs.

Related Posts
DB2 for i SQL – String Manipulation – POSSTR-LOCATE-LOCATE_IN_STRING (EN)

Introduction Often, in our applications, we need to work with text strings, and DB2 SQL can come in very useful Read more

DB2 for i – FAQ & Howtos (EN)

DB2 Database and SQL ... maybe the most important things on IBM i platform: here's a collection of FAQs, tips Read more

IBM i 7.4 Announcement (En)

Comes directly with the Easter egg this IBM announcement for the news of the IBM i 7.4 version, iNext version Read more

Generated Always Columns (EN)

Introduction "Generated Always Column": are columns, table fields, filled by DB2 engine: something like columns with a default value but Read more

Leave a Reply

Your email address will not be published. Required fields are marked *