Modern integrations on IBM i: calling REST APIs and working with JSON

By /

IBM i has been talking to other systems for decades. EDI over AS/400 communications, FTP file drops, MQ message queues — the integration patterns are well established. What has changed is what the other side expects. External systems today speak HTTP, JSON, and REST. If your IBM i application needs to call a modern API, receive a webhook, or expose data to a web front end, you need to speak that language too.

The good news is that IBM i is fully capable of all of it. The tools are built in. This post covers the practical patterns for calling external REST APIs from RPG, parsing JSON responses, and building simple HTTP endpoints that other systems can call.

The foundation: HTTP APIs on IBM i

IBM i ships with two built-in options for making HTTP calls:

HTTPGETCLOB / HTTPPOSTCLOB — SQL scalar functions in DB2 for i. The simplest possible way to make an HTTP call, directly from a SQL statement.

QzhbCgiUtils / ILE HTTP APIs — lower-level C-style APIs for full control over headers, authentication, timeouts, and SSL.

For most integration work, the SQL HTTP functions are the right starting point. They handle SSL automatically, require no setup, and work from embedded SQL in RPG or directly from ACS.

Calling a REST API with SQL

The simplest HTTP GET you can write on IBM i:

SELECT CAST(
  SYSTOOLS.HTTPGETCLOB('https://api.example.com/orders/12345', '')
  AS VARCHAR(10000))
FROM SYSIBM.SYSDUMMY1

That is it. One SQL function call, and you have the HTTP response as a string. You can run this from ACS, from embedded SQL in RPG, or from a CL program using RUNSQL.

For a POST with a JSON body:

SELECT CAST(
  SYSTOOLS.HTTPPOSTCLOB(
    'https://api.example.com/orders',
    CAST('{"Content-Type":"application/json"}' AS CLOB),
    CAST('{"customerID":"C001","total":1250.00,"status":"OPEN"}' AS CLOB)
  ) AS VARCHAR(10000))
FROM SYSIBM.SYSDUMMY1

The three parameters are: URL, headers as JSON, body as JSON.

Calling a REST API from RPG with embedded SQL

From an RPG program, you call the same SQL functions via embedded SQL:

**FREE
ctl-opt dftactgrp(*no) actgrp(*caller) option(*nodebugio *srcstmt);

dcl-s ApiUrl      varchar(500);
dcl-s ApiResponse varchar(32000);
dcl-s OrderID     packed(9:0);

OrderID = 12345;
ApiUrl  = 'https://api.example.com/orders/' + %char(OrderID);

exec sql
  SELECT CAST(SYSTOOLS.HTTPGETCLOB(:ApiUrl, '') AS VARCHAR(32000))
    INTO :ApiResponse
    FROM SYSIBM.SYSDUMMY1;

if SQLCODE = 0 and ApiResponse  '';
  // ApiResponse now contains the full JSON response
  // Parse it — see next section
else;
  dsply 'API call failed';
endif;

*inlr = *on;
return;

Parsing JSON — the practical approach

DB2 for i includes a full set of JSON functions since IBM i 7.4. If you are on 7.4 or later, you can parse JSON directly in SQL without any additional tools.

Given a JSON response like:

{
  "orderID": 12345,
  "status": "SHIPPED",
  "customer": {
    "name": "Acme Corp",
    "email": "orders@acme.com"
  },
  "items": [
    {"sku": "A100", "qty": 5, "price": 29.99},
    {"sku": "B200", "qty": 2, "price": 149.00}
  ]
}

Extract fields using JSON_VALUE:

dcl-s JsonData   varchar(32000);
dcl-s OrderStatus varchar(20);
dcl-s CustName   varchar(100);
dcl-s CustEmail  varchar(100);

// JsonData already contains the API response from above

exec sql
  SELECT JSON_VALUE(:JsonData, '$.status'),
         JSON_VALUE(:JsonData, '$.customer.name'),
         JSON_VALUE(:JsonData, '$.customer.email')
    INTO :OrderStatus, :CustName, :CustEmail
    FROM SYSIBM.SYSDUMMY1;

dsply ('Status: ' + %trimr(OrderStatus));
dsply ('Customer: ' + %trimr(CustName));

The second parameter to JSON_VALUE is a JSONPath expression. $ is the root, . navigates into objects, [0] accesses array elements by index.

Working with JSON arrays

For arrays, use JSON_TABLE to turn each array element into a row:

exec sql
  DECLARE ItemCursor CURSOR FOR
    SELECT SKU, QTY, PRICE
      FROM JSON_TABLE(:JsonData, '$.items[*]'
        COLUMNS(
          SKU   VARCHAR(20) PATH '$.sku',
          QTY   INTEGER     PATH '$.qty',
          PRICE DECIMAL(9,2) PATH '$.price'
        )) AS Items;

exec sql OPEN ItemCursor;

exec sql FETCH ItemCursor INTO :Sku, :Qty, :Price;

dow SQLCODE = 0;
  // Process each line item
  dsply (%trimr(Sku) + ' x' + %char(Qty));
  exec sql FETCH ItemCursor INTO :Sku, :Qty, :Price;
enddo;

exec sql CLOSE ItemCursor;

JSON_TABLE is powerful — it lets you treat a JSON array exactly like a database table, with full SQL filtering, joining, and aggregation available on top of it.

Building JSON to send

When you need to send JSON to an API, you can build it with JSON_OBJECT and JSON_ARRAY in SQL:

dcl-s JsonBody  varchar(5000);
dcl-s CustID    varchar(10);
dcl-s OrderTotal packed(11:2);
dcl-s OrderDate  date;

CustID     = 'C001';
OrderTotal = 1250.00;
OrderDate  = %date();

exec sql
  SELECT CAST(JSON_OBJECT(
    'customerID' VALUE :CustID,
    'orderDate'  VALUE VARCHAR_FORMAT(:OrderDate, 'YYYY-MM-DD'),
    'total'      VALUE :OrderTotal,
    'currency'   VALUE 'USD',
    'status'     VALUE 'PENDING'
  ) AS VARCHAR(5000))
    INTO :JsonBody
    FROM SYSIBM.SYSDUMMY1;

// JsonBody is now: {"customerID":"C001","orderDate":"2026-05-18","total":1250.00,...}
// Send it with HTTPPOSTCLOB

For more complex structures with nested objects and arrays, you can nest JSON_OBJECT and JSON_ARRAY calls, or build the JSON string manually using RPG string concatenation for simpler cases.

Handling authentication

Most real APIs require authentication. The most common patterns:

Bearer token (OAuth 2.0):

dcl-s Headers  varchar(500);
dcl-s Token    varchar(200);

Token   = 'eyJhbGciOiJSUzI1NiJ9...'; // Retrieved from auth endpoint
Headers = '{"Authorization":"Bearer ' + %trimr(Token) + '",' +
           '"Content-Type":"application/json"}';

exec sql
  SELECT CAST(SYSTOOLS.HTTPGETCLOB(:ApiUrl, :Headers) AS VARCHAR(32000))
    INTO :ApiResponse
    FROM SYSIBM.SYSDUMMY1;

Basic authentication:

// Base64-encode "username:password"
// IBM i has no built-in base64 in RPG, use the SQL function:
dcl-s Credentials varchar(200);
dcl-s Encoded     varchar(200);
dcl-s Headers     varchar(500);

Credentials = 'myuser:mypassword';

exec sql
  SELECT CAST(BASE64_ENCODE(CAST(:Credentials AS VARBINARY(200)))
              AS VARCHAR(200))
    INTO :Encoded
    FROM SYSIBM.SYSDUMMY1;

Headers = '{"Authorization":"Basic ' + %trimr(Encoded) + '"}';

exec sql
  SELECT CAST(SYSTOOLS.HTTPGETCLOB(:ApiUrl, :Headers) AS VARCHAR(32000))
    INTO :ApiResponse
    FROM SYSIBM.SYSDUMMY1;

For API keys, add them as a header in the same way — the header name varies by API (X-API-Key, api-key, Authorization).

A complete integration example

Here is a realistic pattern: call an external shipping API to get a tracking status, parse the response, and update a local database record.

**FREE
ctl-opt dftactgrp(*no) actgrp(*caller) option(*nodebugio *srcstmt);

dcl-s TrackingNum  varchar(30);
dcl-s ApiUrl       varchar(500);
dcl-s ApiKey       varchar(100);
dcl-s Headers      varchar(300);
dcl-s Response     varchar(32000);
dcl-s ShipStatus   varchar(50);
dcl-s DeliveryDate varchar(20);
dcl-s OrderID      packed(9:0);

OrderID     = 98765;
TrackingNum = '1Z999AA10123456784';
ApiKey      = 'sk_live_abc123xyz';

ApiUrl  = 'https://api.shippingco.com/track/' + %trimr(TrackingNum);
Headers = '{"X-API-Key":"' + %trimr(ApiKey) + '"}';

// Call the API
exec sql
  SELECT CAST(SYSTOOLS.HTTPGETCLOB(:ApiUrl, :Headers) AS VARCHAR(32000))
    INTO :Response
    FROM SYSIBM.SYSDUMMY1;

if SQLCODE <> 0 or Response = '';
  // Log failure and exit
  dsply 'Tracking API call failed';
  *inlr = *on;
  return;
endif;

// Parse the response
exec sql
  SELECT JSON_VALUE(:Response, '$.status'),
         JSON_VALUE(:Response, '$.estimatedDelivery')
    INTO :ShipStatus, :DeliveryDate
    FROM SYSIBM.SYSDUMMY1;

if SQLCODE = 0;
  // Update our order record
  exec sql
    UPDATE MYLIB.ORDERPF
      SET ShipStatus   = :ShipStatus,
          DeliveryDate = DATE(:DeliveryDate),
          LastTracked  = CURRENT_TIMESTAMP
      WHERE OrderID = :OrderID;

  if SQLCODE = 0;
    exec sql COMMIT;
  endif;
endif;

*inlr = *on;
return;

Exposing IBM i data as an API

Calling external APIs is one direction. The other is making your IBM i data available to external systems — a web front end, a mobile app, a reporting tool.

The cleanest modern approach is to use IBM i’s integrated HTTP server (Apache) combined with RPG CGI programs or, better, a Node.js or PHP layer running in PASE (the UNIX-compatible environment on IBM i).

A simpler starting point that requires no web server configuration: use Db2 for i’s REST interface, which is available on IBM i 7.3 and later via the IBM i Access REST services. This lets external systems query your DB2 tables directly over HTTP with no RPG code required, using SQL as the query language.

For production API exposure, the most common pattern today is:

  • RPG programs handle the business logic and database access
  • A Node.js or Python layer running in PASE handles HTTP routing, authentication, and JSON serialisation
  • The Node.js layer calls RPG programs via command or stored procedure

This keeps your business logic in RPG (where it belongs) while using modern tooling for the HTTP layer.

Version and compatibility notes

  • SYSTOOLS.HTTPGETCLOB / HTTPPOSTCLOB — available from IBM i 7.1 TR6 onwards
  • JSON_VALUE, JSON_TABLE, JSON_OBJECT — available from IBM i 7.4 onwards
  • On 7.3, use SYSTOOLS.JSON2BSON and the BSON functions, or parse JSON manually with string functions
  • SSL certificates are managed via Digital Certificate Manager (DCM) — your system administrator needs to ensure the relevant CA certificates are trusted before HTTPS calls will work

If an HTTPS call fails silently (empty response, no error), the first thing to check is DCM — the certificate chain for the target server may not be trusted on your system.

Next post: Operations and admin — job queues, output queues, and keeping IBM i running smoothly.

Leave a Comment

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

Scroll to Top