Calling REST APIs from RPG without HTTPAPI: QSYS2.HTTP_GET, JSON_TABLE and HTTP_POST

For years, an RPG program that had to call a web service needed extra pieces: Scott Klement's HTTPAPI, raw sockets, or the Java-based SYSTOOLS.HTTPGETCLOB. They all work, but each one is something more to install, learn and support.

On current IBM i releases (7.3 TR11 / 7.4 TR5 and later) Db2 for i can do it alone. The QSYS2.HTTP_GET, HTTP_POST, HTTP_PUT, HTTP_DELETE functions and their _VERBOSE table versions call a URL from SQL, and the JSON functions turn the answer into rows. From RPG that is just embedded SQL.

In this post I show both directions from my sample project dodikibmi/ibmi-inventory, running on PUB400 (IBM i 7.5):

1. GET: load public holidays from a REST API into a Db2 table (INV600R).
2. POST: send a "reorder needed" alert as JSON to a webhook (INV700R).

And one trap I fell into: a 404 answer does not raise an SQL error.

Step 1: try it in plain SQL first

Before writing any RPG, run the call in Run SQL Scripts or the Code for IBM i SQL view. The free Nager.Date API returns the public holidays of a country as a JSON array (shortened here):

[
  {"date": "2026-01-01", "localName": "Tahun Baru Masehi", "name": "New Year's Day", "countryCode": "ID", "types": ["Public"]},
  {"date": "2026-04-03", "localName": "Wafat Isa Almasih", "name": "Good Friday", "countryCode": "ID", "types": ["Public"]}
]

JSON_TABLE maps that array to columns, and HTTP_GET can be its input directly:

SELECT h.hdate, h.hname
  FROM JSON_TABLE(
         QSYS2.HTTP_GET('https://date.nager.at/api/v3/PublicHolidays/2026/ID', ''),
         'lax $[*]'
         COLUMNS (hdate DATE         PATH 'lax $.date',
                  hname VARCHAR(100) PATH 'lax $.name')) h
 ORDER BY h.hdate;

Result on PUB400:

HDATE       HNAME
2026-01-01  New Year's Day
2026-04-03  Good Friday
2026-04-05  Easter Sunday
2026-05-01  Labour Day
2026-05-14  Ascension Day
2026-06-01  Pancasila Day
2026-08-17  Independence Day
2026-12-25  Christmas Day

A few notes on the SQL:

• 'lax $[*]' means "every element of the top-level array". lax mode returns NULL for a missing field instead of failing the whole statement.
• The column type does the conversion: DATE accepts the ISO string 2026-01-01 as it is.
• The second argument of HTTP_GET is the options JSON. Empty is fine for a public GET.

Once the query returns the right rows, the RPG part is small.

Step 2: the same call inside RPG, with MERGE

The program loads this year and next year into HOLIDAY_CALENDAR. MERGE inserts new dates and updates the names of existing ones, so the program can run every month without creating duplicates:

exec sql
   merge into HOLIDAY_CALENDAR t
   using (select h.hdate, min(substr(h.hname, 1, 30)) as hname
            from json_table(:Json, 'lax $[*]'
                   columns (hdate date         path 'lax $.date',
                            hname varchar(100) path 'lax $.name')) h
           where h.hdate is not null
           group by h.hdate) s
      on t.HLDATE = s.hdate
    when matched then
         update set t.HLDESC = s.hname
    when not matched then
         insert (HLDATE, HLDESC) values (s.hdate, s.hname);

Two details matter here:

• GROUP BY h.hdate: some countries have two holidays on the same day. Without the grouping, MERGE sees the same key twice and fails. MIN() simply picks one name.
• substr(..., 1, 30): the table column is CHAR(30). Cut the text in SQL rather than getting a truncation error.

After the statement, GET DIAGNOSTICS :WkRows = ROW_COUNT gives the number of rows merged, which the program returns to its caller.

Once the table is filled, the date service program of the application (DateIsWorkday, DateAddWdays) knows the new holidays at once, without a recompile. That is the real value: the external data lands in an ordinary Db2 table that any RPG program can CHAIN to.

The trap: a 404 is not an SQL error

My first version passed QSYS2.HTTP_GET(:WkUrl, '') directly into JSON_TABLE, like the SQL above. It worked fine until I tested a wrong URL (country code XX):

VALUES QSYS2.HTTP_GET('https://date.nager.at/api/v3/PublicHolidays/2026/XX', '');

{"title":"Unknown country code","status":404,"detail":"The country code provided is invalid or not recognized."}

No SQLCODE, no exception: HTTP_GET simply returned the body of the error answer. JSON_TABLE read that object, found no date, the WHERE h.hdate IS NOT NULL filtered it out, and MERGE reported 0 rows. A batch job would end "normally" every night and load nothing.

The fix is the _VERBOSE version. It is a table function that returns the body and the response headers as JSON, and the headers include HTTP_STATUS_CODE:

SELECT v.RESPONSE_HTTP_HEADER
  FROM TABLE(QSYS2.HTTP_GET_VERBOSE(
         'https://date.nager.at/api/v3/PublicHolidays/2026/XX', '')) v;

{"HTTP_STATUS_CODE":404,"Content-Type":"application/json; charset=utf-8", ...}

So the RPG program now calls the API once, checks the status, and only then parses the body:

// Call the API; the status code comes back in the header JSON
exec sql
   select json_value(v.RESPONSE_HTTP_HEADER,
                     '$.HTTP_STATUS_CODE' returning integer),
          v.RESPONSE_MESSAGE
     into :WkStatus, :Json
     from table(QSYS2.HTTP_GET_VERBOSE(:WkUrl, '')) v;
if SQLCODE < 0;
   WkMsg = 'Calling ' + WkUrl + ' failed, SQLSTATE ' + SQLSTATE;
   exsr SendEscape;
endif;
if WkStatus < 200 or WkStatus > 299;
   WkMsg = 'Holiday API answered HTTP ' + %char(WkStatus)
         + ' for ' + %char(WkYear);
   exsr SendEscape;
endif;

Json is a CLOB host variable (D Json S SQLTYPE(CLOB:1000000)), and the MERGE above reads from :Json instead of calling the API a second time.

SendEscape sends CPF9898 as an escape message with QMHSNDPM. That is the IBM i way to fail: the CL program that runs the job can MONMSG CPF9898 and alert someone, and the job log shows the real reason.

Rule of thumb: use the _VERBOSE functions in programs, and keep the short ones for quick queries. A network error (DNS, timeout, TLS) does raise an SQL error; an HTTP error status does not.

Step 3: the other direction, POST with HTTP_POST

The second program pushes data out. Items below their minimum stock are summarised per warehouse and posted as JSON to a webhook, the kind of endpoint Slack, Teams or another application provides.

Db2 builds the whole message in one statement. LISTAGG turns several rows into one readable line per warehouse, and JSON_OBJECT / JSON_ARRAYAGG wrap it:

exec sql
   with w as (
        select trim(s.STWHS) as code, trim(s.WHNAME) as name,
               count(*) as cnt,
               listagg(trim(s.STITEM), ', ')
                  within group (order by s.STITEM) as items
          from STOCK_STATUS s
         where s.REORDER = 'Y'
         group by s.STWHS, s.WHNAME)
   select coalesce(sum(w.cnt), 0),
          json_object(
            'source'     value 'IBM i Warehouse Inventory',
            'text'       value 'Reorder needed: ' concat
                          listagg(w.code concat ' ('
                                  concat w.items concat ')', '; ')
                             within group (order by w.code),
            'warehouses' value json_arrayagg(
                          json_object('code'      value w.code,
                                      'name'      value w.name,
                                      'itemCount' value w.cnt,
                                      'items'     value w.items)
                          order by w.code))
     into :WkItems, :Body
     from w;

Then the POST, again with the _VERBOSE version so the status is checked:

WkOpt = '{"header":"content-type,application/json"}';

exec sql
   select json_value(v.RESPONSE_HTTP_HEADER, '$.HTTP_STATUS_CODE'
                     returning integer)
     into :WkStatus
     from table(QSYS2.HTTP_POST_VERBOSE(:WkUrl, :Body, :WkOpt)) v;

The options JSON is where the request gets its headers. "header":"name,value" sets one header; without content-type: application/json many APIs reject the body or treat it as form data. The same options string also takes settings such as basic authentication and timeouts; the IBM documentation of the HTTP functions lists them all.

For testing I post to https://httpbin.org/post, an echo service that returns what it received. The answer on PUB400 was HTTP 200 with the message intact:

{
  "source": "IBM i Warehouse Inventory",
  "text": "Reorder needed: CTR (EL-0006, OF-0005); EST (CL-0003, EL-0001, EL-0004, OF-0002, PK-0005); WST (CL-0001, OF-0001, PK-0001, PK-0003)",
  "warehouses": [
    {"code": "CTR", "name": "Central Warehouse", "itemCount": 2, "items": "EL-0006, OF-0005"},
    {"code": "EST", "name": "East Distribution Hub", "itemCount": 5, "items": "CL-0003, EL-0001, EL-0004, OF-0002, PK-0005"},
    {"code": "WST", "name": "West Distribution Hub", "itemCount": 4, "items": "CL-0001, OF-0001, PK-0001, PK-0003"}
  ]
}

The real webhook URL is a program parameter, so the same program serves a test and a production endpoint.

Practical notes

• Test the SQL alone first. If the statement works in Run SQL Scripts, the RPG part is only host variables and error handling.
• CLOB host variables (SQLTYPE(CLOB:n)) hold the request and response bodies. Size them for the largest answer you expect.
• Read the status before the body, as shown above. Treat anything outside 200–299 as a failure the operator must see.
• TLS uses the certificate store of the system. If a call to a new site fails with a TLS error, the certificate authority of that site has to be trusted on the system first (Digital Certificate Manager). On PUB400 the public APIs I used worked out of the box.
• Commitment control: an HTTP call cannot be rolled back. When a program posts to an external system and updates Db2, decide which side is the master and what happens when the second step fails.

When I would still use HTTPAPI

The SQL functions cover most "call an API and store the answer" jobs. For very large downloads streamed to the IFS, fine control over the connection, or older releases without the HTTP functions, HTTPAPI is still a good choice. For everything else, Db2 for i calling the service is fewer moving parts and is easy for an SQL-literate team to support.

Takeaways

• QSYS2.HTTP_GET + JSON_TABLE + MERGE loads a REST API into a Db2 table in one statement.
• An HTTP error status is not an SQL error. Use HTTP_GET_VERBOSE / HTTP_POST_VERBOSE and check HTTP_STATUS_CODE.
• LISTAGG and JSON_OBJECT build the outgoing message in SQL, so the RPG code stays short.
• Fail loudly with an escape message, so the CL job and the operator know.

Both programs (INV600R, INV700R) and the rest of the application are on GitHub: github.com/dodikibmi/ibmi-inventory. My previous post covers the opposite direction, a REST API served from IBM i with Python: A REST API on IBM i with Python and ibm_db.

I am an IBM i developer from Indonesia, working with RPG, SQL and CL on Db2 for i. This project is a sample application I built on PUB400 to practise modern IBM i techniques in public.