Integrating IBM i with REST services

"You cannot call APIs from IBM i." It is a belief that survives because it dates from when it was true. Today there are several routes, and the simplest one lives inside the database: you write a SELECT and you get back the response of a web service.

The most direct route: SQL

Db2 for i provides functions that perform HTTP calls. The name and the schema have changed over time — the original functions live in SYSTOOLS, while more recent releases offer a newer generation in QSYS2 with better performance and more control over headers and authentication.

Before writing any code, then, it is worth checking what is available on your own machine:

SELECT ROUTINE_SCHEMA, ROUTINE_NAME
  FROM QSYS2.SYSROUTINES
 WHERE ROUTINE_NAME LIKE 'HTTP%'
 ORDER BY ROUTINE_SCHEMA, ROUTINE_NAME;

The list that comes back tells you exactly which functions you can use on that release and which schema to find them in. From there, a call to a service returning JSON becomes a query, and the result can be read with JSON_TABLE to turn it straight into rows and columns.

The advantage of this route is that everything SQL can do becomes available to everyone: RPG with embedded SQL, a CL program with RUNSQL, a script, a stored procedure.

Reading JSON without the pain

The part that usually worries people is parsing the response. In SQL you do not have to write a parser:

SELECT *
  FROM JSON_TABLE(
         :response,
         '$.items[*]'
         COLUMNS (
           code        VARCHAR(20)   PATH '$.code',
           description VARCHAR(100)  PATH '$.description',
           price       DECIMAL(11,2) PATH '$.price'
         )
       ) AS data;

The incoming JSON comes out as an ordinary table, with typed columns. From an RPG program with embedded SQL you read it with a cursor, exactly like any other result set.

To build JSON on the way out there are the mirror functions JSON_OBJECT and JSON_ARRAYAGG, which assemble the structure directly from a query.

The other direction: exposing your own programs

When it is the outside world that has to call IBM i, the supported route is the Integrated Web Services server, included in the operating system. It lets you publish an existing program or procedure as a REST service without rewriting it: you point at the object, map the parameters, and the server handles the rest.

The delicate point is not technical. A program written to be called from a 5250 menu often assumes a context — the session's library list, a data area set beforehand, a specific user — that an HTTP call does not have. So it is best to expose programs written to be callable from any context: explicit input parameters, no dependency on session state.

When you need something more

The SQL functions cover synchronous calls and simple formats well. They become awkward when you need elaborate authentication, fine control over timeouts, or when the call volume is high. In those cases there are two alternatives:

• QShell or PASE, where you have curl and full runtimes such as Node.js or Python. A CL program can launch a script and read the result from a file in the IFS.
• A service program calling the system socket APIs, for anyone needing total control. It is the most laborious route and only makes sense when the others are not enough.

What breaks integrations

Certificates. An HTTPS call requires the authority that signed the remote server's certificate to be present in the system certificate store, managed through Digital Certificate Manager. This is by far the most frequent cause of calls that fail immediately, with errors that do not look as though they have anything to do with certificates.

Character encoding. IBM i works natively in EBCDIC, the HTTP world in UTF-8. The SQL functions handle the conversion, but when you go through IFS files or PASE the conversion has to be stated explicitly, otherwise accented letters become the first symptom that something is off.

Timeouts. A synchronous call inside an interactive program blocks the user until the remote service answers. If the service is slow or does not answer at all, the session hangs. For non-essential integrations a queue is better: the program writes the request, a batch processes it.

Where to start

The quickest way to get a feel for it is to open Run SQL Scripts in ACS, run the query listing the available functions shown above, and try a call to a public service that needs no authentication. If that works, everything else works: what remains is configuration, not feasibility.

Which versions this works on

This is the topic on this list where the release matters most: here the differences between versions are substantial.

HTTP functions in SYSTOOLS (SYSTOOLS.HTTPGETCLOB, HTTPGETBLOB and similar) — these are the oldest, documented at least from IBM i 7.3. They are written in Java: every call spins up a JVM, with the cost that implies. They work, but they are only the necessary choice if the machine does not have the others.

HTTP functions in QSYS2 (QSYS2.HTTP_GET, HTTP_POST and similar) — these are the next generation and the ones to prefer: they offer the same capabilities without creating a JVM, and they add HTTP authentication, proxy support, a configurable number of redirects and SSL options. They rest on the transport APIs of the Integrated Web Services client for ILE. The binary (BLOB) versions of these functions were delivered in May 2023, with PTF group level 4 on IBM i 7.5 and level 25 on IBM i 7.4.

The query shown in the article for listing the available functions exists precisely for this: it tells you which of the two generations is on your machine, without guessing.

JSON_TABLE — introduced with database PTF group SI99702 level 14 on IBM i 7.2 and SI99703 level 3 on IBM i 7.3, both from November 2016. It is not available on 7.1. It was later extended: direct shredding of JSON arrays arrived with SF99704 level 7 on 7.4 and SF99703 level 18 on 7.3.

If you are on a release with none of this, two routes work everywhere: QShell or PASE with curl, invoked from a CL program with the response read from an IFS file; or a program using the system socket APIs directly.

Integrated Web Services server — included in the operating system, and used for the opposite direction, exposing programs and procedures as services. Versions have followed one another over time and REST support was added after SOAP: the IBM page listed in the sources states which version of the server is available for each release.

Sources

• New HTTP functions based in QSYS2 — IBM Support
• HTTP function overview — IBM Documentation 7.5
• HTTPGETBLOB and HTTPGETCLOB scalar functions — IBM Documentation 7.3
• Db2 for i SQL: Using JSON_TABLE — IBM Documentation 7.3
• Working with JSON data — IBM Documentation 7.4
• JSON_TABLE() table function — IBM Support
• Integrated Web Services for IBM i — IBM Support
• Db2 for i — Technology Updates (PTF levels for each function)