DEV Community: Dodik Setyawan

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

Dodik Setyawan — Sun, 01 Nov 2026 15:55:01 +0000

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):

GET: load public holidays from a REST API into a Db2 table (INV600R).
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.

A REST API on IBM i with Python and ibm_db, no framework, running in PASE

Dodik Setyawan — Sun, 01 Nov 2026 15:35:46 +0000

Most IBM i shops sooner or later get the same request: "Can the web team read this data as JSON?" The data is in Db2 for i, the rules live in RPG programs, and the new consumer speaks HTTP.

You do not need middleware or a new server for that. IBM i ships Python and the ibm_db driver as open-source packages, and PASE runs them next to the database. In this post I build a small REST API for a warehouse inventory application, with nothing but the Python standard library and ibm_db, and I show the problems I hit on the way.

All code is from my sample project on GitHub: dodikibmi/ibmi-inventory (api/server.py, about 330 lines). It runs on PUB400, the free public IBM i.

The design in one picture

PC / browser ──SSH tunnel──► PASE: python3.9 server.py ──ibm_db──► Db2 for i
                                                                     ▲
5250 green screens (RPG) ───────────────────────────────────────────┘
          both channels post stock movements through ONE SQL procedure

Three decisions shape the whole thing:

Standard library only. http.server is enough for a demo and for internal tools. No pip install on a shared system.
Reads are plain SQL against tables and a view.
Writes go through an SQL stored procedure (POST_MOVEMENT) that holds the same posting rules as the RPG entry program. The API never updates a table directly, so the green screens and the API cannot drift apart.

Endpoints

Method	Path	Result
GET	`/api/health`	database and schema in use
GET	`/api/items?category=OFFC&status=A`	item list
GET	`/api/items/{code}`	one item with stock per warehouse
GET	`/api/stock?warehouse=CTR&reorder=Y`	stock status, optionally only items to reorder
GET	`/api/movements?item=OF-0001&from=2026-09-01`	movement history
POST	`/api/movements`	post a movement; `201` with the new stock

Install

In an SSH session on the IBM i (or with the ACS Open Source Package Management tool):

yum install python39 python39-ibm_db

That is all. /QOpenSys/pkgs/bin/python3.9 now has import ibm_db.

Connecting to the local database

From PASE the driver talks to the Db2 of the same system, so the "database" is the local relational database name (WRKRDBDIRE, entry *LOCAL):

DATABASE = os.environ.get("INV_DB", "*LOCAL").upper()
DB_USER = os.environ.get("INV_USER", "")
DB_PASSWORD = os.environ.get("INV_PASSWORD", "")

_conn = None

def connection():
    global _conn
    if _conn is None or not ibm_db.active(_conn):
        _conn = ibm_db.connect(DATABASE, DB_USER, DB_PASSWORD)
        ibm_db.autocommit(_conn, ibm_db.SQL_AUTOCOMMIT_OFF)
        ibm_db.exec_immediate(_conn, f"SET SCHEMA {SCHEMA}")
    return _conn

Gotcha 1: an empty user is not always allowed. On many systems an empty user and password connect as the user who started the job. PUB400 refuses that with SQL30082. The fix is to pass a user and password, but never in code: the start script reads the password with read -s (no echo, not in the shell history) and puts it only in the process environment.

export INV_USER=${INV_USER:-$(id -un)}
if [ -z "$INV_PASSWORD" ]; then
  read -r -s -p "Database password for $INV_USER: " INV_PASSWORD
  echo
  export INV_PASSWORD
fi
exec /QOpenSys/pkgs/bin/python3.9 server.py

Gotcha 2: one local connection per job. Without server mode the PASE CLI driver gives you one local connection per job. That is why the server opens it once and HTTPServer (not ThreadingHTTPServer) handles requests one at a time. For an internal tool that is fine; see "Before production" below for the alternatives.

Autocommit is off on purpose: the API decides when a unit of work ends.

Reading rows into JSON

One helper runs a parameterised SELECT and returns a list of dicts:

def rows(sql, params=()):
    stmt = ibm_db.prepare(connection(), sql)
    try:
        ibm_db.execute(stmt, tuple(params))
        numeric = {ibm_db.field_name(stmt, i).lower()
                   for i in range(ibm_db.num_fields(stmt))
                   if ibm_db.field_type(stmt, i) in ("decimal", "numeric")}
        out = []
        row = ibm_db.fetch_assoc(stmt)
        while row:
            rec = {}
            for key, value in row.items():
                key = key.lower()
                if key in numeric and value is not None:
                    value = decimal.Decimal(str(value))
                elif isinstance(value, str):
                    value = value.rstrip()
                rec[key] = value
            out.append(rec)
            row = ibm_db.fetch_assoc(stmt)
    finally:
        ibm_db.free_stmt(stmt)
    ibm_db.commit(connection())
    return out

Things an IBM i developer recognises immediately:

CHAR columns come back padded. rstrip() every string, or your JSON is full of "OF-0001 ".
Packed and zoned decimals arrive as strings or floats depending on the driver version. Converting them to Decimal keeps money exact; the JSON encoder then writes whole numbers as integers.
Always use parameter markers (?). Query parameters from the URL go into params, never into the SQL text, so there is no SQL injection.
Free the statement. With a server-mode connection an open statement handle can keep the next statement waiting. free_stmt in finally avoids a hang that is hard to trace.

A handler is then just SQL with optional filters:

def get_stock(q):
    sql = ("SELECT STWHS AS warehouse, STITEM AS item, ITDESC AS description,"
           " STQTY AS on_hand, ITMINQ AS minimum, STVALUE AS value,"
           " REORDER AS reorder FROM STOCK_STATUS WHERE 1 = 1")
    params = []
    if "warehouse" in q:
        sql += " AND STWHS = ?"
        params.append(q["warehouse"].upper())
    if q.get("reorder", "").upper() == "Y":
        sql += " AND REORDER = 'Y'"
    data = rows(sql + " ORDER BY STWHS, STITEM", params)
    return {"count": len(data), "stock": data}

AS warehouse gives the JSON readable names, while the tables keep their short RPG-style column names.

Routing without a framework

A list of (method, regex, handler) tuples is enough:

ROUTES = [
    ("GET", re.compile(r"^/api/health$"), get_health),
    ("GET", re.compile(r"^/api/items$"), get_items),
    ("GET", re.compile(r"^/api/items/([^/]+)$"), get_item),
    ("GET", re.compile(r"^/api/stock$"), get_stock),
    ("GET", re.compile(r"^/api/movements$"), get_movements),
]

do_GET matches the path, passes the query string and the regex groups to the handler, and turns a small ApiError(status, message) exception into a JSON error. Anything unexpected becomes a 500 with the driver message, and the connection is never dropped without a response.

Writes: reuse the rules, do not copy them

Posting a stock movement has rules: valid movement type, active item and warehouse, non-zero quantity, no negative stock, and the stock row must be locked so two users cannot take the last units at the same time. The 5250 program already does all of that in RPG.

Rewriting those rules in Python would give two versions that slowly drift apart. So the rules moved into an SQL procedure, and both channels call it. The business errors use their own SQLSTATE values:

CREATE OR REPLACE PROCEDURE POST_MOVEMENT (
  IN  P_TYPE    CHAR(1),
  IN  P_ITEM    CHAR(10),
  IN  P_WHS     CHAR(3),
  IN  P_QTY     DECIMAL(9, 0),
  IN  P_DATE    DATE,
  IN  P_REF     CHAR(20),
  OUT P_MVNO    DECIMAL(9, 0),
  OUT P_NEWQTY  DECIMAL(9, 0))
  LANGUAGE SQL
  MODIFIES SQL DATA
BEGIN
  ...
  IF NOT EXISTS (SELECT 1 FROM ITEM_MASTER
                  WHERE ITITEM = P_ITEM AND ITSTAT = 'A') THEN
    SIGNAL SQLSTATE '75002'
      SET MESSAGE_TEXT = 'Item does not exist or is inactive';
  END IF;
  ...
END;

The procedure does not commit. The caller owns the unit of work, which is exactly what an API needs: commit on success, roll back on any error.

On the Python side, the SQLSTATE becomes the HTTP status:

BUSINESS_ERRORS = {
    "75001": 400, "75002": 404, "75003": 404,
    "75004": 400, "75005": 400, "75006": 409,   # 75006 = insufficient stock
}

try:
    result = ibm_db.callproc(conn, f"{SCHEMA}.POST_MOVEMENT",
                             (mtype, item, whs, qty, date, ref,
                              " " * 9, " " * 9))
    stmt = result[0]
    number, on_hand = result[7], result[8]
    ibm_db.commit(conn)
except Exception as exc:
    state = ibm_db.stmt_error() or ""        # read it BEFORE the rollback
    message = ibm_db.stmt_errormsg() or str(exc)
    ibm_db.rollback(conn)
    status = BUSINESS_ERRORS.get(state[:5], 500)
    ...

Gotcha 3: read the error before you roll back. rollback() clears the statement error, and you are left with an empty SQLSTATE and a useless 500.

Gotcha 4: OUT parameters. callproc returns a tuple: the statement first, then every parameter, so the two OUT values are items 7 and 8. The driver sizes the OUT buffer from the initial value you pass, and it does not clear what follows the returned digits. Passing nine blanks for a DECIMAL(9, 0) and keeping only the digits afterwards gives a clean number:

def out_number(value):
    digits = re.sub(r"[^0-9-]", "", str(value))
    return int(digits) if digits else None

The result is the behaviour a client developer expects:

POST /api/movements  {"type":"O","item":"OF-0001","warehouse":"CTR","quantity":5,"reference":"DO-1"}
201  {"number": 18, "item": "OF-0001", "warehouse": "CTR", "type": "O",
      "quantity": 5, "date": "2026-09-26", "on_hand": 320}

POST /api/movements  {"type":"O","item":"EL-0005","warehouse":"CTR","quantity":999}
409  {"error": "Insufficient stock for this movement"}

Running it

PUB400 does not open extra ports to the internet, so the server listens on 127.0.0.1 and an SSH tunnel brings it to the PC. One command starts both:

ssh -t -p 2222 -L 48400:127.0.0.1:48400 USER@pub400.com \
    /QOpenSys/pkgs/bin/bash /home/USER/inventory/api/start_api.sh

Then open http://localhost:48400/api/health in a browser, or run the curl smoke test in api/test_api.sh.

One more small help: the request handler arms faulthandler.dump_traceback_later(20). If a request is still running after 20 seconds, Python prints its stack to the log, so a database wait shows the exact line instead of a silent hang.

Before production

This is a sample, and I kept it small on purpose. For a real system I would add:

Authentication and TLS, for example behind the IBM HTTP Server (Apache) as a reverse proxy, instead of exposing http.server.
Concurrency: a WSGI server (gunicorn, waitress) with server-mode connections or one connection per worker, instead of one request at a time.
A proper job: start it with SBMJOB in its own subsystem or with Service Commander, so it runs under a service profile and restarts with the system.
Logging and monitoring of response times and 500s.

The core idea stays the same: Python in PASE is a thin HTTP layer, and Db2 for i (tables, views and SQL procedures) keeps the data and the rules.

Takeaways

Python 3.9 and ibm_db are one yum install away on any current IBM i.
Trim CHAR, keep decimals as Decimal, and always use parameter markers.
Put the business rules in an SQL procedure and call it from every channel, the green screen and the API alike.
Map your own SQLSTATE values to HTTP status codes, and read the error before the rollback.

The full source, the SQL scripts and the 5250 side of the application are on GitHub: github.com/dodikibmi/ibmi-inventory. Questions and corrections are welcome in the comments.

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.