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

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:

1. Standard library only. http.server is enough for a demo and for internal tools. No pip install on a shared system.
2. Reads are plain SQL against tables and a view.
3. 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 [e-mail] \
    /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.